TRENDING

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.

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.

Autocorrect

When entering text in OO Calc cells, you may notice that the program makes assumptions about what you are typing and you get unwanted corrections.

These corrections are completely configurable throught the AutoCorrect dialog.

The AutoCorrect dialog is invoked by selecting Tools – AutoCorrect as shown below.

The Options tab presents some optional corrections that OOo Calc can perform on any text entered by the user. From the identifier of each option, it should be easy to understand what OOo Calc plans to do – if that option is enabled.

In particular, the user can make use of a replacement table of common mispellings. The replacement table is fully configurable via the Replace tab – shown below.For example, if “acn” is a string you use often (a name of a company perhaps) OOo Calc will always insist on replacing it with “can”. By removing this particular entry from the replacement table, OOo Calc will leave “acn” alone.

The Exceptions tab – see below – allows the user to prevent OOo Calc from correcting certain 2-letter initial cap combinations.

Finally, the Custom Quotes tab allows the user to replace single/double quotation marks with any characters of his/her choosing.