Importing an Excel table with mixed data type colums to a SQL Table
Posted on 2006-07-12
I have an MS Excel sheet with 1300 odd lines and a couple of dozen of columns that has been in use for years by general users (so a lot and in a mess), and for the time being, needs to continue to be used. I need the data imported across to a SQL table but not all the data is getting imported.
To cut out all the Excel column headings and notes etc in the spread sheet, I have set the print area for the bulk data I need. Most of these columns then become just one data type (Numbers/smallInt or Text/VarChar). But I do have some columns with a mix of the two. As the numbers will not be used in a mathematical sense, I have tried changing the format of these Excel columns into TEXT, then setting the destination SQL Table Column as a VarChar to no avail. The Source column with a majority of Text transfers across, and if you then write a number in the column (although Excel puts a small green error symbol in the top left corner of the cell saying that the number is stored as Text, do you want to convert to a number...) this value also transfers across.
Have a Source column of numbers (formatted to Text) then type in some text, SQL during the import always knows that it is a Double column so ignores the text values, even if the destination table is expecting only VarChar data types.
Please, please, any ideas on how to get around this problem. I have spent so many hours trying to identify what the problem is, and now how to get around it.
Much thanks in advance.