Import data from Excel Wizard automatically detects data types

Question

Hello I'm trying to import data from excel file (xls) to new SQL table so I use Import and Export data 32/bit to achieve that. When I load the excel file it automatically detects data types of columns. e.g. column with phone numbers is as data type to new table float and in excel is as Double(15) when I try to change the float to nvarchar

I get this :

Found 2 unknown column type conversion(s) You have selected to skip 1 potential lost column conversion(s) You have selected to skip 3 safe column conversion(s)

And I'm not allowed to continue with export.

Is there any way to change the data types when trying to import them?

Thank you for your time.

These data are set as text data type in excel Sample data from one of the columns in excel:

5859031783

5851130582

8811014190

This is what I get:

Answer 1

Select the Column in your Excel sheet and change the data type to text

Then go to your sql server open import-export wizard and do all the steps of select source data and bla bla when you get to the point of Mapping Column, it will select Float data type by default, You will have to change it to NVARCHAR(N) in my test I changed it to NVARCHAR(400), it gave me a warning that I might lose some data as I am converting data from 1 datatyep to another.

When you get to the Data Type Mapping page make sure you select Convert checkbox. and stop the process of failure of as appropriate.

Going through all these steps finally got my data in the destination table with the same Warning that I have converted some data n bla bla after all microsoft worries too much :)

Finally Data in Sql-Server Table

╔═══════╦════════════╦═════════╦════════════════╦══════════════╗
║ Name  ║    City    ║ Country ║     Phone      ║ Float_Column ║
╠═══════╬════════════╬═════════╬════════════════╬══════════════╣
║ Shaun ║ London     ║ UK      ║ 04454165161665 ║   5859031783 ║
║ Mark  ║ Newyork    ║ USA     ║ 16846814618165 ║   8811014190 ║
║ Mike  ║ Manchester ║ UK      ║ 04468151651651 ║   5851130582 ║
╚═══════╩════════════╩═════════╩════════════════╩══════════════╝

Answer 2

I have a very strong dislike for the SQL Server Import/Export Wizard.

Not a direct answer, but I find it much less frustrating to go with the Wizard's best guess then fix up the table specification in SQL Server afterwards.