Solved! Converting an OpenOffice.org Calc Sheet to OOBase

LXer Feature: 27-Aug-2006 I had developed a fairly hefty OpenOffice.org Calc sheet, which was great for quickly entering data I've been working with lately. However, it soon became clear that a switch to ooBase would be necessary. I'm not a big user of ooBase, so wasn't sure how best to go about converting my data. Fortunately, a tutorial someone else had written showed me just how simple it is.

The Problem

I had developed a fairly hefty OpenOffice.org Calc sheet, which was great for quickly entering data I've been working with lately. However, it soon became clear that a switch to OOBase would be necessary. I'm not a big user of Base, so wasn't sure how best to go about converting my data. Fortunately, a tutorial Tom Boyd had written showed me just how simple it is. Even so, I discovered a 'gotchya' along the way.

The General Approach

First, you'll want to be sure your Calc sheet is formatted appropriately for the conversion. Second, you'll need to create a DB in Base, or open an existing one. Third, you'll literally copy and paste the Calc sheet data into Base.

Formatting Your Calc Sheet

Your Calc sheet should have Column Headings in the top row of the spread sheet. In anticipation of needing to move to a DB format, I had taken the liberty to create my own primary key field, using a simple formula to create an "Auto-Increment" effect in Calc. I had done the same thing when I designed CHADDB for MySQL a couple years ago. However, it caused problems when I tried to use that field as the primary key in the conversion process. So I deleted it in favor of Base's primary key mechanism.

You also need to be sure all your cells use the proper data formats. For example, I had been typing in "Yes" or "No" for some values, but left it as text. If you want to take advantage of Base's Boolean Yes/No feature, you need to make sure you change these cells to Boolean format. Select the cells in that column, and Right-Click. Choose Format Cells from the context menu, and then choose the Numbers tab. Select Boolean Values from the listbox and click O.k. I also changed my values from 'yes' and 'no' to 'TRUE' and blank.

Tom's tutorial mentioned potential problems with date conversions, but using the standard mm/dd/yy format in Calc seemed to work just fine. I haven't tried other date formats yet, nor have I tried using other cell formats in Calc. If in doubt, try it out - on a test DB or test table - before making the actual conversion.

Stepping Through the Conversion

Once you have your Calc sheet formatted properly, it is literally a matter of selecting the cells and then copying and pasting them into Base. Open your database, or create a new one. Select the cells in Calc and copy them. In Base, right-click on the Tables frame, and choose Paste. This brings up the conversion dialog.

The conversion dialog steps you through the process. Enter a name for the table. Data and Definitions is selected by default, which is exactly what we want. Check the box for Create a Primary key. Click Next. Now you can choose which columns you want. You can click the >> button to choose all the columns, or pick and choose those that tickle your fancy. Click Next.

The converter doesn't automatically detect the formatting of the Calc data. The default setting for everything is "Text" with 255 characters. That means you need to make sure you change the field for integers, dates and other data types. Tom says using "Decimal" and incrementing the number of decimal points worked for his financial data.

Once you've set the data types appropriately for each field, you can click Create to complete the process. I had over 600 rows of data spanning 50 columns. On my 1.2GHz laptop with 256MB of RAM and other apps running, it took a minute to complete. But it was still pretty fast. Just don't expect an instantaneous conversion if you have a lot of data. Now I can perform all those calculations that would have been difficult in Calc. If I need to play with those results further, well, Calc still has a job.

References

• Tom Boyd's tutorial