Import DB2 table data into a spreadsheet

An illustrated example

This article describes how to import DB2® for Linux®, UNIX®, and Windows® table data into either a Microsoft® Excel spreadsheet or a Lotus® 1-2-3 spreadsheet. Using step-by-step examples, learn how easy it is to import data to create meaningful charts and graphs.

Amar Thakkar (athakkar@au1.ibm.com), Team Leader - DB2 UDB Advanced Support, IBM

Author photo: Amar ThakkarAmar Thakkar is the Team Leader for DB2 UDB Advanced Support in the Asia Pacific region. He has worked in the DB2 Support team for over 5 years and has extensive knowledge in the engine area. Amar currently works as part of the Down Systems Division and has extensive experience in the diagnosis and resolution of customer problems.



29 June 2006

Introduction

Having worked in DB2 for Linux, UNIX, and Windows support for a few years, I have noticed that there is seldom an easy way to view data contained in tables in a readable format or one which can be used to generate graphs or charts easily without the need for complex tools. Many customers also ask me, "What is the quickest and easiest way to view or parse DB2 data so that I can generate graphs and charts to make sense of the data?" Common end-user applications such as Microsoft Excel and Lotus 1-2-3 can be used to import this data. This article will show you how to use ODBC / OLE-DB and .wsf files to import DB2 data.

Mechanisms

Open Database Connectivity (ODBC) - An industry API which allows applications to transparently connect to data sources and access the data. An IBM DB2 ODBC driver is provided with DB2 installations ranging from the Runtime Client to Enterprise Server Edition (ESE).

Object Linking and Embedding for Databases (OLE DB) - A Microsoft API which allows applications to transparently connect to and access a data source. An IBM OLE DB driver is provided with DB2 installations ranging from the Runtime Client to ESE.

Worksheet File Format (WSF) - From the DB2 Information Center: Numeric data in WSF format files is stored using Intel machine format. This format allows Lotus WSF files to be transferred and used in different Lotus operating environments. As a result of this consistency in internal formats, exported WSF files from DB2 products can be used by Lotus 1-2-3.


Importing into an Excel spreadsheet using ODBC

To import into an Excel spreadsheet using ODBC, follow these steps:

  1. Open up an Excel spreadsheet and select New Database Query.
    Figure 1. New database
    New database
  2. If this is the first time data is imported into the Excel using this method, then Microsoft Query needs to be installed, as this may not be installed by default.
    Figure 2. Microsoft Query
    Microsoft Query
    Figure 3. Microsoft Query
    Microsoft Query
  3. Now we need to define the data source that we will be extracting the data from which is to be imported into the spreadsheet. So lets choose New Data Source if has not been defined.
    Figure 4. Define the data source
    Define the data source
  4. We will be using the IBM DB2 ODBC driver provided as part of the DB2 installation. In this example DBEXCEL is the name of the database. Click on the 3rd field ‘Connect’ in the dialog box below and select the database that we will be connecting to.
    Figure 5. Select database
    Select database
  5. This dialog box will appear when we click on Connect above. Select the database alias and provide the user credentials needed to connect to the database.
    Figure 6. Logon dialog
    Logon dialog
  6. Select which columns we would like to import. In our example, let's choose all the columns in the table.
    Figure 7. Select columns
    Select columns
  7. Apply additional filtering if required. In our example, let's just leave this as it is.
    Figure 8. Filter data
    Filter data
  8. No sorting of the data is required at this stage, however sorting data by columns can be useful.
    Figure 9. Sorting data
    sorting data
  9. We want to return this data to Microsoft Excel.
    Figure 10. Finish dialog
    Finish dialog
  10. At which location in the spreadsheet do we want to start importing the data? Let's leave it as the default of $A$1, the top left hand corner.
    Figure 11. Where to import the data
    where to import the data
  11. In order to import the data we need to establish a connection to the external data source.
    Figure 12. Connect dialog
    Connect dialog
  12. We can now see the data imported into the spreadsheet ready to be analysed.
    Figure 13. Data in spreadsheet
    Data in spreadsheet

Importing into an Excel spreadsheet using OLE-DB

To import into an Excel spreadsheet using OLE-DB, follow these steps:

  1. Open an Excel spreadsheet and select Import External Data from the Data menu.
    Figure 14. Data menu
    Data menu
  2. Choose Connect to New Data source.
    Figure 15. Select data source
    Select data source
  3. Select Other / Advanced.
    Figure 16. Data connection wizard
    Data connection wizard
  4. Select IBM OLE DB Provider for DB2, then click on Next.
    Figure 17. Data provider
    Data provider
  5. Test the connection with the user credentials provided to ensure that you can connect to the database.
    Figure 18. Test connection
    Test connection
  6. Now you can connect to the database.
    Figure 19. Connect dialog
    connect dialog
  7. Select the table that you are going to import.
    Figure 20. Select table
    select table
  8. Save the session if you like, otherwise click on Finish. Select where to insert the data and you should now have the data imported into the Excel spreadsheet.
    Figure 21. Data in spreadsheet
    Data in spreadsheet

Importing into a Lotus 1-2-3 spreadsheet using ODBC

To import into a Lotus 1-2-3 spreadsheet using ODBC, follow these steps:

  1. Open up an Lotus 1-2-3 spreadsheet and select Query Table.
    Figure 22. Query table
    Query table
  2. Select external table.
    Figure 23. Query table assistant
    Query table assistant
  3. The database that we will be using is IBM DB2 (*), then click on Open.
    Figure 24. Select DB2
    Select DB2
  4. Create an ODBC data source if there isn’t one already defined.
    Figure 25. Create an ODBC data source
    Create an ODBC data source
  5. To connect to this data source, supply the user ID and password credentials required for connection.
    Figure 26. Connect to database dialog
    Connect to database dialog
  6. Now you can connect to the database. In this example, the userid is athakkar and the database is DB2.
    Figure 27. Open the database dialog
    Open the database dialog
  7. Select the schema in which the table resides.
    Figure 28. Select schema
    Select schema
  8. Select the table that we will be importing data from.
    Figure 29. Select table
    Select table
  9. Select the columns.
    Figure 30. Select columns
    Select columns
  10. We can now see the imported data in the Lotus 1-2-3 spreadsheet.
    Figure 31. Data in spreadsheet
    Data in spreadsheet

Importing into a Lotus 1-2-3 spreadsheet using .wsf files

To import into a Lotus 1-2-3 spreadsheet using .wsf files, follow these steps:

  1. First, export the data from the database using the export command. For example:
     db2 export to syscat.tablespaces.wsf of wsf select * from syscat.tablespaces
  2. Open up the Lotus 1-2-3 application. Select Open and choose All Files for File Type.
  3. Using my above example I will select syscat.tablespaces.wsf and click on Open.
    Figure 32. Open file
    Open file
  4. You should now have the data imported into the spreadsheet.
    Figure 32. Data in spreadsheet
    Data in spreadsheet

Conclusion

This article explains how to import data into Microsoft Excel and Lotus 1-2-3 in a quick and easy manner. After importing table data a few times you will not even have to refer to this article as the process is quite simple. This should allow you to quickly formulate graphs and charts. If you have defined table functions to gather various snapshots or have defined event monitors that are being written to a table, then importing this data allows you to generate a graphical representation of your monitoring data based on columns quite easily, especially for those familiar with Excel or 1-2-3.


Acknowledgements

Special thanks to Connie Lam from DD2 UDB Advanced Support for providing her input and reviewing this article.

Resources

Learn

Get products and technologies

  • Download a free trial version of DB2 Enterprise Edition.
  • Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edtion and provides a solid base to build and deploy applications.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=144125
ArticleTitle=Import DB2 table data into a spreadsheet
publish-date=06292006