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-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".

cialis generika kamagra australia cialis preise cialis kaufen cialis bestellen levitra generika viagra online kaufen kamagra bestellen cialis generika kamagra oral jelly kamagra 100mg prix cialis cialis generique kamagra gel viagra prix viagra pour homme acheter cialis kamagra prix viagra pas cher cialis 20mg levitra 20
Levitra Generika kaufen Kaufen Levitra Generika Online Cialis ohne Rezept kaufen Viagra online Viagra Generika kaufen Cialis Soft Tabs kaufen Sildenafil kaufen Apcalis Oral Jelly kaufen Viagra Soft Tabs bestellen Cialis Original kaufen Viagra bestellen Viagra bestellen Propecia Generika kaufen Viagra und seine Analoga Original Testpakete Levitra und Kamagra Erfahrungen Viagra Soft Tabs Kamagra kaufen
kamagra kopen in de winkel viagra werking cialis kopen in nederland cialis bijwerkingen kamagra bijsluiter levitra bijwerkingen viagra kopen apotheek cialis erfaring cialis i norge viagra effekt hva er kamagra viagra nettbutikk levitra eller cialis kamagra gel comprar cialis efeitos secundarios viagra farmacia cialis bula viagra infarmed levitra comprimidos cialis vs viagra cialis flashback kamagra tjejer kamagra effekt levitra fass kamagra oral jelly opiniones levitra generico precio cialis venta kamagra sobres cialis efectos secundarios