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

• Edited by Madhu Sign of Shine Tuesday, May 19, 2015 11:25 AM spell

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