1

I know a little bit of Bash scripting, but I am very new to PowerShell. When I execute below code using bash, everything is fine. But, when I use PowerShell, each entry per echo is saved only in a single cell in Excel. Why is it like this? How can I accomplish my objective in the simplest way? 

echo "1,2,3" > file.csv
echo "A,B,C" >> file.csv

UNDESIRED:

enter image description here

DESIRED:

enter image description here

I tried to Google it. But, in my understanding, they are converting the string type variables to something like PS Object and convert to CSV format. I tried it and it worked. But I had to force include a header.

New-Object -Type PSObject -Property @{
'X' = $A
'Y' = $B
'Z' = $C
} | Export-Csv 'C:\Temp\test.csv' -NoType

When I also opened the csv file using notepad, every word has double quotation marks (which I don't prefer to have)

You see, that is way more complicated compared to Linux Scripting. Can someone teach me the simplest way to do what I want? Thank you very much!

Li Po
  • 43
  • 8
  • This is normal CSV behaviour. As the file is comma delimited you would need to split the file content on the comma. Then you have your desired outcome. – Alex_P May 29 '20 at 06:55

1 Answers1

1

If in your system locale the ListSeparator character is NOT the comma, double-clicking a comma-delimited csv file will open Excel with all values in the same column.

I believe this is what happens here.

You can check by typing

[cultureinfo]::CurrentCulture.TextInfo.ListSeparator

in PowerShell

To have Excel 'understand' a CSV when you double-click it, add -UseCulture switch to the cmdlet:

Export-Csv 'C:\Temp\test.csv' -UseCulture -NoTypeInformation

As for the quotes around the values:
They are not always necessary, but sometimes essential, for instance if the value has leading or trailing space characters, or if the value contains the delimiter character itself.
Just leave them as-is, Excel knows how to handle that.

If you really can't resist on having a csv without quotes, please first have a look at the answers given here about that subject.

Edit

If you are absolutely sure all fields can do without quoting, you can do this:

$sep = [cultureinfo]::CurrentCulture.TextInfo.ListSeparator
"1,2,3", "A,B,C" -replace ',', $sep | Out-File -FilePath 'D:\Test\file.csv' -Encoding utf8
Theo
  • 57,719
  • 8
  • 24
  • 41
  • 1
    thank you very much! It worked! I tried to query the ListSeparator, and it is comma. But, I think the one that answered my problem is the "-Encoding utf8". It worked like a charm! This is the code I used: echo "A,B,C" | Out-File -FilePath aaa.csv -Encoding utf8 -Append – Li Po May 30 '20 at 10:31