IMEX=1 revisit and TypeGuessRows setting change to 0 (watch for performance)Posted: February 13, 2009
When we import mixed type data in a column from excel to SQL server, we may have issues with losing some data. We can set IMEX=1 to allow the incoming column allow mixed data types based on the Registry setting ImportMixedTypes to Text. However, there is a default setting for Excel to Guess Data Type of a column, the default value is 8.(The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype.) We can change this value to 0 to force Excel to check all values in the column to choose the data type for the column.
Here is the registry setting we need to check:
Type Regedit from Run… command:
TypeGuessRows: 0 —-Check all column values before choosing the appropriate data type.ImportMixedTypes: Text —-import mixed-type columns as text.
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\Book1.xls;Extended Properties="EXCEL 8.0;IMEX=1;HDR=YES";
SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;IMEX=1;HDR=YES;Database=C:\data\Book1.xls;’, ‘select * from [Sheet1$]’)
We are using IMEX=1, the Import Mode, in the connection string to apply the registry setting.
The possible settings of IMEX are: 0,1,2.
(0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities))