October 11, 2016 at 11:22 pm
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.
October 12, 2016 at 3:28 am
Try exporting from Excel to CSV and importing from the CSV.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 3:59 am
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
October 12, 2016 at 4:44 am
After saving the CSV, open it using Notepad or some other text editor. Do not use Excel to open CSV files.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 12, 2016 at 3:06 pm
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)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply