Skip to content
Advertisement

Import data from Excel Wizard automatically detects data types

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:

enter image description here

Advertisement

Answer

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

enter image description here

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.

enter image description here

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

enter image description here

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 🙂

enter image description here

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 ║
╚═══════╩════════════╩═════════╩════════════════╩══════════════╝
User contributions licensed under: CC BY-SA
9 People found this is helpful
Advertisement