none
Replacing Space in csv RRS feed

  • Question

  • Hi,

    I have a csv file and I need to replace the space in the TEST column. I believe there is a relplace cmdlet, however that is not working.

    "Count","TEST "
    "535","005120 "
    "738","005130 "
    "529","005211 "

    I need the output to be something like this.

    "Count","TEST"
    "535","005120"
    "738","005130"
    "529","005211"

    Thanks

    Wednesday, June 4, 2014 6:12 PM

Answers

  • Something along these lines should work.  You need to replace a space in both the column header and the values:

    (Import-Csv -Path .\someFile.csv) |
    Select-Object 'Count', @{ Name = 'TEST'; Expression = { ($_.'TEST ').TrimEnd() }} |
    Export-Csv -Path .\someFile.csv -NoTypeInformation

    • Proposed as answer by David Wyatt Wednesday, June 4, 2014 6:37 PM
    • Marked as answer by PowerShellNovice Wednesday, June 4, 2014 10:39 PM
    Wednesday, June 4, 2014 6:21 PM
  • Here's one way:


    import-csv CSVfile.csv | foreach-object {
      new-object PSObject -property @{
        "Count" = $_.Count
        "TEST" = $_."TEST " -replace ' $', ''
      }
    } | export-csv CSVfile-2.csv -notypeinformation
    


    -- Bill Stewart [Bill_Stewart]



    Wednesday, June 4, 2014 6:21 PM
    Moderator
  • Import-Csv file |export-csv file

    Should remove spaces.


    ¯\_(ツ)_/¯

    Wednesday, June 4, 2014 6:22 PM
  • Here is how to recondition CSV files:

    import-csv j.csv |
        select count,@{N='Test';E={($_.'test ').Trim()}} |
        export-csv j2.csv -notype


    ¯\_(ツ)_/¯



    • Proposed as answer by jrv Wednesday, June 4, 2014 6:30 PM
    • Edited by jrv Wednesday, June 4, 2014 7:26 PM Fixed
    • Marked as answer by PowerShellNovice Wednesday, June 4, 2014 10:40 PM
    Wednesday, June 4, 2014 6:29 PM
  • Here is how to recondition CSV files:

    import-csv j.csv |
        select count,@{N='Test';E={$_.'test '}} |
        export-csv j2.csv -notype


    ¯\_(ツ)_/¯



    That still leaves a trailing space in the data row, but is otherwise pretty much identical to what I posted.
    Wednesday, June 4, 2014 6:36 PM

All replies

  • Something along these lines should work.  You need to replace a space in both the column header and the values:

    (Import-Csv -Path .\someFile.csv) |
    Select-Object 'Count', @{ Name = 'TEST'; Expression = { ($_.'TEST ').TrimEnd() }} |
    Export-Csv -Path .\someFile.csv -NoTypeInformation

    • Proposed as answer by David Wyatt Wednesday, June 4, 2014 6:37 PM
    • Marked as answer by PowerShellNovice Wednesday, June 4, 2014 10:39 PM
    Wednesday, June 4, 2014 6:21 PM
  • Here's one way:


    import-csv CSVfile.csv | foreach-object {
      new-object PSObject -property @{
        "Count" = $_.Count
        "TEST" = $_."TEST " -replace ' $', ''
      }
    } | export-csv CSVfile-2.csv -notypeinformation
    


    -- Bill Stewart [Bill_Stewart]



    Wednesday, June 4, 2014 6:21 PM
    Moderator
  • Import-Csv file |export-csv file

    Should remove spaces.


    ¯\_(ツ)_/¯

    Wednesday, June 4, 2014 6:22 PM
  • Here is how to recondition CSV files:

    import-csv j.csv |
        select count,@{N='Test';E={($_.'test ').Trim()}} |
        export-csv j2.csv -notype


    ¯\_(ツ)_/¯



    • Proposed as answer by jrv Wednesday, June 4, 2014 6:30 PM
    • Edited by jrv Wednesday, June 4, 2014 7:26 PM Fixed
    • Marked as answer by PowerShellNovice Wednesday, June 4, 2014 10:40 PM
    Wednesday, June 4, 2014 6:29 PM
  • Add the -NoTypeInformation to Bill's Export-CSV cmdlet and you'll have your results.

    ...} | export-csv CSVfile-2.csv -NoTypeInformation
    David's answer works as well.
    Wednesday, June 4, 2014 6:36 PM
  • Here is how to recondition CSV files:

    import-csv j.csv |
        select count,@{N='Test';E={$_.'test '}} |
        export-csv j2.csv -notype


    ¯\_(ツ)_/¯



    That still leaves a trailing space in the data row, but is otherwise pretty much identical to what I posted.
    Wednesday, June 4, 2014 6:36 PM
  • Here is how to recondition CSV files:

    import-csv j.csv |
        select count,@{N='Test';E={$_.'test '}} |
        export-csv j2.csv -notype


    ¯\_(ツ)_/¯



    That still leaves a trailing space in the data row, but is otherwise pretty much identical to what I posted.

    I was trying to fix it when the web site went crazy - I gave up.  I just fixed it and the web site is working again.

    ¯\_(ツ)_/¯

    Wednesday, June 4, 2014 7:26 PM