Link to home
Start Free TrialLog in
Avatar of pikarde822
pikarde822

asked on

Bulk Insert fails. Skip first (invalid) line not working

Hi,

I have a csv file that I'm importing.
It has roughly 100,000 rows and 4 fields per row. Comma separated. CrLf as line separator.
The very first line contains just 1 value (Customer Name)
Example:
"Customerx"
"07/22/2008","0001","Product1","99"
"07/22/2008","0003","Product3","99"
"07/22/2008","0024","Product24","99"
...
it's not important what the numbers mean.
I ran bulk insert with FIRSTROW=2
However the import fails. I noticed if it doesnt have a delimiter in it, it doesnt work even thou it is supposed to be skipped.
How can I make it work without having to rewrite the csv file as this is supposed to run in an automated environment?

Thanks
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

try to delete 1st row "Customerx"
and run- > did you get error?

try FIRSTROW=3  4  5.. (to see if there are not additional top rows)
Avatar of pikarde822
pikarde822

ASKER

Deleting the first row would require to have a programmer code it accordingly or manual interaction. We have only one customer who sends csv files like that. It would be unreasonable for us to pay premium price for the programm to set something in place for just this one customer.
No difference with other FirstRow parameters, we tried that already.
There is only 1 header row.
Have you tried importing with a format file? The format file can be used to determine how the fields layout is. I assume that SQLServer does not automatically find the field layout because it assumed the field layout being correct from the first row onwards.

instead of bcp/bulk insert, try creating a dts package.
can you please post your Bulk Insert code and error that you got?
I just tried from GUI - no problems
ASKER CERTIFIED SOLUTION
Avatar of abel
abel
Flag of Netherlands image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
problem that /n is not row terminator for the  line with "Customerx" :
 
however: this code that skip 2real line is working:
 

--truncate table test
CREATE TABLE [test] (
[Col001] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col002] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col003] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Col004] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


BULK INSERT test FROM 'C:\test.csv'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
select * from dbo.test
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Sorry everybody. I found the bug, It was in the pre-processing of the csv file. based on the amount of entries per line a temp table was created dynamically. Since the first row only had 1 entry, the temptable had only one column and the bulk insert failed due to insufficient columns available.
So, yes the firstrow DOES work as expected. No need for a format file at this time.
Thanks everybody for the time and assistance spent.
Using the format file might help, but often with a short row to begin with (or end with),  it can cause problems...

What verson of SQL Server are you using ? There are a couple of options...

Is it possible to post a sample of your import data ? changing things of course... CSV is open to abuse, and using a format file is not overly forgiving. How big do you expect the CSV to be ?

Can certainly help you with this... Just need a closer example to create a format file for you and need the version of SQL (2005 and 2008 do have additional more tolerant methods).

> Sorry everybody. I found the bug,

no problem, you could also have refunded your points. However (see guidelines) this should be not a reason to use B/C grades on answers. You should do that when you are dissatisfied after first asking for clarification ;)

> Thanks everybody for the time and assistance spent.

you're welcome.
Oh ok. I'll remember next time.
i did a B because nobody asked to see the bulk insert statement ;-) and then the temp table format.....
I didnt want yall do all the research and work for nothing.
Ok. Thanks for the points, it was a nice exercise regardless  ;)