15

I have a new install of Excel 2016, that hates CSV files. It opens them with everything in one column flagpole style, down column A, with commas and speech marks visible.

Salient points:

  1. I have two machines, desktop and laptop, both running same version of Excel. Desktop works fine, opens the same problem files formatted correctly.

  2. I can create CSV files on laptop, save those, open them again on laptop, and it's fine.

  3. Even opening it in Notepad++, saving in the hope of some sort of file format normalisation, and still no good.

  4. I have compared regional settings and almost all settings in Excel.

  5. I tried renaming the file to TXT, it brought up the text file conversion dialogue, I chose comma delimited. First time it ignored that, still got everything in column A, second attempt, that actually worked, however, that is a pants solution, I want to be able to just natively open CSV files without saving as TXT, I use many different ones every day.

Anyone got any ideas?

Thanks in advance.

TFD
  • 23,890
  • 2
  • 34
  • 51
Rob Clarke
  • 167
  • 1
  • 1
  • 7

12 Answers12

21

CSV files are character separated value files, not necessarily comma separated. For more than half the world the separator character is a semicolon (;), not a comma (,)

Excel 2016 properly respects your Windows regional settings, and uses the specified List Separator character

One solution is to change your regional settings for the List Separator attribute to the character you want Excel to default to using, e.g. a comma (,)

This can be changed in the operating system Control Panel, under Region settings, Additional Settings, List separator

For various reasons some people seem to have the incorrect regional settings for the culture they most commonly work in, and therefore have semicolon as the default separator

If you prefer not to change your operating system regional setting to what you think is normal for CSV files, you can change the default behavior in Excel with the Use system separators checkbox under the File/Options/Advanced menu

If you want custom options each time you open a CSV file, use the Data/From Text menu, but this becomes slow and awkward for lots of files

CSV References:

TFD
  • 23,890
  • 2
  • 34
  • 51
  • 1
    You are a legend. I would swear I checked those. Works normally now. Many thanks, may your camel spit diamonds. – Rob Clarke Oct 29 '15 at 16:59
  • 7
    just to be clear for future readers CSV absolutely means COMMA separated values (not character), it's just that Microsoft has decided to ignore that, do their own thing, and break csv files. The same thing happens on macs and you correct it by setting the "Decimal" value under the Advanced button in the Languages & Regions control panel. – masukomi Apr 28 '17 at 18:06
  • @masukomi just for the record, they do absolutely mean CHARACTER separated values. History and culture are the reason (even the Wikipedia article alludes to this). Yes in North America it is very common to be a comma, but in other parts of this wide world the comma is not a suitable character as it is the decimal mark, in fact more countries use the comma than use the point as a decimal mark. Feel free to remove your comment or start your own answer :-) – TFD Apr 29 '17 at 04:05
  • 9
    I'm sorry but a) [the spec (RFC 4180)](https://tools.ietf.org/html/rfc4180) is very specifically "comma" b) Wikipedia says "Many applications that accept CSV files have options to select the delimiter character" that doesn't mean C stands for "character" it means many apps ignore the spec and make life miserable for anyone dealing with these files. Comma being decimal mark is irrelevant because CSV spec handles coma decimal numbers without problem. `"122,45"` as a cell would be handled correctly and NOT split on the comma. – masukomi May 01 '17 at 20:34
  • @masukomi RFC from 2005 for MIME is a proposal, not standard. The Wikipedia section "Specification" point 3. explains it succinctly. CSV files predate the Internet and RFC's etc – TFD May 02 '17 at 01:18
  • 3
    @TFD RFCs are widely accepted as "de facto" standards even if they're not ratified as full standards; and the goal of any standard is communication. RFC4180 documents a common format for software to use to communicate; and it calls that format "comma separated values". You aren't obligated to be _compliant_ with that format (unless you are); so if you want to use a semicolon as your delimiter; go ahead; but don't claim to be CSV-compliant. Nevertheless; the rest of your answer is spot-on; so have an upvote. – JuSTMOnIcAjUSTmONiCAJusTMoNICa May 23 '17 at 20:01
  • 4
    CSV has nothing to do with your regional setting in some OS, or what accountants use to separate things in their world. In the world of data transmission and analysis, CSV always means **Comma** Separated Values (and has for several decades). Excel doesn't really do CSV very well, and it's certainly (yet another) MS brain-fart to use the regional separator character instead of always using a comma. – Reversed Engineer Dec 14 '17 at 10:28
20

I've found a way of saving messy CSV files into a nice table format but I'm not sure if it will work for your case.

Data -> New Query -> From File -> From CSV

By opening the CSV file this way, a pop-up 'Query Editor' window will appear with a nicely organised table format where you can edit, save and load into your excel sheet.

I hope this helps.

Shane
  • 201
  • 2
  • 2
  • Thanks for that. It really helped. I was pulling my hair out with frustration. This solved it for me. Note, in what I would call the 'Import dialogue' (that window appears to have no official name), I had to explicitly select File Origin > `Unicode UTF-8` to set the character encoding. And also select Data Type Detection > `Do not Detect Data Types` to stop Excel from being 'helpful'. – JW. Jan 26 '17 at 19:43
8

For me the solution was to:

Data > From Text > Choose your csv file

Then you can define all the import settings for csv files.

nicruo
  • 508
  • 4
  • 13
4

I found another way to fix this, without changing your windows local settings. In Excel, you go to File > Options > Advanced. Un-check the "Use System Separators" within the Editing Options and change the Decimal separator with "," and the Thousands Separator ".".

Even it does look more like a bug than a feature of Excel 2016, it works without changing the Windows Local settings, and it's just a local Excel change.

eliberator
  • 139
  • 1
  • 5
  • This worked for me, regional options didn't. Make sure you fully close Excel after you make the change then re-open the file. And the original question actually stated their desired delimiter was comma but that was not being respected (same situation for me), so the solution is to set Decimal Separator as "." and Thousands Separator as ",". – Chris SH Nov 24 '16 at 06:11
4

Just had this same problem. Changing the file extension from csv to txt and opening in Excel brings up the classic wizard so you can map the strings to fields.

Joao G. Ferreira
  • 83
  • 7
1

The correct answer is to edit your regional settings as suggested above (if a long term change in behavior is desired)

Control Panel -> Region -> Additional Settings -> List separator:

But for my purposes a simple Edit -> Find and Replace using Notepad to replace all commas with semi-colons was a quick and dirty solution that I preferred.

Despite the comment that csv means 'Character Separated Values', in Office 2016 my .csv file association to Excel still says 'Microsoft Excel Comma Separated Values File'.

HadiRj
  • 1,015
  • 3
  • 21
  • 41
plumdog
  • 355
  • 5
  • 9
  • Of course, searching for comma and replacing with semicolons is bad for two reasons: **1.** It will mess up any fields that really contain semicolons, as those would need to be quoted in the resulting file otherwise the semicolons will be viewed as delimiters, not as part of the data in the field, and **2.** CSV are supposed to use commas as delimiters, not semicolons ("C" stands for Comma!), so it's better to discipline Excel until it obeys, instead of being lenient and propagating the problem. – Reversed Engineer Apr 26 '22 at 09:49
1

I have quite a complex csv file where none of the suggestions worked out for me. So I ended up using LibreOffice Calc for the job. It worked like a dream.

Kantonus
  • 43
  • 3
1

I had the same problem, I fixed it in this way (Excel 2020)

Data -> Text to Columns

Now you can configure as you wish the CSV delimiters/endlines...

Text to Columns

Suraj Rao
  • 29,388
  • 11
  • 94
  • 103
Gabriel Terry
  • 55
  • 1
  • 6
0

I had the same issue on Mac OS X El Captain. The answer given here worked for me. Reproducing it here in case the link doesnt work in future:

  1. Close the Excel application
  2. Click on the Apple button
  3. Select System Preferences
  4. Select Language and Region
  5. Click Advanced
  6. Change the Decimal separator from a comma (,) to a full stop (.)
  7. Then click on Ok/Save
  8. Test the Excel import again
Anupam
  • 14,950
  • 19
  • 67
  • 94
0

When changing the list separator, make sure it doesn't overlap with the decimal symbol and the digit grouping characters. I had to change my list separator to (,) my decimal to (.) end my digit grouping to ('). Now .CSV opens lekker!!!

user10127576
  • 1
0

In my excel, it's: data> get data> from file> from text/csv

Zohar Levi
  • 614
  • 6
  • 16
-2

Try opening in excel, then using text to columns, based on commas. You could probably create some simple vba to open it in that way too.

Henry W
  • 212
  • 1
  • 7
  • Well, that works, thanks. However, it's still a couple of extra steps that I haven't had to do before. It would be nice if it opened a CSV properly without any intervention, just like on my other machine, running exactly the same version of Excel. Anyone got any other suggestions? – Rob Clarke Oct 29 '15 at 02:37