Error converting data types when importing from Excel to SQL Server 2008

Question

Every time that I try to import an Excel file into SQL Server I'm getting a particular error. When I try to edit the mappings the default value for all numerical fields is float. None of the fields in my table have decimals in them and they aren't a money data type. They're only 8 digit numbers. However, since I don't want my primary key stored as a float when it's an int, how can I fix this? It gives me a truncation error of some sort, I'll post a screen cap if needed. Is this a common problem?

It should be noted that I cannot import Excel 2007 files (I think I've found the remedy to this), but even when I try to import .xls files every value that contains numerals is automatically imported as a float and when I try to change it I get an error.

http://imgur.com/4204g

Answer 1

To avoid float type field in a simple way:

1. Open your excel sheet..
2. Insert blank row after header row and type (any text) in all cells.
3. Mouse Right-Click on the head of the columns that cause a float issue and select (Format Cells), then choose the category (Text) and press OK.

And then export the excel sheet to your SQL server.

This simple way worked with me.

Answer 2

Going off of what Derloopkat said, which still can fail on conversion (no offense Derloopkat) because Excel is terrible at this:

1. Paste from excel into Notepad and save as normal (.txt file).
2. From within excel, open said .txt file.
3. Select next as it is obviously tab delimited.
4. Select "none" for text qualifier, then next again.
5. Select the first row, hold shift, select the last row, and select the text radial button. Click Finish

It will open, check it to make sure it's accurate and then save as an excel file.