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
Levitra Soft Cialis Professional Levitra Original Cialis Daily Red Viagra Cialis Jelly Acheter Levitra Viagra Professional Viagra Gold - Vigour Kamagra Jelly Viagra pour femme viagra kaufen cialis kaufen Erektile Dysfunktion Viagra Professional Viagra Jelly cialis online kopen Levitra Soft viagra voor vrouwen Viagra Soft
Prezzo Levitra Acquisto Red Viagra Acquisto Cialis Disfunzione Erettile Dopo Prostatectomia Radicale Acquisto Propecia Cialis Generico Viagra Originale senza ricetta Comprare Red Viagra Comprare Levitra Generico On Line Comprare Levitra Online Disfunzione Erettile Blocco Psicologico Levitra Vendita Comprare Propecia Acquisto Female Viagra Acquisto Cialis Jelly Viagra Super Active Levitra Originale Viagra online Levitra Compresse Divisibili Disfunzione Erettile Ecografia
Viagra Soft kamagra oral jelly cialis generisk viagra online viagra online Generisk Viagra Cialis Online Kvinnor Cialis Generisk Cialis Kvinnor Viagra Cialis Super Active Cialis soft viagra apoteket Kamagra Cialis Soft Viagra sverige Kamagra Gel Viagra Soft kamagra oral jelly Kamagra Online
kamagra gel comprar kamagra cialis pas cher cialis prix viagra generico cialis generico cialis prezzo comprare cialis cialis online viagra online
Levitra Vendita levitra Generico Farmacia On Line Vicenza Comprar Cialis Comprar Cialis comprar Kamagra Cialis Super Active Comprar Levitra Cialis Suisse Acheter Levitra Acheter Propecia viagra schweiz cialis kaufen Kamagra Bestellen erectiepillen kopen Cialis Kopen Viagra Viagra Professional Viagra Jelly cialis online kopen Levitra Soft
nike blazer michael kors Ray ban nike huarache Adidas Scarpe Abbigliamento Asics nike flyknit scarpe converse Scarpe air jordan scarpe da calcio nike free nike cortez nike roshe air max new balance oakley converse Scarpe Adidas scarpe Nike Scarpe louis vuitton Ray ban adidas Adidas superstar longchamp air max Cinture scarpe Puma Puma Scarpe Scarpe nike air max scarpe asics nike air force Saucony polo camicie Polo Ralph Lauren