0

I've got the simple table in CSV format:

999,"01/01/2001","01/01/2001","7777777","company","channel","01/01/2001"
990,"01/01/2001","01/01/2001","767676","hhh","tender","01/01/2001"
3838,"01/01/2001","01/01/2001","888","jhkh","jhkjh","01/01/2001"
08987,"01/01/2001","01/01/2001","888888","hkjhjkhv","jhgjh","01/01/2001"
8987,"01/01/2001","01/01/2001","9999","jghg","hjghg","01/01/2001"
jhkjhj,"01/01/2001","01/01/2001","9999","01.01.2001","hjhh","01/01/2001"
090009,"","","77777","","","01/01/2001"
980989,"01/01/2001","01/01/2001","888","","jhkh","01/01/2001"
0000,"01/01/2001","01/01/2001","99999","jhjh","","01/01/2001"
92929,"01/01/2001","01/01/2001","222","","","01/01/2001"

I'm trying to import that data into SQL Server using BULK INSERT (Transact-SQL)

set dateformat DMY;

BULK INSERT Oracleload
FROM '\\Mac\Home\Desktop\Test\T_DOGOVOR.csv'
WITH
    (FIELDTERMINATOR = ',',
     ROWTERMINATOR = '\n',
     KEEPNULLS);

On the output I get the next error:

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

Something wrong with date format maybe. But what script I need to write to fix that error?

Please help.

Thanks in advance.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Oleg Savelyev
  • 275
  • 4
  • 16

3 Answers3

2

BULK INSERT (nor bcp) cannot (properly) handle CSV files, specially if they have (correctly) " quotes. Alternatives are SSIS or PowerShell.

Community
  • 1
  • 1
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
0

I always look at the data in Notepad++ to see if there are some weird characters, or non-printable characters, like a line break or something. For this, it seems like you can open it using Notepad (if you don't have Notepad++) do a find-replace for " to nothing... Save the file, and re-do the Bulk Load.

ASH
  • 20,759
  • 19
  • 87
  • 200
0

This record:

jhkjhj,"01/01/2001","01/01/2001","9999","01.01.2001","hjhh","01/01/2001"

The first column has a numeric type of some kind. You can't put the jhkjhj value into that field.

Additionally, some records have empty values ("") in date fields. These are likely to be to interpreted as empty strings, rather than null dates, and not convert properly.

But the error refers to "row 1, column 2". That's this value:

"01/01/2001"

Again, the import is interpreting this as a string, rather than a date. I suspect it's trying to import the quotes (") instead of just using them as separators.

You might try bulk loading to a special holding table, and then re-importing from there. Alternatively, you can change how data is exported or write a program to pre-clean it — strip the quotes from fields that shouldn't have them, isolate records that have data that won't insert to an exception file and report.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794