Get Excel 2013 To Recognize The Comma Delimiter In A CSV File
If you have MS Excel installed on your system, any CSV file you download is automatically associated with the program. Excel is more than capable of opening and editing a CSV file, the only problem is the way it reads the data in the file. By default, Excel is not set up to recognize the comma as a delimiting character. The result is, whenever you open a CSV file, the data looks all wrong. The data in rows is successively added to columns instead of staying in its CSV defined layout. It’s hard to read and even harder to work with. Here’s a simple way to get Excel to read and display the data correctly.
Open a new Excel file and go to the Data tab. Look for the ‘Get External Data’ set of tools and select ‘From Text’. Browse for and select the CSV file you have.
Excel will then open the following dialog box. Skip past the first step but make sure the ‘Delimited’ option is selected under Original Data Type. In the second step, uncheck ‘tab’ and select ‘Comma’. Click Finish and wait for Excel to load the data.
We haven’t found a way to set this as the default and it seems to only work when importing data. You cannot first open a CSV file and then change the delimiting character from the Data tab as it can only deal with one column at a time. This means every single time you want to see your CSV file open and the data displayed correctly, you will have to import it into a fresh Excel file. It’s inconvenient but not unmanageable.
You think in this day and age, Microsoft would make this a simple endeavour. I don’t understand why you have to jump through hops to get a CSV file imported???
Someone pointed me to this simple solution:
Add “sep=,” to the first line of your CSV file – without the quotes
You can change the list separator character on your regional settings
in Windows 10, that fixed if for me as I had problems exporting xlsx
files onto csv as it generated a “semi colon” separated values file…
Thanks – that solved it for me!
You sir are a gentaleman and a scholar!
easy thanx
If you change the file exension to “.txt” instead of “.csv” and open it in Excel, it will prompt the dialog that lets you choose the delimeters and other options.
You can easily break the data into columns AFTER opening a CSV. Just select the first column (the only one containing) data, then click on the Data tab, click on the Text to Columns button, select Delimited and on the next screen choose Comma or whatever character is used as a field delimiter.