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.