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?
This question is related to
excel
csv
office-2013
The answer is
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
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.
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.
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 !
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.
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/
Source: Stackoverflow.com