Accessing Excel data using the ODBC wrapper

You can access Microsoft Excel workbooks with the ODBC wrapper by using the Excel ODBC driver.

Before you begin

  • The Excel ODBC driver must be on the federated server.
  • The federated server must be able to open and read the worksheets in the Excel workbook to retrieve the data. Therefore, the Excel workbooks must be on the same computer as the federated server or on a accessible mapped network drive.
  • Format the columns according to the type of data the column is expected to have.
  • The data inserted in the columns must comply with the format type that is specified for the column.
  • If the first eight rows of the spreadsheet have no data make sure that they are empty. To ensure that a cell is empty, open the spreadsheet in Microsoft Excel and select Edit > Clear All .
  • Ensure that the data inserted into the columns in the spreadsheet comply with the specified type.

About this task

The Excel application does not need to be installed on the federated server. The Excel ODBC driver is automatically installed with Windows.

With the ODBC wrapper and the Excel ODBC driver, you can access data from any of the worksheets within a workbook. The Excel ODBC driver interprets a workbook as a database and interprets each worksheet within the workbook as a table.

The Excel ODBC driver supports earlier versions of Excel workbooks even if the version of Excel application that produced the workbooks is no longer supported. For example, Microsoft no longer supports worksheets created in Excel Version 4.0, but the driver supports Excel worksheets that were created in that version.

Restrictions

  • The ODBC wrapper cannot access a worksheet when the workbook is already opened by a user or an application in the read/write mode. However, if the ODBC wrapper opens the workbook before a user or an application opens the workbook, the user or application can open the workbook in read-only mode.
  • The Excel ODBC driver expects that the first nonblank row contains the labels for the worksheet columns. You must insert a row of column labels in the worksheet if the worksheet does not have the labels.
  • Because the Excel ODBC driver is only available for Windows operating systems, you can use the ODBC wrapper to access Excel data only on federated servers that run Windows.
  • You can perform insert and update operations on Excel worksheets, but you cannot perform delete operations. The Excel ODBC driver does not support delete operations. To delete data from the worksheet, you must open the worksheet in Excel to make the changes.

Procedure

  1. Ensure that the Excel workbook that you want to access is on the federated server or on an accessible mapped network drive.
  2. If necessary, change the layout of the data in the Excel worksheets to adhere to the Excel ODBC driver requirements. Repeat this step for each worksheet or named range that you want to access.
  3. If necessary, create any named ranges that you want to access.
  4. You can use the ODBC Data Source Administrator to configure the system DSN. The name that was specified when you created the system DSN is assigned as the value for the NODE option in the CREATE SERVER statement.
    If your Excel data source is shared through a Windows network that uses workgroups, you must specify the database name of the system DSN with the following syntax: 
    \\computer_Name\filename_Subdirectory
    where computer_Name is the computer name of the Excel data source and filename_Subdirectory is the sub-directory and file name of the Excel file.
    Example
    If the computer name of the Excel data source is XLSQLS and the network directory to the Excel file is E:\share\test.xls, then you specify the following DSN database name: 
    \\XLSQLS\share\test.xls
    where the root directory of the network directory E: is replaced with \\ and the computer name XLSQLS.
  5. Issue the CREATE WRAPPER statement.
  6. Specify the location of the workbook by registering a server object in the federated database system catalog. For the ODBC wrapper, you need a server object for each DSN. The DSN is associated with the workbook when the Excel ODBC driver is used. The NODE compounds_workbook_dsn is the system DSN that you created. The NODE option is required for the ODBC wrapper to access Excel worksheets.

    To specify the location of the workbook, issue the CREATE SERVER statement and use the DSN as the system DSN for the NODE option.

    For example: 
    CREATE SERVER compounds_workbook WRAPPER odbc 
     OPTIONS (NODE 'compounds_workbook_dsn', PASSWORD 'n')

    Repeat this step for each workbook that you plan to access.

  7. Issue the CREATE NICKNAME statement to create a nickname for the worksheet that you want to access. The syntax is: 
    CREATE NICKNAME nickname FOR server_name.remote_table
  8. If you created a named range to access the data, specify the name of the range as the remote_table portion of the CREATE NICKNAME statement.
    For example, if the name of the range is testing, the CREATE NICKNAME statement is: 
    CREATE NICKNAME compounds_nickname FOR compounds_workbook.testing

    To access the data in the entire worksheet instead of a range, you specify the name of the worksheet followed by the $ symbol.

    For example, if the name of the worksheet is Sheet1, the CREATE NICKNAME statement is: 
    CREATE NICKNAME compounds_nick FOR compounds_workbook.Sheet1$