September 8, 2008 at 1:26 pm
I am trying to load an excel file into a SQL Server 2005 database. The file contains 23 columns, all of which import perfectly but one, the Phone column.
There are only 3 fields that contain data in the Phone column but they always import into the database as NULL values. I have a Data Conversion set up for all the fields, which convert to String. This is the only column giving me issues and I can't figure out why.
The excel file itself only contains 18 rows.
Has anyone else had this issue?
September 8, 2008 at 2:23 pm
I seemed to have fixed the problem with the import. Apparently I had to add IMEX=1 to the ConnectionString to avoid this problem in the future due to the way Excel datatypes read into SQL Server.
Maybe this will help someone else that may run into this problem in the future.
September 8, 2008 at 2:42 pm
For a complete explanation see:
Microsoft's recommended method to get around the problem:
http://support.microsoft.com/kb/194124/EN-US/
You can add the option IMEX=1; to the Excel connect string in the OpenDatabase method. For example: Set Db = OpenDatabase("C:\Temp\Book1.xls", _
False, True, "Excel 8.0; HDR=NO; IMEX=1;")
as you have done.
And this is what IMEX=1 does behind the scenes:
NOTE: Setting IMEX=1 tells the driver to use Import mode. In this state, the registry setting ImportMixedTypes=Text will be noticed. This forces mixed data to be converted to text. For this to work reliably, you may also have to modify the registry setting, TypeGuessRows=8. The ISAM driver by default looks at the first eight rows and from that sampling determines the datatype. If this eight row sampling is all numeric, then setting IMEX=1 will not convert the default datatype to Text; it will remain numeric.
You must be careful that IMEX=1 not be used indiscriminately. This is IMPORT mode, so the results may be unpredictable if you try to do appends or updates of data in this mode.
The possible settings of IMEX are:
0 is Export mode
1 is Import mode
2 is Linked mode (full update capabilities)
The registry key where the settings described above are located is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Excel
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply