Mixed data types in Excel column to OLEDB Destination

Mixed data types in Excel column to OLEDB Destination

Below is a copy of this topic from Microsoft MSDN Forum:

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:25 AM


Answer

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

Katherine Xiong
TechNet Community Support
Thursday, May 21, 2015 6:41 AM


Please find the original discussion in below link:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ce095b10-84a4-4ae3-8944-70a2b53daa44/mixed-data-types-in-excel-column-to-oedb-destination?forum=sqlintegrationservices