Error in converting Float into Decimal

Question

Error in converting Float into Decimal

rossetto_ax

Say Hey Kid

Points: 666
More actions
November 6, 2007 at 6:17 am

#180114

Hi,
I have a problem in converting a value from Float to decimal. Could everybody help me???:D
I take some values from an Excel file and I want to import into a Temp table:
CREATE TABLE #D_Tmp(
[DecVal] [decimal](3, 2) NULL
)
Set @sql = N'INSERT INTO #D_Tmp SELECT convert(decimal(3,2),cast([F1] as float)) as DecVal FROM OPENROWSET(''Microsoft.Jet.OleDB.4.0'', ''EXCEL 8.0; Database=' + @Path + ''',''SELECT * FROM ['+ @FolderName + '$B7:O60000]'')'
Exec(@sql)
SELECT * FROM #D_Tmp
The file excel is like this.
VALUE
--------------------
100
100,00000000001
100,00000000001
100
100
100,00000000001
Note that has comma as decimal separator
- I've tried to convert the VALUE column in FLOAT and then to CAST as decimal(3,2). Nothing. :crying:
- I've tried to Round the VALUE... Nothing. :crying:
Thanks
Alessandro

Viewing 7 posts - 1 through 6 (of 6 total)

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

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

What is cell format for the Value field in Excel. I believe ',' may be your delimiter for 100. You may want use custom formatting in excel before trying to import the data to SQL Server. Else you may want to import data into a string field and use replace to replace the ',' with '.' and modify/update the datatype of your choice.

Prasad Bhogadi
www.inforaise.com

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 2

Thanks..

now I'm going to try...

Unfortunately I can't modify the Excel, I must only import...

Do you think that the problem is the comma or point as decimal?

Have you an example?

Thank a lot.

ALessandro

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 3

Maybe I'm missing something but it sounds like you are trying to import a value of 100,00000000001 into a field with the format 9.99?

Shouldn't you be using decimal (14,11) to hold that much data?

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 4

Yes, this is right but I couldn't modify the table... now I try to round beforre inserting

Kenneth.Fisher SSC-Insane Points: 20396 More actions · Answer 5

Your first problem is that Decimal(3,2) will hold at most 9.99. Based on your data you will never be able to store it in that field.

If on the other hand your field were Decimal(5,2) which will hold 999.99 then you could try the following:

Set @sql = N'INSERT INTO #D_Tmp SELECT cast(REPLACE([F1],',','.') as Decimal(5,2)) as DecVal FROM OPENROWSET(''Microsoft.Jet.OleDB.4.0'', ''EXCEL 8.0; Database=' + @Path + ''',''SELECT * FROM ['+ @FolderName + '$B7:O60000]'')'

That will replace your , with a . so your values will be 100.0000001 which converts to a decimal (5,2) just fine.

Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

rossetto_ax Say Hey Kid Points: 666 More actions · Answer 6

WOW IT WORKS !!! :Wow:

Thanks !!

the problem was actually the decimal places !! With Decimal(5,2) work fine.

Thanks a lot for your patience. 😉

Alessandro