Working With CSV Files in MS Excel

Don't open...import delimited

Posted by AgileCoder on July 6, 2012

In my day job I have to deal with a lot of spreadsheet-type data stored as Comma-Separated Values.  One way to work with these files is in a text editor like Windows Notepad, or my favorite editor Notepad++.  However, sometime you want to have the power of Excel to do data analysis, formatting or sorting.

The problem is that when you "open" a file saved as a .csv with Excel you can run into data formatting problems as Excel tries to render the data in the most logical format. Here is an example.  I have a file of occupational data that looks like this:

Occupation,Occupation Title
"00-0000","Total, All Occupations"
"11-0000",Management Occupations
"11-1000",Top Executives
"11-1011",Chief Executives
"11-1021",General and Operations Managers
"11-1031",Legislators
"11-2000","Advertising, Marketing, and Sales Managers"
"11-2011",Advertising and Promotions Managers
"11-2021",Marketing Managers
"11-2022",Sales Managers
...

When I open the file in Excel, it mangles the occupation codes, trying to convert them to dates:

Occupation	Occupation Title
00-0000	Total, All Occupations
11-0000	Management Occupations
11-1000	Top Executives
11-1011	Chief Executives
11-1021	General and Operations Managers
11-1031	Legislators
Nov-00	Advertising, Marketing, and Sales Managers
Nov-11	Advertising and Promotions Managers
Nov-21	Marketing Managers
Nov-31	Sales Managers

The solution (in Excel 2010) is to:

  1. Open a blank workbook, then choose File > From Text.
  2. Choose your .csv file and click "Import".
  3. Choose the "Delimited" file type and click "Next".
  4. Enter the appropriate Delimiters until the Data Preview shows you the right column breaks and click "Next".
  5. Pick your 'problem' columns and change the "Column Data Format" to Text.
  6. Click "Finish".