Bulk insert in SQL Server with a regional separator

Question

I want to make bulk insert of data where decimal separator is a comma as in regional settings. The data are the following:

RegionName  Value_1 Value_2 Value_3
Region 1    27,48   66,41   32,82
Region 2    38,93   45,80   61,83
Region 3    38,17   58,02   35,11
Region 4    34,35   16,03   29,01
Region 5    67,94   58,02   17,56

I make the bulk insert using this script:

create table RegVaues (
RegionName varchar(30)
,Value_1 float
,Value_2 float
,Value_3 float
)
go

bulk insert RegVaues
from N'A:\TestValues.txt'
with
(
DATAFILETYPE = 'widechar'
,fieldterminator = '\t'
,rowterminator = '\n'
,firstrow = 2
,keepnulls
)
go

After fulfilling a scrip I receive an error:

sg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 2 (Value_1).

When I try the same with a dot as a separator - all works. I have tried to insert the data with different types (float, decimal, numeric). In my SSMS in Tools->Options->International Settings the Language is set to "Same as in Microsoft Windows". The database collation is Ukrainian_CI_AS. But still the data with a comma separator couldn't be inserted. What I'm doing wrong?

Answer 1

Well the error is self explanatory, values with commas in them are not float values and when you are to insert values like 27,48 , 66,41 into a float column, it tries to convert them values to float and it fails hence the error message.

A simple solution would be to insert the data into a holding/staging table first with column data type character (VarChar) , then replace the commas with a decimal point and then use them values to insert into your final destination table.

Also mind you float is an approximate data type and should only be used for approximate values like (mass of earth and distance between planets etc) for exact values use DECIMAL or NUMERIC data types.