locked
Export-csv giving unexpected results RRS feed

  • Question

  • I have a pipe delimited file as follows 

    CDM|CDMDesc|Qty|HCPCSCode|GrossCharge|Updated Price|
    43278300|0.9 % SODIUM CHLORIDE:50 ML:BAG:50 ML:50 ML:::BAG|1.0000000|UD|15.0000|7.3013
    43299400|0.9 % SODIUM CHLORIDE:500 ML:BAG:500 ML:500 ML:::BAG|1.0000000|UD|19.0000|7.8475|
    43299400|0.9 % SODIUM CHLORIDE:500 ML:BAG:500 ML:500 ML:::BAG|1.0000000|UD|19.0000|7.8475|
    43299400|0.9 % SODIUM CHLORIDE:500 ML:BAG:500 ML:500 ML:::BAG|1.0000000|UD|19.0000||
    43299400|0.9 % SODIUM CHLORIDE:500 ML:BAG:500 ML:500 ML:::BAG|1.0000000|UD|19.0000||
    43299400|0.9 % SODIUM CHLORIDE:500 ML:BAG:500 ML:500 ML:::BAG|1.0000000|UD|19.0000||
    43415470|ACETAMINOPHEN 160 MG/5 ML UDC:160 MG/5 ML:UDC:160 MG:5 ML:::ML|2.0000000|UD|17.7000|8.4044|

    However when I use the following powershell script in Powershell v4

    Get-Content -Path C:\MAT\ship.txt | Export-csv -Path C:\MAT\t3.csv -NoTypeInformation. The t3.csv produces the following :

    PSPath           PSParentPath   PSChildName PSDrive PSProvider ReadCount Length
    C:\MAT\ship.txt  C:\MAT t1.csv C Microsoft.PowerShell.Core\FileSystem 1 1023
    C:\MAT\ship.txt C:\MAT t1.csv C Microsoft.PowerShell.Core\FileSystem 2 890
    C:\MAT\ship.txt C:\MAT t1.csv C Microsoft.PowerShell.Core\FileSystem 3 795
    C:\MAT\ship.txt C:\MAT t1.csv C Microsoft.PowerShell.Core\FileSystem 4 797
    C:\MAT\ship.txt C:\MAT t1.csv C Microsoft.PowerShell.Core\FileSystem 5 914

    Why I am getting this information instead of the .csv of the orginal file

    Help much appreciated.

    Thursday, July 28, 2016 8:47 PM

Answers

  • Don't use Get-Content for a delimited file. Use Import-Csv. If the delimiter is not a comma (,) use the -Delimiter parameter to tell PowerShell what delimiter to use.

    -- Bill Stewart [Bill_Stewart]

    Thursday, July 28, 2016 9:12 PM
  • Import-Csv C:\MAT\ship.txt -Delim '|' |Export-Csv  C:\MAT\t3.csv -NoTypeInformation

    \_(ツ)_/

    Thursday, July 28, 2016 9:13 PM
  • Hi Geo,

    to read a csv file, you need to use Import-Csv, even if the original file has a different extension.

    Your example however is not a well-formatted csv (sometimes you have a closing Pipe, sometimes you don't). You may need to fix this, which can be done programmatically.

    Anyway, if it were a correctly formatted csv, this is what you would do:

    Import-Csv "C:\MAT\ship.txt" -Delimiter "|" | Export-Csv -Path "C:\MAT\t3.csv" -NoTypeInformation

    Cheers,
    Fred


    There's no place like 127.0.0.1


    • Edited by FWN Thursday, July 28, 2016 9:15 PM
    • Marked as answer by Richard MuellerMVP Monday, August 29, 2016 10:32 PM
    Thursday, July 28, 2016 9:14 PM

All replies

  • Did you try with "-Delimiter "|"" parameter?

    https://technet.microsoft.com/en-us/library/hh849932.aspx


    This post is provided AS IS with no warranties or guarantees, and confers no rights.
    ~~~
    Questo post non fornisce garanzie e non conferisce diritti


    • Edited by aperelli Thursday, July 28, 2016 9:01 PM
    Thursday, July 28, 2016 9:01 PM
  • Don't use Get-Content for a delimited file. Use Import-Csv. If the delimiter is not a comma (,) use the -Delimiter parameter to tell PowerShell what delimiter to use.

    -- Bill Stewart [Bill_Stewart]

    Thursday, July 28, 2016 9:12 PM
  • Import-Csv C:\MAT\ship.txt -Delim '|' |Export-Csv  C:\MAT\t3.csv -NoTypeInformation

    \_(ツ)_/

    Thursday, July 28, 2016 9:13 PM
  • Hi Geo,

    to read a csv file, you need to use Import-Csv, even if the original file has a different extension.

    Your example however is not a well-formatted csv (sometimes you have a closing Pipe, sometimes you don't). You may need to fix this, which can be done programmatically.

    Anyway, if it were a correctly formatted csv, this is what you would do:

    Import-Csv "C:\MAT\ship.txt" -Delimiter "|" | Export-Csv -Path "C:\MAT\t3.csv" -NoTypeInformation

    Cheers,
    Fred


    There's no place like 127.0.0.1


    • Edited by FWN Thursday, July 28, 2016 9:15 PM
    • Marked as answer by Richard MuellerMVP Monday, August 29, 2016 10:32 PM
    Thursday, July 28, 2016 9:14 PM
  • Thanks everyone for the quick response - that worked

    Import-Csv C:\MAT\NDC.txt -Delimiter "|" | Export-Csv -Path C:\MAT\t3.csv -NoTypeInformation

    Thursday, July 28, 2016 9:22 PM