BULK Insert not accepting decimal

Question

I am using bulk insert to be able to import multiple CSV files from a folder into my table.

the sql im using is.

set @sql = 'BULK INSERT RawActivity FROM ''' + @path + @filename + ''' '
           + '     WITH ( 
                   FIELDTERMINATOR = '','', 
                   FORMATFILE = ''C:\\Users\\pllaguno\\Desktop\\Febrero\\format.fmt'',
                   ROWTERMINATOR = ''\n'', 
                   FIRSTROW = 2 
                ) '

my CSV file is as following, i will include some lines where sql is sending me errors and where not(line number first).

2 Unknown Driver,2/1/2019,4651,Seat Belt Alert Start,12:00:06 AM,46,km/h,0.4,km,,,,,"3 Sur 256, Sevilla, 15840 Ciudad de México, CDMX, Mexico",19.40908,-99.12303
146 Unknown Driver,2/1/2019,9804,Seat Belt Alert End,12:12:36 AM,,-,,-,,,,,"[Cedis Saltillo], Perif. Luis Echeverría 3951, Centenario, 25050 Saltillo, Coah., Mexico",25.40517,-100.98308
152 Unknown Driver,2/1/2019,10319,Seat Belt Alert End,12:12:58 AM,,-,,-,,,,,"[Cedis Monterrey Centro], Calle Pedro María Anaya 1210, Industrial, 64440 Monterrey, N.L., Mexico",25.69608,-100.32383
153 Unknown Driver,2/1/2019,13042,Seat Belt Alert End,12:12:59 AM,,-,25.0,km,,,,,"Av. Eugenio Garza Sada Sur SN-S""OXXOEXPRES"", Roma, 64700 Monterrey, N.L., Mexico",25.65527,-100.29421

the error given is (EDIT: the line should be 153, the problem is some lines have more quotes inside)

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 152, column 15 (Latitude).

and my .fmt file is like this:

11.0
16
1   SQLCHAR   0  8000   ","      1     Driver         SQL_Latin1_General_CP1_CI_AS
2   SQLCHAR   0  40   ","      2     ActivityDate         SQL_Latin1_General_CP1_CI_AS
3   SQLCHAR   0  8000   ","      3     Vehicle         SQL_Latin1_General_CP1_CI_AS
4   SQLCHAR   0  40   ","      4     Event         SQL_Latin1_General_CP1_CI_AS
5   SQLCHAR   0  8000   ","      5     EventTime         SQL_Latin1_General_CP1_CI_AS
6   SQLCHAR   0  40   ","      6     Speed         SQL_Latin1_General_CP1_CI_AS
7   SQLCHAR   0  8000   ","      7     SpeedUnit         SQL_Latin1_General_CP1_CI_AS
8   SQLCHAR   0  40   ","      8     TripDistance         SQL_Latin1_General_CP1_CI_AS
9   SQLCHAR   0  8000   ","      9     TripUnit         SQL_Latin1_General_CP1_CI_AS
10   SQLCHAR   0  40   ","      10     OnSiteTime         SQL_Latin1_General_CP1_CI_AS
11   SQLCHAR   0  8000   ","      11     HDOP         SQL_Latin1_General_CP1_CI_AS
12   SQLCHAR   0  40   ","      12     NumSatellites         SQL_Latin1_General_CP1_CI_AS
13   SQLCHAR   0  8000   ",\""      13     Channel         SQL_Latin1_General_CP1_CI_AS
14   SQLCHAR   0  40   "\","     14     Location         SQL_Latin1_General_CP1_CI_AS
15   SQLCHAR   0  8000   ","      15     Lattitude         SQL_Latin1_General_CP1_CI_AS
16   SQLCHAR   0  40   "\r\n"      16     Longitude         SQL_Latin1_General_CP1_CI_AS

The column for Latitude is decimal(9,6)

I would check the file for lines before and after 152, you are saying in your code data starts at a different line, so in your file the line it imports may start at a different number/row then you are looking at. Also try opening the file in Excel and look at all the data for that row that is giving you issues. — Brad, Mar 13 '19 at 17:50
@Brad that is correct, just realized that, let me update with the line that has the problem, it appears that it has more double quotes inside it. — pato.llaguno, Mar 13 '19 at 17:51
@Brad do you have any idea into how to escape those instances where some have quotes inside? — pato.llaguno, Mar 13 '19 at 17:53
yea the extra double quotes would cause the rows to move over on the insert. — Brad, Mar 13 '19 at 17:53
For quotes on the inside, no I do not, I had that issue and couldn't find a solution so I had to write a c# app to fix some files that we had that issue with prior to importing them. I could do it with an app but no way to find settings to do it in an import, especially bulk import. The best solution is to fix it at the source and remove the extra quotes on file export. Or use a different character for text qualifier. — Brad, Mar 13 '19 at 17:55
@Brad i think there must be a way since opening it in Excel does get the columns correctly — pato.llaguno, Mar 13 '19 at 17:56
You do have 2 sets of quotes in your file for each one (already escaping them) so it may work for you since you have that. My issue was just a single double quote in the middle of my text data. I spent quite a while searching for my issue but could not find a solution for mine and dont remember one similar to yours. I am sorry I do not have a solution but since you do have 2 double quotes you may be able to find solution. — Brad, Mar 13 '19 at 18:01
I found one link that may help: https://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server In one spot it says set TextQualifierFlag = "", I dont see that in your code, maybe try that. — Brad, Mar 13 '19 at 18:08
@Brad from what i understand since im using the formatfile i don't need that, didn't help :/ — pato.llaguno, Mar 13 '19 at 18:13

score -1 · Answer 1 · answered Mar 13 '19 at 18:03

I can see two differences from the documented format.

1) The host file data length in bytes can be 0, since you are dealing with a delimited file.

2) Non-text columns do not need to have a collation associated with them. So set the Lattitude and Longitude Collations to "".

Finally, is "Lattitude" a typo?

Here is the MS doc page for the fmt file format. https://learn.microsoft.com/en-us/sql/relational-databases/import-export/non-xml-format-files-sql-server?view=sql-server-2017

BULK Insert not accepting decimal

1 Answers1