Bulk Insert: One Row not importing in...

Ok so I got this working:

BULK INSERT

dbo.tbCheckPointTest

FROM 'c:\inetpub\wwwroot\upload\maxie_task_aging.csv'

WITH

(DATAFILETYPE = 'char', FIELDTERMINATOR = ',',

FIRSTROW = 2, ROWS_PER_BATCH = 6, ROWTERMINATOR = '\n')

My data file looks like this:

REPORT_EXECUTED_ON:20070403_170001 Research 306 263 2470 State 1031 7 0 Program 13 1 0 Program Tech 85 69 139

Total of 6 rows, so I indicate to start on Row 2, yet only the last three lines are getting into my table.

I have changed and manipulated the query varies counts, but still just the last three lines get imported.

Thoughts?

Thanks!

Hi,

From the looks of your data file, it is difficult to tell how many blank lines are associated with your data file.

It appears that "Research" may begin on line 4, in which case you would have only three rows of data: "Research", "State", and "Program".

I am confused by the fact that you get the last three rows: "State", "Program", and "Program Tech".

Is it possible that some rows are terminated by "0A0D" and others only by "0A" or "0D", and "\n" is being interpreted as strictly "0A0D" (hexadecimal)?

I also am confused by your use of "FIELDTERMINATOR = ',' ", since I don't see any commas in the data you have displayed.

Dan

|||

Sorry. I had the .csv open with excel when I copied and pasted it:

REPORT_EXECUTED_ON:20070404_110001

Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

I am going to see if anything you suggested will help.

|||

The problem seems to be that the BULK INSERT function seems to expect all the rows in the file to have the correct number of fields for the target table (even those being skipped). So it is ignoring the newlines until it has found enough commas (and then counts any other commas until it finds the row terminator as part of that field).

Try loading the file:

REPORT_EXECUTED_ON:20070404_110001,,,
,,,
Research Specialist - Research , 340, 91, 2635
State Worker (Interim) , 918, 3, 0
Program Specialist - Quality Assurance Specialist , 16, 1, 0
Program Technician - Data Entry Hold , 82, 28, 160

Though you will need a FIRSTROW = 3

If you put the last field into a varchar rather than an int then if you add commas to the end (e.g. 2635,,,,) then you will find that these commas end up in that field (if not then it will complain about unexpected characters when it tries to convert it to an int or whatever).

So any rows to be skipped should have the correct number of field separators in them (3) or they will not be included in the skip count.

|||

Thank you, that solved the issue. Now I have to get w/the programmer who delivers the file to me.

|||

Nice catch, Dhericean!

Dan