Opening CSV files in Excel

CSV (Comma Separated Values) is a useful file format for tabular data that I often use. But every time I want to load a CSV file into Excel it does not seem to recognize the files. Usually it just loads the entire file into a single column. Irritating, but now I finally figured out why!

It turns out the deliminator used to separate the columns in a CSV is determined by the localization settings in Windows. On my system, even though my Windows is English, my locale is set to Swedish. In this locale, the “List Separator” is not comma, it’s semicolon. So Excel will look for semicolons instead of commas, and simply not recognize the commas in the CSV as column deliminators. (I don’t know why you would want localization to affect a file format. It seems odd to change how a file format works based on locale. But that’s Microsoft for you…)

Ok, so how do you fix this then? Here are some solutions I’ve figured out that all work with my Excel 2010 on Windows 7:

• I’ve found that if you change the file extension from .csv to .xls and then open the file in Excel, it will first warn you that the file may be corrupt, but then show a dialog allowing you to load the file and manually set the delimiter. Note that the extension should be .xls (not .xlsx) and that you must open the file from inside Excel (i.e. do not double click the file to open it).

• It is also possible to import the CSV data into Excel. Start Excel and make sure that you have an open workbook. Then go to the Data tab on the ribbon and select From Text. Now you can open the CSV  and a dialog will show where you can select what deliminator to use. Finally you get to paste the imported data into your spreadsheet.

• Of course you can always modify the CSV file and replace the commas with the character prescribed by the system settings. This can easily be done using the Replace All function found in most Text editors, such as Notepad2.

• A more permanent solution is to change the list separator in your regional settings: Control panel -> Region and Language -> Formats tab -> Additional Settings -> List separator. Note that this will affect all applications using the localized settings. I don’t know where else this deliminator is used.

• Finally, if you already have a CSV file that Excel loaded as a single column, you can tell Excel to split this data into multiple columns like this: Select the column with the data by clicking on the column header. Next go to the Data tab in the ribbon and click Text to Columns. In the dialog that appears, select your delimiter and complete the wizard.

As you can see there are many way to get around the problem. I find that each has its pros and cons, and you have pick the solution that works best for your situation.