.csv file output from Powershell is displayed in one column in Excel

Question

Lately on some Windows 10 machines I noticed that Get-Service | Export-Csv "C:\users\[xxx]" creates a .csv file that if opened in Excel displays all properties just in column A. I'm used to seeing each property being output to a separate column.

The Windows and Office versions on both machines are identical. Windows: 1709 (Build 16299.431) Office: 1802 (Build 9029.2253 CTR)

Excel-Online also displays the output in one column.

Right-clicking the .csv file on each machine shows diffrent file types: "CSV-File (.csv)" which displays multiple columns versus "Microsoft Excel-CSV-File(.csv)" which opens in one column.

Moreover, the file icons are different.

for the machines where it displays multiple columns and for machines where it displays in one column.

Has anybody experienced this and knows why that might be?

I'm consfused as to which is the correct behaviour.

Thanks for your input.

Paxz · Accepted Answer · 2018-07-12 07:54:27Z

5

Export-CSV uses , to seperate columns, while excel wants ; as seperators.

The easiest way for you to handle the situation is to use this instead:

Export-Csv "C:\users\[xxx]" -Delimiter ';'

This will create the .csv file with ; instead of ,.

You probaly have different default delimiters on the two machines, the answer here explains it pretty well: How to get Excel to interpret the comma as a default delimiter in CSV files?

edited Jul 12, 2018 at 7:54

answered Jul 12, 2018 at 7:35

Paxz

3,0661 gold badge22 silver badges36 bronze badges

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

4 Comments

colonel_claypoo Over a year ago

Interesting. I used -Delimiter ';' and now it's the other way around. The file now opens everything in one column for the machine where it had been multiple columns before and vice versa on the other machine. Any idea why Excel uses different delimiters on different machines? Could it have to do with the file extensions and icons I mentioned in my initial post?

Paxz Over a year ago

@colonel_claypoo Take a look at superuser.com/questions/606272/…

StefTheo Over a year ago

Select the one column, then the data tab from the ribbon, then text to columns and you will go through the wizard. As for the delimiter attribute I am using the "|" because sometimes my data can contain a semi colon or a comma.

colonel_claypoo Over a year ago

@Paxz: Thank you, that's the answer. Much appreciated.

Grallen · Accepted Answer · 2018-12-06 18:55:24Z

1

The problem for me came from the encoding the csv file is written in. When I used Out-File in PowerShell it encoded in UCS-2 Little Endian and everything ended up in one column.

My solution for you would be along these lines:

Get-Service | Export-Csv -Path "C:\users\[xxx]" -NoTypeInformation -Encoding UTF8 -Force

edited Dec 6, 2018 at 18:55

answered Dec 6, 2018 at 18:42

Grallen

1,7001 gold badge17 silver badges17 bronze badges

Collectives™ on Stack Overflow

.csv file output from Powershell is displayed in one column in Excel

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related