I have an Excel file that I want to import into SQL Server.
The package works fine! Except it nulls out some columns. and since these columns are null, it's picking null for the rows.
interesting, you could change the number of rows that's being used for guessing by the jet engine...
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/54dc94c5-280b-41e5-82fe-26925f9eb95b/
I never had this problem before, so I'm learning now!!
Learned that a TypeGuessRows in registry which is 8 by default, and this is number of rows which Jet engine try to fetch "data type" can be changed up to 16.
At the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel
Another property named IMEX which is for Mixed types in excel. actually when Jet engine mark any column as mixed type ( like columns which character and numeric together ), then IMEX=1 excel will always fetch data as text.
so use IMEX=1 and set TypeGuessRows as number of rows which is appropriate.
Related MS KB:
http://support.microsoft.com/kb/189897
Hope that helps someone as it helped me.
The package works fine! Except it nulls out some columns. and since these columns are null, it's picking null for the rows.
interesting, you could change the number of rows that's being used for guessing by the jet engine...
http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/54dc94c5-280b-41e5-82fe-26925f9eb95b/
I never had this problem before, so I'm learning now!!
Learned that a TypeGuessRows in registry which is 8 by default, and this is number of rows which Jet engine try to fetch "data type" can be changed up to 16.
At the following location.
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\X.X\Engines\Excel
Another property named IMEX which is for Mixed types in excel. actually when Jet engine mark any column as mixed type ( like columns which character and numeric together ), then IMEX=1 excel will always fetch data as text.
so use IMEX=1 and set TypeGuessRows as number of rows which is appropriate.
Related MS KB:
http://support.microsoft.com/kb/189897
Hope that helps someone as it helped me.
No comments:
Post a Comment