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:
- Open a blank workbook, then choose File > From Text.
- Choose your .csv file and click "Import".
- Choose the "Delimited" file type and click "Next".
- Enter the appropriate Delimiters until the Data Preview shows you the right column breaks and click "Next".
- Pick your 'problem' columns and change the "Column Data Format" to Text.
- Click "Finish".