Fun with BULK INSERT SQL SERVER - type mismatch or invalid character for the specified code page

Question

I am inserting data that looks like this:

AA00000111 PSNH-OT J, SMITH 03/01/2011 10/11/1957 42 Male Hartford NH Lorazepam Benzodiazepines C AA00000151 PSNH-OT BEN, HARRY 03/06/2011 07/18/1969 42 Male Hartford NH Fentanyl Synthetic Opioids C AA00000151 PSNH-OT URA, HARRISON 03/06/2011 07/18/1969 29 Male Hartford NH Norfentanyl Synthetic Opioids C AA00000181 PSNH-OT WAYNE, GRIFFON 03/06/2011 09/01/1982 75 Female Hartford NH cTHC (Marijuana metabolite) Illicits C 

with this bulk insert:

 use RadarDataMining go BULK INSERT tblRadarsAC FROM 'C:\PerfLogs\radars.txt' WITH ( FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n' ) 

and I get the following errors:

 Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (date_tested). Msg 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 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 4 (date_tested). Msg 4864, Level 16, State 1, Line 2 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 4 (date_tested). Msg 4865, Level 16, State 1, Line 2 Cannot bulk load because the maximum number of errors (10) was exceeded. Msg 7399, Level 16, State 1, Line 2 The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error. Msg 7330, Level 16, State 2, Line 2 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)". 

fourth type of date field

for some reason he does not like my date field. I do not understand why. what am I doing wrong?

Answer 1

Create a staging table that displays the date field as varchar, not the date, and see if you can insert a volume into it. Perhaps you have some kind of non-printable character in the field and, therefore, this is not a date for volume insertion.

Once this is a table. make a choice like this

Select 'XX' + field 4 + 'XX'

If you see a space representing Xs and a date, you have a non-printable character. If you want to know what this character is, you can use the ASCII () function.