We help IT Professionals succeed at work.
Private
Troubleshooting Question

How to make csv open correctly in Excel?

68 Views
Last Modified: 2020-09-08
Hi,

I have created a csv file from PowerShell. What is weird, in the server where I created the csv file I can open it and the values are in appropriated column:


The same csv file copied in my PC opens like this:


Why is that? Is it the a way it opens in Excel or the way I export the csv in PowerShell?
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Please attach the csv file.
CERTIFIED EXPERT
Top Expert 2014

Commented:
I'm guessing that the versions of Excel on the server and your PC are different.  Can you confirm (and which version(s))?

I have observed that opening Excel and then importing a .CSV (assuming you select the correct delimiter) always shows correctly.  In the past, depending on the encoding of the .CSV (text file), Excel would put everything in one column when just double-clicking the file to open it with Excel.  Check what the encoding of the file is.  You can see by opening it with Notepad, choose Save As, then looking at the Encoding box (you can change it here as well).
CERTIFIED EXPERT

Commented:
Double click the CSV will not open "properly" in Excel.

you probably need to create an Excel object and open the CSV file from there from PowerShell

A general quick solution would be:

$xl = new-object -comobject excel.application
$xl.visible = $true
$csv = "$pwd\test.csv";
$xl.workbooks.open($csv)

Author

Commented:
The one that open fine is: Excel 2016 install in Windows 2019

The others one that open in bad format: Office 365 in Windows 10

Here is the csv file attached: UsersTest.csv


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
If you run this macro (after changing the Filename), does the file open properly?

Sub OpenCSV()
    Workbooks.OpenText Filename:="C:\YourFolder\UsersTest.csv.txt", Origin:= _
        65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
        Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), TrailingMinusNumbers:=True
End Sub

Open in new window

Author

Commented:
Sorry both does not work but what I try to achieve to make Excel open it with good format without asking users to lunch any code.

Why Excel 2016 works and not MS Office 365 Excel?

Every time I export csv by PowerShell I used this parameter: -Encoding UTF8 
CERTIFIED EXPERT

Commented:
as mentioned, CSV will never be opened in "good format" in Excel by default when you double clicked on it
CERTIFIED EXPERT
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Finally I change my regional setting to the same as I the server at the office and it works!  Thanks
CERTIFIED EXPERT
Top Expert 2014

Commented:
Can you share what regional settings you had in place?
Brian BEE Topic Advisor, Independent Technology Professional
CERTIFIED EXPERT

Commented:
I'll hazard a guess that since the Author is located in Quebec, one system was set for French and the other English. In French, the decimal separator is ",", so a CSV (separated by commas) would not be interpreted properly.

Author

Commented:
Sorry for the delay.

Yes it was in french and at my office MS Office is in English. Also, I did an Office update juste to make sure;-)
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.