« Array Formulas: Working with Matrices | Main | Data Filtering : Standard Filter »
October 12, 2004
Importing Tabular Data : CSV Files
If the various OOo Calc forums are an indicator of what gives Calc users the most grief, then importing data into Calc via CSV (coma separated values) files is up there with charting as one of the most error prone tasks.Before you can perform data analysis with OOo Calc, you first need to import the data into the program. Supposedly, one of the more straightforward mechanisms for doing so is via CSV files. These are text files whose files have defined separators tab, comma, semicolon etc) that allow easy mapping from the CSV file into the cells of the spreadsheet.
In this tutorial, we will import some French population data into OOo Calc. The popdata.csv file is listed below.
Alsace, ALS, Strasbourg, 8280, 1566048,1624372, 1734145
Aquitaine, AQU, Bordeaux, 41308, 2656544, 2795830,2908359
Auvergne,AUV, Clermont-Ferrand, 26013, 1332678 ,1321214, 1308878
Basse-Normandie, BAS, Caen, 17589, 1350979 ,1391318, 1422193
Bourgogne, BOU, Dijon, 31582, 1596054 ,1609653, 1610067
Bretagne ,BRE, Rennes, 27208 ,2707886 ,2795638, 2906197
Centre, CEN, Orléans, 39151 ,2264164, 2371036, 2440329
Champagne - Ardenne, CHA,Châlons-sur-Marne, 25606, 1345935, 1347848, 1342363
Corse, COR, Ajaccio, 8680 ,240178, 250371, 260196
Franche-Comté, FRA, Besançon, 16202, 1084049, 1097276, 1117059
Haute-Normandie, HAU ,Rouen, 12317, 1655362, 1737247, 1780192
Île-de-France, ILEk, Paris, 12012, 10073059, 10660554, 10952011
Languedoc - Roussillon, LAN ,Montpellier, 27376 ,1926514, 2114985, 2295648
Limousin , LIM, Limoges, 16942, 737153, 722850, 710939
Lorraine, LOR, Nancy, 23547 ,2319905, 2305726, 2310376
Midi - Pyrénées, MID, Toulouse, 45348, 2325319, 2430663, 2551687
Nord - Pas-de-Calais, NOR, Lille, 12414,3932939 ,3965058, 3996588
Pays-de-la Loire, PAY, Nantes, 32082, 2930398, 3059112, 3222061
Picardie ,PIC, Amiens, 19399 ,1740321, 1810687, 1857481
Poitou - Charentes, POI, Poitiers, 25810, 1568230, 1595109, 1640068
Provence - Alpes - Côte d'Azur, PRO, Marseille, 31400, 3965209, 4257907, 4506151
Rhône - Alpes, RHO, Lyon, 43698, 5015947, 5350701, 5645607
From the File-Open dialog, we select File type TEXT CSV (*.csv *.txt) We then select the text CSV file we wish to import.
The Text Import dialog opens - which will give you a preview of your imported data. The default separator is comma, and fixed field width is unselected, so we do not need to change any of the settings in this window. Click OK
Now you can inspect the imported text data, making any formatting modifications that you deem necessary. Note that the width of the columns have been optimally sized by OOo Calc.
When it comes time to save your data and/or exit the OOo Calc application, you will be given the option of saving as a CSV file. If you do opt to save as a text CSV file, you will lose any formatting modifications that you made to the imported data.
Posted by Dave at October 12, 2004 04:38 AM
Comments
Thank you very much for helping me out.
Posted by: Sujith Menon at January 31, 2005 11:53 AM
Dave,
Thanks for this tid bit. I'm used to MS Excel automatically trying to recognize a file that I'm importing without a specific file extension. I now know that I have to specifically open a .csv or .txt through the file open mechanism.
Paul
Posted by: Paul Brenner at February 1, 2005 03:32 PM
How do I import a text file containing more than 32000 rows in openoffice calc?
Posted by: Milind P Wairkar at February 6, 2005 07:15 AM
My question is, how can I save an OOo text document as a .csv file? As far as I can tell, in the Save As dialog/menu box, .csv is not an option for saving? Can you please explain how to save a OOo text file as .csv.
Also, using the insert external data option in OOo Calc, I can get as far as selecting the file in the URL window, setting the delimiters and the data apppears in the correct rows and columns, but when I choose Okay, the program cycles back to the url selection page. I am using OOo 1.1.1. Has this been fixed in 1.1.4?
Posted by: Terry Liggett at February 9, 2005 02:58 AM
for some reason when i follow these directions, the "Text Import" dialog never opens up. instead i see the "ASCII filter" and then when I press "OK" the csv file opens up in Writer. I'm using 2.0 RC. any ideas anyone? thanks..
Posted by: mjb at October 12, 2005 01:15 PM
My problem is saving an OO Calc csv as a txt file.
I opened a tab delimited txt file with OO, added 6 hours of data and need to save it back as a txt file to upload to a website building program. When I come to do that, I can only save as HTML or (Excel).csv.
I have tried a direct paste into Excel which will save to .txt, but in the process, lots lots of data gets lost.
Any assistance greatly appreciated!
Posted by: Murray at November 4, 2005 09:14 PM