How to correctly display .csv files within Excel 2013?

Asked 30/7, 2013 at 17:36 Answered 17/4, 2017 at 20:24

Solved excel csv office-2013

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?

Plot answered 30/7, 2013 at 17:36 Comment(3)

When you open the file does any message appear? – Kleenex 30/7, 2013 at 19:6

No. No message appear – Plot 1/8, 2013 at 8:2

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. – Tunnage 31/7, 2015 at 18:52

417

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.

Hendrick answered 4/11, 2013 at 14:4 Comment(14)

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. – Plot 14/2, 2014 at 20:35

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) – Chrystal 26/2, 2014 at 18:57

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. – Hendrick 4/3, 2014 at 20:37

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. – Trescott 23/9, 2014 at 20:49

This shows the line if you open the csv in Libreoffice Calc. Works fine in MS excel though. – Maihem 16/10, 2014 at 7:54

For those who are wondering whether this also works for Excel 2010: no it doesn't. I've just tried it. – Donettedoney 23/9, 2015 at 9:54

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? – Afterworld 18/12, 2015 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. – Depreciate 22/12, 2017 at 6: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 :) – Defoliant 31/5, 2018 at 20:57

For Tab: sep=\t – Wizardly 5/6, 2018 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 {thesaurus.com/browse/stupid} – Woodsy 8/11, 2018 at 21:35

@Depreciate 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. – Declivitous 5/12, 2018 at 17:8

@Declivitous 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. – Depreciate 5/12, 2018 at 17:15

And then redo this 4532561 times for all your files! How is this for an accepted answer?! – Periodical 13/3, 2019 at 12:5

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/

Itu answered 24/7, 2014 at 0:19 Comment(0)

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 !

Bursa answered 4/2, 2014 at 15:14 Comment(8)

This does not work. I have this set as indicated and it is ignored. Everything is till in the first column. – Trescott 23/9, 2014 at 20:50

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... – Outbound 19/1, 2015 at 16:34

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" – Incommunicable 13/7, 2015 at 16:5

I have the same problem with OP and this is true answer for it! Thanks! – Crass 12/4, 2018 at 7:45

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=," – Plentiful 29/8, 2018 at 18:37

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. – Phototelegraphy 14/5, 2019 at 12:31

i changed to English (Canada) and worked just fine!. thanks to all! – Ensphere 22/12, 2020 at 16:35

It is work - windows 10 - Excel 2013 - Thank you – Dahlgren 6/2, 2021 at 10:47

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

Beagle answered 10/9, 2015 at 20:0 Comment(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). – Protochordate 2/11, 2015 at 22:5

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? – Hanks 23/8, 2017 at 22:28

Using "-Encoding Default" fixed it for me. Thanks! – Bufordbug 14/10, 2017 at 17:32

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.

Willams answered 27/1, 2014 at 17:15 Comment(1)

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. – Trescott 23/9, 2014 at 20:50

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.

Reference answered 4/5, 2016 at 7:30 Comment(2)

> 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 – Disbelief 30/11, 2019 at 15:46

Or you can change decimal separator in Excel options superuser.com/a/1470373 – Disbelief 30/11, 2019 at 15:53

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.

Guileful answered 17/4, 2017 at 20:24 Comment(0)

Hot tags

Godot Unity Godot Help Programming Godot 4.X GUI GDScript 3D 2D Physics CSharp Godot 3.X VR XR Projects C++

Recommended topics

Hot tags