Character data from a Microsoft Excel "Text" column is loaded into ADT as NULL if other rows in that column contain purely numeric characters.

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

Description:

The Microsoft Excel column is formatted as "Text". Some of the data in that column is all numeric and some has a mixture of numbers and letters or characters like hyphen. When ADT scans the spreadsheet, it interprets the data type as NUMBER. Even if the metadata is edited so that the data type is VARCHAR, the data is moved as if it were numeric so that the all-numeric data has ".0" appended; and the data containing alpha characters is not moved and the target receives NULL.

Solution:

As a spreadsheet, Excel's bias is for columns to be numeric, even when the Format property is set to Text. That will left-justify the data, but the column's data type is still open to interpretation. ADT's ODBC Scanner uses information provided by the ODBC DataSource to determine the data type. The ODBC DSN has a "Number of rows to scan" parameter that defaults to 8 and can be set up to 16. If most of the data for the first 8 to 16 rows is all numbers, then ODBC will consider any alpha data to be an error and will tell the ODBC Scanner that this is a numeric data type. For your column to be truly a character data type, do the following in Excel.

  • Highlight a column. (Excel requires each such column to be modified individually.)

  • From the Excel Menu Bar, select "Data".

    • Select "Text to Columns".
      Click Next".

    • If "Fixed-Length" is selected, change it to "Delimited".
      Click Next.

    • Uncheck "Tab".
      In the "Text qualifier:" box, select "{none}" from the drop-down list.
      Click Next.

    • In the "Column data format" section, select "Text".
      Click Finish.

Now ADT's ODBC Scanner will mark this column as VARCHAR, and ADT scripts will move the data as string values.

Alternative Solutions:

Some other ways to deal with this are not preferred, but they are workable.

In Excel, use SAVE AS to save the sheet as a CSV. Then configure the ODBC DSN with the Microsoft Text driver and use the DEFINE FORMAT panel in ODBC configuration to modify the default data types and widths. The ADT ODBC Scanner will then use your definitions to store the metadata. Note that when you do this, the file's name will be the "table" name in ADT since there is no longer a Defined Name or "sheet," so be sure it doesn't have any hyphens or blanks in the name. For example, a file named my Numbered-Accounts.xls should be saved like my_Numbered_Accounts.csv. This method will not be appropriate if more than one "table" exists in the same Excel sheet.

or

In the original Excel (.xls) file, duplicate the first 8 to 16 dummy rows at the top of the spreadsheet, just beneath the header row. Insert data of the type desired, such as all-alpha, in the Text columns of those rows. Run the ADT ODBC Scanner. Then remove the dummy rows from the Excel sheet.