I am attempting to import excel sheets into SSMS and I am having trouble with data types. I was running into problems with columns with both text and numbers in them. I want these columns to import as varchar, but some of the columns were importing as "float" and I was getting an "unknown type conversion" error that would crash the package. Changing the mapping seemed to cause more problems than it solved. The process that I have found that seems to work most of the time is as follows:
- Import a sheet with only headers and sample data (to "seed" the data type) into a new table
- Use Design view to correct data types as necessary
- Import the sheet with all the headers and data
This has worked for 9 tables. However, I am now running into the following problem: I followed steps (1) and (2) above. When executing step (3), I am getting "unknown column type conversion" errors for columns without any data in them. For some reason, the Import tool interprets a couple of empty columns as "DateTime" when the data type of the table column is "float". This is causing the import to fail. I changed the data type in Excel properties to "Number" but that does not seem to make a difference. I have two questions:
- How can I get the data imported with the correct type?
- Why does the Importer get it wrong sometimes? Other columns without data are recognized as "float".