June 28, 2010 at 1:26 am
Last 1 week im facing issue in importing excel to database.
Some recorsd shows as NULL in database.
SO i just start from root and check excel privew in edit,
but there itself some records shows as NULL.
Please help to resolve that...........
June 28, 2010 at 4:39 am
This is a very, very, very common problem with SSIS and Excel. It shows up in hundreds of blog and forum posts.
Have you tried Google?
The issue is that Excel guesses the datatype based on a fixed number of rows of that column. If it sees x times a number, the column will get a numeric datatype, thus rendering NULL for your string values later on.
How to fix this? Set IMEX=1 in your connection string. This will tell the driver that if there are intermixed datatypes, to convert everything to text. Furthermore, you have to set a registry value to 0, telling the driver to scan the whole column, and not just the first x rows.
Reading material:
http://support.microsoft.com/kb/189897 (registry key)
http://swap.wordpress.com/2007/09/18/solution-for-excel-data-uploading-problem-using-ssis/ (IMEX=1).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply