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:
Advertisement
Answer
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 ║ ╚═══════╩════════════╩═════════╩════════════════╩══════════════╝