Using OpenOffice Base to bring a SQL table into a Spreadsheet

This was rather simple, actually. The task at hand was bring down information from an iSeries database into a format I could use use on another system. For this kind of quick and dirty work, I like to use a spreadsheet. We’re only talking about 1800 rows of data or so, and of those, I really only needed 2 columns.

1. Create an ODBC source via your Control Panel. If this is over your head, you’re in the wrong place. In my particular case, the source was connected to an IBM iSeries with IBM’s iSeries Access ODBC Driver. That driver is included with IBM iSeries Access for Windows. (Everything IBM has incredibly long names. Take “IBM WebSphere Development Studio Client for iSeries,” for example, which is both the name of the program and the name of the folder containing it. To be fair IBM has since renamed it “IBM Rational Application Software Delivery Platform.” Why not just call it Eclipse and be done with it?)

2. Start OpenOffice, and use the “Connect to an existing database” option. This gives you a pulldown from which you’ll select ODBC, and browse to the source you created in step 1. You’ll be prompted to “save the database,” which produces a file OpenOffice calls a “Database document.”

3. Base opens up its database view. In the tables section, navigate to the table you want to copy. Right click on the table, and then copy.

4. Open up OpenOffice Calc. In cell A1, paste. Boom! The table’s data is in your spreadsheet.

And of course this might freeze up everything if there’s too much data to fit over your VPN. Not to worry – you can always create a View to narrow down the fields to just what you need. The same instructions apply for a View as for a Table.

Facebook comments: