13

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

Marek
  • 3,555
  • 17
  • 74
  • 123
  • 1
    What datatype that column is in your Excel sheet try setting it to `Text` before you export it. – M.Ali Jan 04 '14 at 18:06
  • @M.Ali I tried but still get same issue. – Marek Jan 04 '14 at 18:11
  • can you show some sample data please? – M.Ali Jan 04 '14 at 18:13
  • @M.Ali I updated my question. The issue is that it automatically formats these numbers into float so it gets formatted e.g. as `3.35E+03`. – Marek Jan 04 '14 at 18:21
  • @M.Ali I tried to run it with `float` data types from excel `text` and got same values as in excel. Everything is OK now! Thank you. – Marek Jan 04 '14 at 18:22
  • @M.Ali It stills formats some values that are greater then Double (15) as e.g. `505224108` as `5.05224e+008` Don't you know why? This value `707575` after import is the same. – Marek Jan 04 '14 at 18:34
  • When you go 1 step back from this slide shown in your question and to go `Edit Mappings` and see what data length you have selected if its set to MAX then change it to 4000 and then try again – M.Ali Jan 04 '14 at 19:08
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44512/discussion-between-marek-and-m-ali) – Marek Jan 04 '14 at 19:14
  • This is the solution that worked for me: http://stackoverflow.com/a/20801337/2808230 – IceWarrior353 Feb 03 '17 at 22:27

2 Answers2

28

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 ║
╚═══════╩════════════╩═════════╩════════════════╩══════════════╝
M.Ali
  • 67,945
  • 13
  • 101
  • 127
1

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.

Graham Monkman
  • 404
  • 6
  • 9