SPREADSHEETS - 07.10.2011

Too much data?

Do spreadsheets have limits?

In each sheet. Earlier releases of spreadsheet software may well have data processing limits. For example, Excel 2003 has a limit on the total number of rows you can have in a worksheet - 65,535. It is possible, however, to have a raw data file from another program that has more than this maximum number of rows. Even in Excel 2007 you may run into the limits of your system if you try to import a file that has hundreds of thousands of rows. If you solve the capacity issue, you may still want to restrict the number of rows of data in each of your worksheets in order to make inspecting the data visually more manageable (not a bad idea given the potential volume involved). There are several ways to do this...

Macro Excelnomics

Row by row. In Excel, a macro is a series of commands and instructions that you group together as a single command to accomplish a task automatically. So you could use a macro to fetch each row from the source file and place it in a new row of the worksheet. The macro can be set to automatically overspill data into a new worksheet once your specified maximum number of rows has been reached (see The next step).

Tip 1. A macro opens with reference to a file (in our example “MyFile.txt”). Make sure your macro will open the real source file you want to import.

Tip 2. Two variables you’ll need to insert into any such macro program will be the settings of “sDelim” and “MaxSize”. The first specifies what character separates each field of data (for example, in a CSV file this would be a comma). The second specifies the maximum number of rows you want on each worksheet. Naturally, you won’t want to set MaxSize greater than whatever your version of Excel will allow.

Tip 3. The macro will probably need to assume that you have enough worksheets already in your workbook to contain the data (in our example, they are called “Sheet” and numbered sequentially i.e. Sheet1, Sheet2, Sheet3, etc.). So make sure your workbook does have enough by creating new sheets in it before you import the data.

Simple solution

Copy and delete. There is an alternative solution which doesn’t rely on programming skills. You could simply save copies of the raw text file (the one you want to import) and then cut the size of each file down by deleting any unwanted data.

For example. Let’s say you are operating under the 65,535-row limit and you have a total of 120,000 rows you need to import from your source file into Excel.

Step 1. Make two copies of the raw text file (e.g. a CSV file).

Step 2. This involves you deleting the second half of the first text file and the first half of the second. Thus, you can import the first file (now 60,000 rows) into one worksheet and the second file (also 60,000 rows) into the second.

For details of a macro to help you fit large amounts of data into an Excel workbook, visit http://financialcontroller.indicator.co.uk(FC 04.01.11).