Excel Import Bug

Question

Excel Import Bug

Yelena Varshal

SSC-Dedicated

Points: 34286
More actions
March 1, 2006 at 2:07 pm

#112025

I should have probably reported this issue under "Data Corruption" forum, but since a lot of administrators use DTS I decided to post it here.
You import NULLs instead of real values for the Number field if you import from Excel spreadsheet into SQL Server table if the field does not contain values in the first 8 records.
To reproduce: try to import any Excel spreadsheet using DTS Import Wizard selecting Excel 97-2000 driver. The field that has occasional numbers in it but first 8 rows don't contain anything for this field will come up as nvarchar if you click on the Transform button in the wizard. Even if you change the destination type to Int, the field will have Int type but NULL data for all rows.
Now replace one of the first 8 empty cells in this field with a number (1 for example) Do everything else the same. The field will be imported correctly with all correct values.
This is probably a bug in the driver because I noticed the same behavior with a third-party import tool that is using Excel driver, not only DTS.
P.S. After posting this I found that it is also "By Design"
http://support.microsoft.com/kb/236605/EN-US/
PRB: DTS Wizard may not detect Excel column type for mixed data in SQL Server

Regards,Yelena Varsha
March 6, 2006 at 8:00 am

This was removed by the editor as SPAM
Modify your Excel connection string to include "Extended Properties='Excel 8.0;IMEX=1'.

This will enable your import to convert all columns to text, and not drop any values during the import.

Mark

Alexey Duvalin SSC Enthusiast Points: 104 More actions · Answer 1

Do you have add ` (tilda) in begin each cell? That mean - convert you digital into char. If you try to import from Excel file with that means, you will see - tilda not importing.

Simple macros in Excel may be set tilda for you.

P.S. Sorry for my bad english.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 2

Alexey,

You English is OK. I say myself that my SQL is better than my English.

No, I did not add anything. There was no macros too. I also received a confirmation from the third-party import tool vendor that they too know about it. This is a "by design" of Excel driver that makes a decision what type the column is based on the first 8 rows.

Thanks so much for your input, I will keep in mind this tilda conversion.

Yelena

Regards,Yelena Varsha

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 3

Yelena;

Sorry I hadn't noticed this post until you had pointed it out in the QOD forum. I thought I would add this as a help for others that may be searching these forums for a solution. As I had posted in QOD for March 15, at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=120&messageid=265749.

You can "correct" this behaviour on Excel imports by:

modifying registry values at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel

Make sure ImportMixedTypes = "Text"
If desired, change TypeGuessRows to a higher number to get a better "sampling" of rows.

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Mark,

The first thing I did when I read your reply in QOD was that I copied it to my notes. Thanks! I have to evaluate that how it will work with the third-party import tool. One would think that the third-party vendor who replied to us that they new about this behavior would do something to the tool to avoid importing NULL data, but even the support vendor who use thir tool did not know about that.

Thanks again, you have a good solution!

Regards,Yelena Varsha