200

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?

Improve this question
3
  • 1
    When you open the file does any message appear? Commented Jul 30, 2013 at 19:06
  • No. No message appear Commented Aug 1, 2013 at 8:02
  • My experience is if the file has a header row, Excel 2013 will automatically go through the import wizard. Without that header row, it shows it all as a single column per row. Commented Jul 31, 2015 at 18:52

7 Answers 7

Reset to default
419

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.

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

14 Comments

Best solution, but not the one I was expected for. That setting is not from Excel itself, that means I have to write this above line on all my CSV file.
The fact that this is non-standard, no applications that write CSV's add this line, and only Excel (as far as I know) accepts it. Makes this a poor at best solution. Not to mention that this does not work for Excel 2007 (the best we have here at work)
We are not talking about standards. We´re talking about Microsoft Excel. Excel ships with different configurations and hotkeys depending on the language. This makes importing a CSV file a puzzle. This is simply a workaround, and I believe it helps a lot of people, mostly power users, though not the best answer.
Need a solution that does not involve adding lines to the file, as this breaks other things that need to use the file. Also, already tried the regional settings solution, but the settings were already as indicated and excel is still putting everything in the first column.
This shows the line if you open the csv in Libreoffice Calc. Works fine in MS excel though.
|
98

For Excel 2013:

  1. Open Blank Workbook.
  2. Go to DATA tab.
  3. Click button From Text in the General External Data section.
  4. Select your CSV file.
  5. 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/

Improve this answer

1 Comment

How would one make this wizard open when double click csv and it open excel?
41

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 !

Improve this answer

7 Comments

This does not work. I have this set as indicated and it is ignored. Everything is till in the first column.
Amazing - this just helped out a colleague who was using Office 2011. We set her region from Poland (which uses "," as decimal separator) to UK and this fixed it. Why this should make a difference I have no idea...
This solution works to me (excel 2013). I adjust my regional format: I set a dot (.) as "decimal separator", a comma (,) as "thousand separator" and a comma (,) as a "list separator"
I am using the Windows 10 default "English (Canada)" format which normally displays the CSV file correctly (column separated) when opening the file using Excel 2013-2016. Recently I noticed, after re-opening a CSV file, that one of my application was changing the default numbering format. As specified here, after reverting to the dot (.) as "decimal separator" and comma (,) as a "list separator" numbering format, the CSV file was displayed fine. There's no need for a fancy workaround involving hacking the first row of the CSV file with notepad in order to add "sep=,"
Finally! After trying for many minutes more than I should to replace with regex in Notepad++ the separator, the decimal point/comma and not characters in quotes etc., I was reminded about this answer and just set my language settings differently, like indicated here. Now it properly separates the columns and numbers also appear properly, without messing up any text.
|
27

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
Improve this answer

3 Comments

I appreciate this answer. I like to use Some-Command | Out-File -Encoding Default which usually gives correct ANSI encoding (factory default in USA at least).
So if you don't specify the encoding, Out-File encodes the file as something other than Default by default? I'm trying to understand that... it's default... but not used by default... and therefore not the default?
Using "-Encoding Default" fixed it for me. Thanks!
20

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.

Improve this answer

1 Comment

This does not address the main question. We need this to be default behavior for any such file, not to fix it in one specific file.
19

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.

Regional Settings

Improve this answer

2 Comments

> This solution works to me (excel 2013). I adjust my regional format: I set a dot (.) as "decimal separator", a comma (,) as "thousand separator" and a comma (,) as a "list separator" – cesargastonec Jul 13 '15 at 16:05
Or you can change decimal separator in Excel options superuser.com/a/1470373
4

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.