Bulk Insert Not working

0
votes

I have a Bulk Insert query as follows

BULK INSERT tmp_table FROM 'file.jrl' 
WITH ( 
              DATAFILETYPE='widenative' , 
              FIELDTERMINATOR = '~' , 
              MAXERRORS = 0 , 
              ROWS_PER_BATCH = 116064 , 
              ROWTERMINATOR  = '0x0a' , 
              TABLOCK  )

It is giving me following error

Msg 4866, Level 16, State 4, Line 1 The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1 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 1 Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I am using DATAFILETYPE='widenative' , because my data contains some special characters like Ñ,Ã etc For RowTerminator value I have also checked with '\n'

My column separator is ~. Is there anything I have to change? My sample data is as follows

12345 ~asdfdfdfd ~ ~ ~ ~ ~0000000000~ ~0000000000~ ~rrrrñtttttt ~

1
sql-server-2012bulkinsert
The error is rather clear - your table column is shorter than the value found in the file at row 1, column 1, ie 12345 . Where is the table schema? And did you notice the extra space after the digits? - Panagiotis Kanavos
@Panagiotis Kanavos, 95% of the error messages BULK INSERT returns have very little in common with the actual cause. - Alex
@captainsac, have a look at this: social.msdn.microsoft.com/Forums/sqlserver/en-US/…. Also make sure that the number of columns never changes in the file. - Alex
Lacking any other information I'd check the column sizes first. Besides, what may appear to be irrelevant may actually be correct. - Panagiotis Kanavos
@Alex what you posted simply proves that the input file wasn't correctly formatted. BULK INSERT isn't a CSV import tool, it a fast bulk import tool from files that do follow the format. SSIS should be used if any kind of transformation is required - Panagiotis Kanavos

1 Answers

0
votes

Do you work for BioWare?

Anyway, the problem here doesn't come from the row terminator. The error message tells you that the first error occurs at the first column, so there's a problem with your field terminator.

My bet is on the datafiletype. Try 'widechar' instead of 'widenative'. Native field terminator is \t, and using native will make the BULK ignore the FIELDTERMINATOR option.

WKR.