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.

Read More

Counting unique entries in a range

A good source of hints and tips for OOo Calc can be the numerous Excel blogs. One of the best is from Dick Kusleika.

This formula for counting the number of unique items in a range of cells was one of three suggested by Dick – but the only one that ported successfully to OOo Calc.

It is an array formula. Use Ctrl-Shift-Enter when entering it.

=SUM(IF(FREQUENCY(IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);””); IF(LEN(A1:A10)>0;MATCH(A1:A10;A1:A10;0);””))>0;1))

where A1:A10 is the range being analyzed..

Read More

Array Formulas: Working with Matrices

An array is a linked range of cells on a spreadsheet containing values. A formula in which the individual values in a cell range are evaluated is referred to as an array formula.

Not only can an array formula process several values, but it can also return several values. The results of an array formula is also an array.

The OOoCalc functions that are introduced here are TRANSPOSE, MMULT, and MINVERSE.

As an introduction to array formulas – we introduce one of the builtin array functions – TRANSPOSE, which as the same suggests – transposes the rows and columns of the original array to the target array.

In the example below, we wish to transpose A1:D3 to B6:D9.

With B6 selected – enter =TRANSPOSE(A1:D3) – followed by Ctrl-Shift-Enter. OOo Calc takes care of the rest! Note that in the Input Window – the function is embedded within { } indicating an array function. These are automatically added by OOo Calc.

OOo Calc also has a builtin function for multiplying matrices – MMULT. The use of this function is demonstrated in the example below.

We use Insert->Names->Define to define the two product matrices as Matrix1 and Matrix2. Using defined names to define and manipulate matrices is good practice – as is highlighting the matrices with different background colors.

To calculate the inverse of a matrix, we use the MINVERSE function. This is illustrated below.

We can now apply our knowledge of matrix manipulation in OOo Calc to solve for a system of linear equations. The technique is illustrated in the example below.

Read More

Webquery: Scraping stock quotes from MSN

In a previous entry we imported data from an HTML file on the local disk. In this example, we will extract stock quotes from an MSN site and import the data into OOo Calc spreadsheet.

We will import the stock quote information from this MSN webpage. A screen shot is shown below.

For this example, we import the data into the spreadsheet as before with Insert – External Data . The URL is;=INTC and the table we need to select is HTML_15.

You also need to make sure your proxy server settings are correct under Tools – Options (Internet)

Now, we can choose to have OOo Calc import this data at predefined regular intervals. This is useful – given the constantly changing nature of the stock prices. Also, each time that the user opens the document, OOo Calc will prompt to update the external links. Basically, what we have created is a ‘window’ to an external document. It can be a snapshot – or it can regularly monitor the target – updating accordingly.

Having extracted the data from the external website in it’s’raw’ form, we can use VLOOKUP to grab any piece of data we want. This is shown below.

Read More

Webquery : Importing HTML tables

With the help of the Web Page Query Calc import filter, you can insert tables from HTML documents in a Calc spreadsheet.

Let us step through the relatively straightforward process of importing data from tables in a HTML document.

The data for this example was obtained from an excellent website with tons of population data from all over the world :

Here is what the source HTML file looks like : swiss.html

For this example, we will be importing awebpage from the local disk.

The External Data dialog is activated by Insert-External Data.

Once we fill in the path to the local copy of the HTML file and the table inside this file we wish to import – we hit OK and we are done – it is that easy.

Read More

Secret StarWars games

Open Office Calc has an interesting Easter egg – a hidden feature that might not be obvious or documented. Programmers sometimes bury Easter Eggs in their programs or web sites to add extra depth and challenge users to find them.

In an empty cell, type the following formula..=GAME(“StarWars”) This is what you will see…

After you have selected your hero, the game begins. Another screen shot…

You can only play the game once. To play again – exit OOo Calc and the quickstarter before restarting.

Read More

A Sudoku Solver!

I have been wanting to do this for the longest time – ever since I became aware of this puzzle last year. So for the past week, I’ve been busy putting together this spreadsheet that will assist you with solving a Sudoku puzzle.

Anyway, three days and 900 formulae later, I present… ooo_sodoku.

This is not a push-button solver. Perhaps the next version will allow that. Basically, for each square in the solution grid, the solver will determine all the possible values – saving you a lot of the drudge work associated with these puzzles.

As you manually enter the values in the solution grid – top left, the main grid in the center will display one of three colors for each square. Light blue indicates that square already filled in solution grid. Green indicates only one possible value – which has yet to be entered in the solution grid, and brown indicates more than one solution

Your comments, encouragement and feedback are always welcome. As I said earlier, there are 900+ formulae here – so bugs are possible.

Read More

Basic functions : SUMIF

This short tutorial illustrates the two basic ways that SUMIF can be used in a spreadsheet.

The SUMIF function takes two or three arguments.

For the two argument version, the condition is applied to each cell in the range being SUMmed. In the example below, C9 is the sum of all values in C3:C8 that are greater than 5.

In the three argument version, the condition is applied to a separate range of cells. This range can be numerical of textual. In the example below, the values corresponding to Tom are summed in C10

Read More

Conditional Formatting II

To illustrate the application of conditional formatting, we consider the need to highlight dates in a spreadsheet that are fast approaching or are already past.

The original spreadsheet without highlighting.

Before we apply conditional, we define any custom cell formats we may need. In this case, we have cells highlighted orange and cells highlighted yellow. We open the conditional formatting dialog and evaluate two conditions. The order is important here. In the conditional formatting dialog, once a cell evaluates TRUE, it will not be evaluated again. With that in mind, we first evaluate if a date is past due. We then evaluate if a date is fast approaching.

The modified spreadsheet with conditional formatting applied. Using this technique, the spreadsheet has become more readable and any key dates or deadlines can be more readily identified.

Read More

Conditional Formatting: Comparing two Lists

We will use conditional formatting to identify unique entries between two lists.

In the sheet below, we have two lists of US States. We would like to identify the list entries that are unique to each list. This technique can also be applied to two versions of the same list to identify additions/removals.

Step 1: Select B2:B19 and open the Conditional Formatting dialog – below. We have already defined a formatting style that has a darker background.

In the Conditional Formatting dialog, select the “Formula is”option from the drop down list on the left and enter the formula as displayed below. Choose the predefined style. Exit the dialog.

We apply similar conditonal formatting to the D2:D19 array. I will leave that as an exercise for the reader.

The result after the conditional formatting is applied. Those cells that are shaded dark do not have a matching entry in the other list.

Read More
  • 1
  • 2