none
Mixed data types in Excel column to OEDB Destination RRS feed

  • Question

  • I am importing the Source: Excel 2007 (xlsx) to Destination:SQL Server DB Table..

    One filed had 739 records in that First 700 had  General (i.e., Numeric ) last 39 had General(Alpha Numeric)

    CT

    -----

    4564

    45645

    4548

    0125

    '''''

    '''' 700 rows

    ADF456

    ADER156

    DER1234

    ''''''

    '''''39 rows

    So I applied

    :: REGEDIT::: 

    HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows ::TypeGuessRows value to zero (0)

    IMEX=1

    Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\destination.xlsx;Extended Properties="Excel 12.0 XML;HDR=YES;IMEX=1";

    But SQL Table Last 39 Records Dumped as NULL  whichever is Alphanumeric.

    Why?

    Dynamically How Can I import without doing Text to column in Excel on that column ?

    Thanks

    Madhu



    Tuesday, May 19, 2015 11:23 AM

Answers

  • Hi Madhu,

    The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. So you can get the first 700 rows with Numeric data type, the last 39 rows wit NULL values.

    To fix this issue, we can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. Because the “IMEX=1” extended property for Excel connection string is used when there are mixed data types in one column.

    Then please also change the value of the TypeGuessRows registry key so that Excel driver scans more than 8 rows (by default) to find the data types. Because the driver by default looks at the first eight rows and from that sampling determines the datatype. So please change the key as below:

    • If the source Excel file is .xls file, the provider used will be Microsoft JET OLE DB 4.0. In this case, we need to modify the following registry key from 8 to 0:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
    • If the source Excel file is .xlsx file, the provider used will be Microsoft ACE OLE DB 12.0. In this case, we need to modify the proper registry key according to the Office Excel version:
      •Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
      •Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
      •Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows


    Reference:
    Excel Source

    Thanks,

    Katherine Xiong


    Katherine Xiong
    TechNet Community Support


    Thursday, May 21, 2015 6:41 AM
    Moderator

All replies

  • What is the datatype of the receiving end? I guess it is numeric and thus the driver NULLifies the alphas.

    It has to be TEXT to import the aphas as is.


    Arthur

    MyBlog


    Twitter

    Tuesday, May 19, 2015 1:32 PM
    Moderator
  • One trick would be set HDR=NO, then delete the header after export.
    Tuesday, May 19, 2015 1:35 PM
  • DataType shown as DT_R8
    Wednesday, May 20, 2015 5:32 AM
  • Hi Madhu,

    The Excel driver reads a certain number of rows (by default, 8 rows) in the specified source to guess at the data type of each column. When a column appears to contain mixed data types, especially numeric data mixed with text data, the driver decides in favor of the majority data type, and returns null values for cells that contain data of the other type. So you can get the first 700 rows with Numeric data type, the last 39 rows wit NULL values.

    To fix this issue, we can modify this behavior of the Excel driver by specifying Import Mode. To specify Import Mode, add IMEX=1 to the value of Extended Properties in the connection string of the Excel connection manager in the Properties window. Because the “IMEX=1” extended property for Excel connection string is used when there are mixed data types in one column.

    Then please also change the value of the TypeGuessRows registry key so that Excel driver scans more than 8 rows (by default) to find the data types. Because the driver by default looks at the first eight rows and from that sampling determines the datatype. So please change the key as below:

    • If the source Excel file is .xls file, the provider used will be Microsoft JET OLE DB 4.0. In this case, we need to modify the following registry key from 8 to 0:
      HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows
    • If the source Excel file is .xlsx file, the provider used will be Microsoft ACE OLE DB 12.0. In this case, we need to modify the proper registry key according to the Office Excel version:
      •Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
      •Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows
      •Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows


    Reference:
    Excel Source

    Thanks,

    Katherine Xiong


    Katherine Xiong
    TechNet Community Support


    Thursday, May 21, 2015 6:41 AM
    Moderator