Opening CSV files in Excel 2016

Question

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.

Answer 1

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:

• https://en.wikipedia.org/wiki/Decimal_separator (see map of world using comma as decimal point separator, it's very common, and hence CSV's often use semicolon separators)
• https://data-gov.tw.rpi.edu/wiki/CSV_files_use_delimiters_other_than_commas
• https://en.wikipedia.org/wiki/Comma-separated_values (spec point 3)
• https://ec.europa.eu/esco/portal/escopedia/Comma-separated_values_%2528CSV%2529
• https://parse-o-matic.com/parse/pskb/CSV-File-Format.htm

Answer 2

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.

Answer 3

For me the solution was to:

Data > From Text > Choose your csv file

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