Error converting data type nvarchar to float

Question

Error converting data type nvarchar to float

yuvipoy

SSChampion

Points: 10469
More actions
October 11, 2016 at 11:22 pm

#313792

Hi All,
This is general message i am getting across all not in 2012 alone, since i am using 2012 i opened this here.
I am getting the following error when try to import data from excel.
Error converting data type nvarchar to float
I am having 10 columns in a table out of which 2 are float and rest are nvarchar.
Getting the value from excel i need to import to my table.
When i used Import/Export wizard to import the data to table where the input is in excel format.
Since it is one time i don't want to go for SSIS.
I also tried creating the table as Sheet1$ and tried to insert into mytable, but no luck as Sheet1$ created all the values as nvarchar(255).
Insert into mytable
Select * from [Sheet1$]
Error: Error converting data type nvarchar to float
Tried changing my float value in the excel sheet to number(10,5), but while creating the table(sheet1$) it creates as nvarchar only.
Tried changing my float value in the excel sheet to number(10,5), imported directly but the wizard fails
How to resolve this type of issue.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246964 More actions · Answer 1

Try exporting from Excel to CSV and importing from the CSV.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yuvipoy SSChampion Points: 10469 More actions · Answer 2

When saved the excel file as csv having some challenges like the numbers are in exponential when saved as csv.

So when saved as csv and opened my csv file found my numbers are in exponential form and in my columns one column has commas(,) in it

Phil Parkin SSC Guru Points: 246964 More actions · Answer 3

After saving the CSV, open it using Notepad or some other text editor. Do not use Excel to open CSV files.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

whenriksen SSCarpal Tunnel Points: 4752 More actions · Answer 4

To check which records will not convert to float:

1. Import the spreadsheet with all columns as nvarchar(255).

2. Add a new float column to the table after populated

3. Update float column = Try_Cast(nvarchar column as float);

4. Select from table where float column is null, review the nvarchar column to see what values Excel imported. I bet it is a bunch of empty cells.

Good luck

Wes
(A solid design is always preferable to a creative workaround)