How to correctly display csv files within Excel 2013

Question

It seems Excel 2013 doesn t read CSV files correctly  Excel 2010 does   Every time I open  csv files  all my data are displayed in the first column   I know I can go to DATA  Convert  and then choose commas  but this set up is not saved  and I am fed up doing this every time I open this kind of file   How can I set Excel 2013  once and for all  to display CSV files  comma separated  prettily

User · Answer

I know that an answer has already been accepted  but one item to check is the encoding of the CSV file   I have a Powershell script that generates CSV files   By default  it was encoding them as UCS-2 Little Endian  per Notepad      It would open the file in a single column in Excel and I d have to do the Text to Columns conversion to split the columns    Changing the script to encode the same output as  ASCII   UTF-8 w o BOM per Notepad    allowed me to open the CSV directly with the columns split out  You can change the encoding of the CSV in Notepad   too    Menu Encoding   Convert to UTF-8 without BOM Save the CSV file Open in Excel  columns should be split

User · Answer

For Excel 2013    Open Blank Workbook  Go to DATA tab  Click button From Text in the General External Data section  Select your CSV file  Follow the Text Import Wizard   in step 2  select the delimiter of your text    http   blogmines com blog how-to-import-text-file-in-excel-2013

User · Answer

Taken from https   superuser com questions 238944 how-to-force-excel-to-open-csv-files-with-data-arranged-in-columns  The behavior of Excel when opening CSV files heavily depends on your local settings and the selected list separator under Region and language    Formats    Advanced  By default Excel will assume every CSV was saved with that separator  Which is true as long as the CSV doesn t come from another country   If your customers are in other countries  they may see other results then you think   For example  here you see that a German Excel will use semicolon instead of comma like in the U S

User · Answer

You can choose which separator you want in Excel 2013 Go to DATA -  Text To Columns -  Choose delimited -  then choose your separator  Tab  Semicolon  Comma  Space or other  and you will see changes immediately in the  data preview  then click FInish  Once you have the format that you wanted  you simply save the document and it will be permanent

User · Answer

The problem is from regional Options   The decimal separator in win 7 for european countries is coma   You have to open Control Panel -  Regional and Language Options -  Aditional Settings -  Decimal Separator   click to enter a dot     and to List Separator enter a coma        This is

User · Answer

Another possible problem is that the csv file contains a byte order mark  FEFF   The byte order mark is intended to detect whether the file has been moved from a system using big endian or little endian byte ordering to a system of the opposite endianness  https   en wikipedia org wiki Byte order mark  Removing the  FEFF  byte order mark using a hex editor should allow Excel to read the file

User · Answer

Open the CSV file with a decent text editor like Notepad   and add the following text in the first line   sep     Now open it with excel again   This will set the separator as a comma  or you can change it to whatever you need

[excel] How to correctly display .csv files within Excel 2013?

Examples related to excel

Examples related to csv

Examples related to office-2013