Query regarding the format of a csv file when using the export-csv command in powershell

Question

I'm familiar with exporting data to csv with Powershell, but a particular vendor wants to receive the data in a file that is specific type 'CSV'. If I use the following example command, the data looks fine in the raw csv format (i.e via notepad), where you see it's comma separated ...

,,JVF1033292,test,SL,10700,6804626,745.586,43001843,Test,8/12/2020,8/14/2020,T,5584,,,JPY,0,XTKS,,,,,,0 ,,JVF1033293,test,SL,3695,6805179,1362.8457,43001843,Test,8/12/2020,8/14/2020,T,3524,,,JPY,0,XTKS,,,,,,0

... but when the same file is opened in Excel all the data is in one row and therefore is failing on the vendors side. The code I'm using is below.

$Table | ConvertTo-Csv -NoTypeInformation | ForEach-Object {$_-replace "`"", ""} |  select -skip 1 | out-file -filepath ("$dir_opr\LVT\Export\CTO\ForJefferies\GMO_Jefferies_Trade_Data_" + $Date+ ".csv")

If I use the below code, then it looks fine in Excel (tabbed correctly), but the raw file is also tabbed and not comma separated which the vendor has issues with.

$Table | ConvertTo-Csv -Delimiter "`t" -NoTypeInformation | ForEach-Object {$_-replace "`"", ""} |  select -skip 1 | out-file -filepath ("$dir_opr\LVT\Export\CTO\ForJefferies\GMO_Jefferies_Trade_Data_" + $Date+ ".csv")

X JVF1032244 Test BY 450.0000 BYM41J3 10.00000000 43001843 Test 08/11/2020 08/13/2020 T 3.00 JPY 0 XTKS 0.00
X JVF1032245 Test BY 200.0000 BYM41J3 250.00000000 43001843 Test 08/11/2020 08/13/2020

Is it possible to create a comma separated and tabbed raw file, that also is delimited in Excel so not all in one column?

As far as I can tell with testing locally, it might be due to the default encoding that out-file uses - i.e utf8NoBOM. Try out-file -filepath $myfile -encoding utf8 - that seems to fix it on my machine. — mclayton
– mclayton, Commented Aug 12, 2020 at 15:39
Yes that was it, I added -Encoding UTF8 and that resolved it thanks! — BobbyS
– BobbyS, Commented Aug 12, 2020 at 15:40
cool cool. i'll ad it as an answer so other people can find it easier if they hit the same issue... — mclayton
– mclayton, Commented Aug 12, 2020 at 15:41
ok thanks! I'm prettynew to using this so not sure how to close this out, but thanks very much! — BobbyS
– BobbyS, Commented Aug 12, 2020 at 15:42
No worries. You normally just wait at least 24 hours to see if you get a better answer, and then accept the one you like best. People will vote answers up or down, and the one with the highest consensus floats to the top of the page. You can vote your accepted answer up as well if you like. — mclayton
– mclayton, Commented Aug 12, 2020 at 15:44

mclayton · Accepted Answer · 2020-08-12 15:42:22Z

1

As far as I can tell with testing locally, it might be due to the default encoding that out-file uses - i.e utf8NoBOM. (See the description for the -Encoding parameter for details: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/out-file?view=powershell-7)

Try out-file -filepath $myfile -encoding utf8 - that seems to fix it on my machine.

answered Aug 12, 2020 at 15:42

mclayton

10.4k3 gold badges26 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query regarding the format of a csv file when using the export-csv command in powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related