CODEWITHSUNDEEP
sql-servererror-handlingtype-conversionbulkinsert
LukeT
LukeT

Reputation: 15

SQL Query - Cannot convert varchar to float after bulk insert

I get an error

Error converting data type varchar to float

and I am hoping for some ideas.

I'm doing a bulk insert from a .dat file to a temp table and then wanting to convert the data to a float when adding it to an existing table when I get the error.

CREATE TABLE #Temp_Table ([Op_Base_Qty] VARCHAR(50) NULL)

BULK INSERT #Temp_Table
FROM '\\File_Path.DAT' 
WITH (FIRSTROW = 2, FIELDTERMINATOR = '|', ROWTERMINATOR = '\n')

TRUNCATE TABLE [DBO].[TABLE2]

INSERT INTO [DBO].[TABLE2]([Qty])
    SELECT CAST([Op_Base_Qty] AS Float)
    FROM #Temp_Table

Doing troubleshooting, the values that are throwing an error have a length of 9 characters (i.e. 1,000.000 or 4,324.400). There are no nulls or text in the raw data. Thinking that the length was the problem, I've also tried decimal (18,3) with no success.

How can I convert a varchar with characters like 1,000.000 to a float (or any numeric)?

Thanks for your help

Upvotes: 1

Views: 699

Answers (1)

SqlZim
SqlZim

Reputation: 38043

try removing the commas:

select convert(float,replace([Op_Base_Qty],',',''))

Upvotes: 1

Related Questions