Data Import from MS Excel to SQL Server 2000/2005

Today I was puzzled to see unexpected results when I tried to do a relatively simple task. I had a excel file and I wanted to import the data from this file into a table in SQL Server 2000/2005. The excel file consisted of many columns of various data types a.k.a intermixed data types. When I imported the file into SQL Server and checked the table, I was surprised to see NULL values for some columnswhen the data acutally existed for those columns in the original excel file. After little bit investigation I learned that the data driver during this taks looks only at the first 8 rows of the data for any column to decide its data type. For some of my columns the first 8 rows had NULL values and when I did the import all the column values were resulted in NULLS even though when the original file had some non-NULL values.

Then I made all the columns on my excel sheet as text using format cells option(to eliminate the intermixed data types issue) but that did not work. I still got the NULL values for some columns.

Finally I got it working when I first imported the data into MS Access db and then imported the MS Access table into SQL Server. I also learned that using flat files instead of excel files would also solve the problem.

The seemingly simple task wasn't actually simple ;). Everyday there is something new to learn.