Using the Advantage Data Transformer ODBC Scanner to scan Microsoft Office Excel files

Document ID : KB000055318
Last Modified Date : 14/02/2018
Show Technical Document Details

To use a Microsoft Excel spreadsheet as a source for Data Movement programs, the Excel metadata must be scanned into the Advantage Data Transformer Metadata Store. Some preparation of the Excel file is required. To prepare and scan an Excel file, do the following.

  1. Define a Table Name in Microsoft Excel.

    1. Highlight the cells that make up the table; include the column headings and data cells.
    2. On the Excel Menu bar, select Insert
    3. Select Name
    4. Select Define and enter a name for the table structure.
    5. Click Add.
    6. Click OK.

      Column names.

      The Excel column headings will be the metadata column names in Mapper. These headings may not contain any spaces - no embedded, leading, or trailing spaces.

  2. Create an ODBC DataSource.

    Using Microsoft ODBC DataSources Administrator, create a new ODBC System DSN configured with the Microsoft Excel Driver.

  3. Run the ADT ODBC Scanner:

    1. In Mapper, select File > Scanners > ODBC.
    2. Select a metadata store status from the drop-down list.
    3. Expand the Microsoft Excel Driver.
    4. Expand the System DSN you just created.
    5. Expand the <path>\<filename>.
    6. Select the table name you defined in Step 1.
    7. Click OK to run the scanner.

To confirm the metadata, open the Data tab in Mapper. Expand the Status, the ServerType EXCEL, the ServerName Excel, the "database" (a fully qualified file name,) and locate the Table Name. Double-click on the table icon to see the column attributes.