I need to import Excel spreadsheets into SQL Server 2005 every few months, and I always end up banging my head on the same problem. At least I know what to look for now!
Using the Import Data Wizard in Management Studio, is my preferred method of getting data from spreadsheet format into the database, and from there I can massage the data to load it into the necessary tables. The issue is that when you import a column that contains data that could be read as a number or text, for example a classification column containing values like 150, 160, K20, 146, 12G, Excel’s ISAM driver will take a guess as to what type of data will be going into this column, usually based on the first 8 rows. If it determines that it’s text, you’ll get an nvarchar(255), and if it’s a number you’ll get a float. However, if it decides that a nvarchar is needed, then all numbers will be imported as null. Of course, it’s the same if a float is used. If all 8 rows have a value of NULL, then the entire column will always be assumed to be NULL.
There’s a few ways around this. The first is to separate your data so that all your “float” values are in one spreadsheet, and all your “nvarchars” are in another. This isn’t a very attractive idea as it essentially doubles the work.
Another option is to modify the source document to ensure that text data is used for every cell, but just formatting the cells as text isn’t sufficient. You have to reapply the data for each cell individually. Needless to say, I didn’t go down this route, but it’s detailed in a link below.
The solution was to put IMEX=1 into the connection string. This puts the Excel ISAM driver into “Import” mode, and all values come in correctly. The main drawback to this method is that you can’t modify the connection string used in the Management Studio Import Data Wizard, and so you have to save it as an SSIS package and then make the change. “IMEX=1″ comes just after HDR, and in the same quotes: “Excel 8.0;HDR=NO;IMEX=1;”. It’s important to get this right or you won’t be able to connect. Note that even with IMEX=1, you still want to have data that isn’t just numeric in your first 8 rows. More information is available at http://support.microsoft.com/kb/194124/EN-US/.
A good overview on importing data from Excel to SQL Server with a variety of techniques is available at http://support.microsoft.com/kb/321686.
Afternoon, you have no idea how many brownie points your answer to the Excel mixed type has given me with no of my bosses.
Many, Many thanks
THANK YOU!!
I have found that copying your data to a text file (tab delimited in my case) and importing from there will bring in the numbers as well as the character data.
Just be sure to set all the column sizes in preview part (when defining your import file) to be large enough to accommodate the largest field or you get truncation errors.