Issue:
Excel columns having mixed data types are
loaded with NULL instead of actual value.
Replicating
the problem
1) Created excel with two rows.
Slno and Datatypevalue
2) Inserted 10 rows. slno
as serial numbers from 1 to 10 and Datatypevalue as first 7 rows are serial numbers
and 8 to 10 as text value of numbers.
|
Slno
|
DataTypeValue
|
|
1
|
1
|
|
2
|
2
|
|
3
|
3
|
|
4
|
4
|
|
5
|
5
|
|
6
|
6
|
|
7
|
7
|
|
8
|
Eight
|
|
9
|
Nine
|
|
10
|
ten
|
3) Add a DFT in SSIS. Pull Excel
source connection task and ole db destination task in data flow.
4) Setup the source and destination
connections.
5) When excel data is previewed value
in 7 rows and next three rows as NULL.
Cause
and Solution
Cause:
This is because excel guesses data type by based on TypeGuessrows mentioned in registry settings for excel in the
server. BY Default 8 rows as mentioned in registry for excel connection.
Solution 1: Works most of the times.
This
solution involves changing the connection string of excel and registry settings
in server where excel is installed.
1) Set ImportMixedTyes value in registry is “Text”
2)
Update TypeGuessrows to 0 in
registry. Value 0 means excel considers all the rows to guess the data type of
the column.
3) Update IMEX
settings in Excel connection string to 1. IMEX = 0 and IMEX = 2 result in ImportMixedTyes being ignored. (Extended
Properties="EXCEL 12.0 XML;HDR=YES;IMEX=1";)
Solution
2: Works at all times.
As generally thought TypeGuessrows does not always scan all
rows in excel but instead it scans 16284 rows, this means if any column contains
first 16384 rows as numerical and next rows as text value then it will also be
loaded as NULL.
To resolve
this we need to use a tactical solution by loading the data along with column
name. This will ensure column evaluation to text and all the data is retrieved
as source value. Later this row data can be deleted by using conditional split in
SSIS or delete from table option. Additional to column change connection string
in excel needs to change to expect mixed data type data in the columns. Append
IMEX=1 in extended properties of connection string.
Also note
this solution does not require change in the registry settings.
Note:
1) Registry settings for excel can
be found in below path
Path:
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access
Connectivity Engine\Engines\Excel\
2) Care should be taken during
browse for registry settings. Registry settings can be viewed with the help of
information available in excel connection string details. Check OLE DB provider
version, in this case it is 12.0.
Connection
String: Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=O:\Users\Vinay Kumar\ETL Projects\Excel Data Type
issue\Excel_DataType_Issue\Test.xlsx;Extended Properties="EXCEL 12.0
XML;HDR=YES;IMEX=1";
3) By default IMEX=1 won’t be
available in connection string. This is updated to resolve the mixed data type
issue. 4) JET
OLE DB provider – used to read Excel 2003 or older – or with the ACE OLE DB
provider, which is used to read Excel 2007 or newer.
Reference
Link:
