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:
"00-0000","Total, All Occupations"
"11-1021",General and Operations Managers
"11-2000","Advertising, Marketing, and Sales Managers"
"11-2011",Advertising and Promotions 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
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".
| Categories: General