Import data from a database by using ODBC

To import data using ODBC, you need a data source. An ODBC data source stores the technical information needed to access the data, such as the driver name, network address, and so on. If you want to do a quick, one-time import from a database such as Microsoft Access, you can use an existing default data source. If you are going to query the same database numerous times, you can create a reusable file data source that contains a shortcut to the database.

Import the data using a default data source

Because these steps use a predefined machine data source to import data, this is the quickest procedure to follow. These steps are for an Access database. If you import data from a different database application, these steps may vary slightly.

  1. Verify that the worksheet to which you are importing data is active. When the query is executed, columns of database data will be added to the right of any existing columns in this worksheet.
  2. Choose File > Query Database (ODBC).
  3. Click the Machine Data Source tab.
  4. Click the data source name you want to use; for example, MS Access Database. Click OK.
  5. If prompted, enter your database login name and password.
  6. Navigate to the folder that contains the database that you want to query.
  7. Click the database so that it appears under Database Name, and then click OK.
    Note

    If you are importing from a dBase file, you will not be able to choose the file here. Click OK, and then choose the dBase file from the Available tables drop-down list. If OK is not available, choose the folder in the Folders list, then click OK. The dBase file name must be 8 characters or fewer, not including the file extension.

  8. Choose the table that you want to query from the Available tables drop-down list.
  9. Choose the fields that you want to import so that they appear under Selected fields.
  10. If you want to import a subset of data, click Use rows, and specify a condition. Click OK.

Create a reusable file data source to import data

If you are using the same database many times, you can create a file data source that contains a shortcut to the database so that you don't have to browse to the database every time. These steps are for an Access database. If you import data from a different database application, these steps may vary slightly.

  1. Verify that the worksheet to which you are importing data is active. When the query is executed, columns of database data will be added to the right of any existing columns in this worksheet.
  2. Choose File > Query Database (ODBC).
  3. On the File Data Source tab, click New.
  4. Choose the driver for which you want to set up a data source; for example, Microsoft Access driver (*.mdb).
    Note

    If the driver that you need is not in the list, you must install the driver before you can complete these steps.

  5. Click Next.
  6. Enter a name for the link, for example, testACCESS.
  7. Click Next and then click Finish.
  8. If you have an older release of Access you may need to specify the system database. Choose Database, click System Database, and then navigate to and select the system database (for example, System.mdw).
  9. Under Database, click Select.
  10. Navigate to and select the database that you want to query so that it appears under Database Name, then click OK.
  11. If prompted, enter your database login name and password.
  12. Click OK in each dialog box until the Query Database (ODBC) dialog box appears.
  13. Choose the table that you want to query from the Available tables drop-down list.
  14. Choose the fields that you want to import so that they appear under Selected fields.
  15. If you want to import a subset of data, click Use rows, and specify a condition. Click OK in each dialog.
  16. The next time you want to import data from this database, choose File > Query Database (ODBC), choose the file data source that you defined, and then follow steps 12 to 15.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy