IMEX=1 revisit and TypeGuessRows setting change to 0 (watch for performance)


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:

Under entries

Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/TypeGuessRows
Hkey_Local_Machine/Software/Microsoft/Jet/4.0/Engines/Excel/ImportMixedTypes

Change:
TypeGuessRows: 0    —-Check all column values before choosing the appropriate data type.ImportMixedTypes: Text         —-import mixed-type columns as text.

Connection string:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\data\Book1.xls;Extended Properties="EXCEL 8.0;IMEX=1;HDR=YES";

–Or:

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.

PS:

The possible settings of IMEX are: 0,1,2.

(0 is Export mode 1 is Import mode 2 is Linked mode (full update capabilities))

 

 

http://support.microsoft.com/kb/194124

http://support.microsoft.com/kb/189897

Advertisements

11 Comments on “IMEX=1 revisit and TypeGuessRows setting change to 0 (watch for performance)”

  1. Tas says:

    I just want to say thanks for this post. I had a major headache with IMEX where I was led to believe it didn\’t work. Finding out that it only checked the first 8 rows by default was a major relief. This is the only post on the net I could find which actually describes this problem. Thank you!

  2. Ankur says:

    This solution realty works for my problem . Awesome .

  3. Issac says:

    It is really helpful for me to sort out the issue. At first, i tried it out only in the connection string. But, then this blog helped me a lot to change the registry settings.

  4. A.ppreciated says:

    Very well explained and spot on for me

  5. Mansour says:

    it deosnt work with me please help

  6. Elena says:

    I’ve tried TypeGuessRows=0 and it seems to works only with correct type and not field size. I’ve problem with the size, the first several records have short length in the xslx file and later on length became longer and I’m getting error when try openrowset statement that data is trancated.

  7. This is really helpful. Has fixed a major headache. Thank you very much.

  8. kittu says:

    Thanks for the post. Helpful information. I have the same issue but when I tried to put IMEX = 1 I am getting error says ‘Couldn’t find Installable ISAM’ and I tried the other option by setting 0 for TypeGuessRows but no use, getting error ‘Cannot edit TypeGuessRows: error writing the value’s new contents’. Plz help me.

  9. akt says:

    Superb
    Works For Me also…
    I have tried lot for solving issue, but some how it will create problem,
    Thanks for a blog and help….

  10. akt says:

    Thanks a lot….Really proper solution to excel import problem.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s