[posted and mailed, please reply in news]

Andrew Slentz (ajslentz@yahoo.com) writes:
> I have a format file which is working but not correctly. It is, for
> some reason, dropping the first line of the input .csv file. The
> problem is something with the second coulumn of data having quotes in
> it. Any ideas? Below is some info.
>...
> Sample input file:
> ID,NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompare dTo
> 1,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp1
> 2,"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp1
> 3,"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp1
> 4,"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp1
> 5,"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp1
> 6,"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp1
> 7,"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp1
> 8,"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1

Alas, BCP is not able to handle this file, because BCP does not
know what a column header is. It thinks that the entire file is
rows and field in those rows.

I made longer post on BCP last night, and I recommend you to look it
up, because it may give you some understanding on how BCP works. You
will not that the datafile in that post, too, had column header, but
in that case BCP was able to sort out the problem. That was because
the line with the column names did not contain the delimiter for the
first field.

I don't know DTS, but it is possible that DTS could handle this better.
Else you would have some program that stripped that first line. Or
simply added a dummy delimiter to each row but the first, and then
change the format file accordingly.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp