Import Microsoft Access and Excel Data

Home | FAQs & Troubleshooting

 

Expand/Collapse all Show/Hide All

Expand/Collapse item  Overview

Accessing data stored in Access databases, Excel spreadsheets, and other Microsoft Office products is a very common and useful way to use ODBC. Microsoft Access is ideal for storing information for custom applications unique to your business. You can use the Visual Integrator module and ODBC to both import and export data from or to an Access database.

ODBC can also be used to access information stored in a Microsoft Excel spreadsheet. Spreadsheets are convenient for storing a variety of financial information; however, because they are not a database application, you must first prepare the spreadsheet so that ODBC can read the data.

Expand/Collapse item  Before You Begin

  • Before using an ODBC data source, verify that the ODBC driver for the data source is properly installed. If you do not see any ODBC drivers for Access or Excel in the ODBC Administrator dialog box, you must first install the Data Access components. For more information, refer to the installation documentation provided by the database product (specifically reference ODBC setup information).

  • When the ODBC driver is installed, you can create a data source name (DSN) that uses the driver to access a specific database file. Some products automatically create a data source for you.

 

To import Microsoft Access data

  1. Select Visual Integrator Main menu > Import Job Maintenance.

  2. In the Import Job Selection window, enter a job name and table name, and then click Accept. For more information, see Import Job Selection - Fields.

  3. In Import Job Maintenance, in the File Type field, select ODBC Data Source. For more information, see Import Job Maintenance - Fields.

  4. In the Data Source field, select an ODBC data source. Click Tables.

  5. In the ODBC Table Selection window, all tables defined in the Access database are displayed in the Available Tables list box.

  6. Select one or more tables that contain the data you want to access. For more information, see ODBC Table Selection - Fields.

  7. Click the Fields tab. Each column defined for the selected tables appears in the Available Fields list. Select the fields that you want to access.

  8. When you have defined the tables and fields to import, click Accept.

When the job is executed, the data is imported.

 

To import Microsoft Excel data

  1. Select Visual Integrator Main menu > Import Job Maintenance.

  2. In the Import Job Selection window, enter a job name and table name, and then click Accept. For more information, see Import Job Selection - Fields.

  3.  In Import Job Maintenance, in the File Type field, select ODBC Data Source. For more information, see Import Job Maintenance - Fields.

  4. In the Data Source field, select an ODBC data source. Click Tables.

  5. In the ODBC Table Selection window, each named area of the spreadsheet appears as a table in the Available Tables list box.

  6. Select one or more tables that contain the data you want to access. For more information, see ODBC Table Selection - Fields.

  7. Click the Fields tab. Each column description is listed as a field name in the Available Fields list box. Select the fields that you want to access.

  8. When you have defined the tables and fields to import, click Accept.

When the job is executed, the data is imported.