IBM Support

Importing Data into OpenOffice Calc With the IBM Toolbox for Java JDBC Driver

Troubleshooting


Problem

This document provides instructions for importing data from an IBM i system into an OpenOffice Calc application spreadsheet.

Resolving The Problem

This document provides instructions for importing data from an IBM i system into an OpenOffice Calc application spreadsheet.

Prerequisites:

1. Apache OpenOffice version 2.0 or later, which is available at the following URL:

http://www.openoffice.org/

2. JTOpen, the open source version of the IBM Toolbox for Java JDBC driver, which is available at the following URL:

http://sourceforge.net/projects/jt400/

Note: All screen captures were taken with OpenOffice.org 4.0.1 on Windows 7.
  • Step 1: Register the JDBC Driver with OpenOffice.org

    a. Start the OpenOffice Calc application.
    b. Select 'Tools' from the 'Options' menu. Select 'Java' in the left pane of the window. Select the Java runtime environment (JRE) to use, and click 'Class Path...'

    c. Click 'Add Archive...'

    d. Browse to the location of the jt400.jar, and click 'Open' to add it to the class path used by OpenOffice.org

    e. Click 'OK' on all open dialog boxes.
    f. Close all OpenOffice.org applications, including the quick launch tray application if open.

  • Step 2: Create an OpenOffice Base application document that uses the IBM Toolbox for Java JDBC driver

    a. Start the OpenOffice Base application. Select the option to connect to an existing database and select the value 'JDBC' from the list. Click 'Next>>'

    b. Complete the connection URL. For example, jdbc:as400://systemname. Set the JDBC driver class to com.ibm.as400.access.AS400JDBCDriver. Click 'Next>>'

    c. Set the username (IBM i user profile) that is used to establish the connection. Check the 'Password required' box and optionally, click 'Test Connection'. The test prompts for a password and test. Click "Next>>"

    d. Leave the default options, and click 'Finish'

    e. Specify a file name for the new database and click 'Save'. If prompted for a password, sign in to the database. Go to Step 3.

    Step 3: Set up a query to access data

    a. In the database document created in Step 2, click 'Queries' and select 'Create Query in SQL View...'


    b. In the Query Design window, enter the SQL statement to run:

    c. Select the menu option File -> Save. Add a name for the query, and click 'OK'.

    d. Close the OpenOffice.org Base application.

  • Step 4: Import data from the query into OpenOffice Calc

    a. Open the OpenOffice Calc application.
    b. Select the menu option 'View -> Data Sources'.
    c. Expand the previously defined database, and expand the Queries section:

    d. Click the previously defined query. If prompted, sign on.
    e. Drag the query to the first cell where you want to import data (usually A1):

    f. Save the document.

Microsoft, Windows, Windows NT, and the Windows logo are trademarks of Microsoft Corporation™ in the United States, other countries, or both.
Java and all Java-based trademarks and logos are trademarks or registered trademarks of Oracle™ and its affiliates.

[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Data Access","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

520146876

Document Information

More support for:
IBM i

Software version:
Version Independent

Operating system(s):
IBM i

Document number:
634709

Modified date:
08 July 2021

UID

nas8N1013028

Manage My Notification Subscriptions

Page Feedback