198

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?

David
  • 4,785
  • 7
  • 39
  • 63

7 Answers7

416

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.

Guilherme Viebig
  • 6,901
  • 3
  • 28
  • 30
  • 3
    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. – David Feb 14 '14 at 20:35
  • 31
    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) – jmarkmurphy Feb 26 '14 at 18:57
  • 22
    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. – Guilherme Viebig Mar 04 '14 at 20:37
  • 10
    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. – Elliot Sep 23 '14 at 20:49
  • 2
    This shows the line if you open the csv in Libreoffice Calc. Works fine in MS excel though. – Arda Oct 16 '14 at 07:54
  • 1
    For those who are wondering whether this also works for Excel 2010: no it doesn't. I've just tried it. – Fabio says Reinstate Monica Sep 23 '15 at 09:54
  • 2
    Does anybody know where this is documented? Can't find anything in the help of Excel. Also, are there more first-line-hacks of this kind? – wotuzu17 Dec 18 '15 at 13:57
  • @GuilhermeViebig One of the reasons to use CSV instead of actual Excel files is to have a more standard, non-binary format that you can use with other tools. – jpmc26 Dec 22 '17 at 06:36
  • Haha, used CSV quite often and didn't know that this is a thing. I like it and will from now on consider it in coding with CSV :) – CodingYourLife May 31 '18 at 20:57
  • For Tab: sep=\t – Demodave Jun 05 '18 at 20:42
  • Thank you. I was able to quickly "patch" a "CSV opening problem" an internal user was having. However, UGH! The "C" in CSV literally stands for "Comma"... Having to explicitly tell Excel this while dirtying up the file is {https://www.thesaurus.com/browse/stupid} – Bitwise Creative Nov 08 '18 at 21:35
  • @jpmc26 FWIW An .xls(x) file is just a .zip container of XML files (try opening one in your favorite archive tool). With a bit of effort you can make an Excel file work in any program that supports that kind of structure. – TylerH Dec 05 '18 at 17:08
  • @TylerH Yes, but that's not particularly helpful if you're looking for your source control to handle diffs or for compatibility with other software. The internal structure is a bunch of obscure XML, and I'm not aware of software able to handle it in unzipped form. – jpmc26 Dec 05 '18 at 17:15
  • And then redo this 4532561 times for all your files! How is this for an accepted answer?! – Herman Toothrot Mar 13 '19 at 12:05
96

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/

marcosbernal
  • 1,066
  • 8
  • 4
40

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 !

skander
  • 401
  • 4
  • 2
  • 5
    This does not work. I have this set as indicated and it is ignored. Everything is till in the first column. – Elliot Sep 23 '14 at 20:50
  • 1
    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... – John Jan 19 '15 at 16:34
  • 3
    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
  • I have the same problem with OP and this is true answer for it! Thanks! – Luke Apr 12 '18 at 07:45
  • 1
    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=," – JP slash otterlover Aug 29 '18 at 18:37
  • 1
    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](https://stackoverflow.com/a/1732454/6743127) 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. – Fabian Röling May 14 '19 at 12:31
  • i changed to English (Canada) and worked just fine!. thanks to all! – Siberia Dec 22 '20 at 16:35
  • It is work - windows 10 - Excel 2013 - Thank you – Farshad Javid Feb 06 '21 at 10:47
26

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
Shayne Ephraim
  • 541
  • 4
  • 13
  • 3
    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). – Mister_Tom Nov 02 '15 at 22:05
  • 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? – jdgregson Aug 23 '17 at 22:28
  • Using "-Encoding Default" fixed it for me. Thanks! – Kirk Liemohn Oct 14 '17 at 17:32
21

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.

user3241007
  • 235
  • 2
  • 2
  • 2
    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. – Elliot Sep 23 '14 at 20:50
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

CaldeiraG
  • 152
  • 2
  • 14
cdmdotnet
  • 1,663
  • 3
  • 17
  • 22
  • > 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 – Alex78191 Nov 30 '19 at 15:46
  • 1
    Or you can change decimal separator in Excel options https://superuser.com/a/1470373/ – Alex78191 Nov 30 '19 at 15:53
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.

Devon Holcombe
  • 518
  • 1
  • 5
  • 18