Ask the Expert

The process of importing data into a spreadsheet

What is the process of importing data into a spreadsheet, and how do those ODBC drivers operate?

Because I'm not sure if you are asking what you need to do, or what ODBC is, I will give you the definition, and then the process. Open Database Connectivity (ODBC) is a widely accepted application-programming interface (API) for database access. It is based on the Call-Level Interface (CLI) specifications from X/Open and ISO/IEC for database APIs and uses Structured Query Language (SQL) as its database access language. This allows applications programs, scripts (JavaScript, VBScript), dataset object scripts and other CLI compliant services to access databased on a single type of access.

If you are importing into Microsoft Excel, you can create an SQL script that will import data directly from Universal DB2/400. In order to allow the scripting language (SQL - in this case) to make the CLI calls, you must provide information based on the ODBC driver you are using, the database you are connecting with, security information, the object you wish to operate against, and what you wish to do to that object. The good news is that most of this is done through selection screens.

If you are using Excel 2000, then select the menu option Data, Get External Data, New Database Query. You will be prompted for the Database (Based on the ODBC drivers installed on your system). Select the database that defines you AS/400 such as QDSN_AS400NAME. When you click OK, you will be connected to your DB2/400 Database. You may then select the file, fields and enter sorting, grouping and selection criteria. Once completed, you will be prompted to Return Data to Microsoft Excel. Click Finish. You will be prompted for the cell you wish to begin, and the import begins. Just remember that you can only import 65536 rows (records) of data.

This was first published in April 2001

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: