"Andrew Slentz" <ajslentz@yahoo.com> wrote in message
news:40cfb33e$0$25524$c397aba@news.newsgroups.ws.. .
> 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.
>
> Format file (I use firstrow=2 in Bulk Import command:
> 8.0
> 6
> 1 SQLCHAR 0 3000 ",\"" 1 Provider_Raw_ID Latin1_General_CI_AS
> 2 SQLCHAR 0 3000 "\"," 0 none_name Latin1_General_CI_AS
> 3 SQLCHAR 0 3000 "," 0 none_Spec_orig Latin1_General_CI_AS
> 4 SQLCHAR 0 3000 "," 3 SpecialtyCode Latin1_General_CI_AS
> 5 SQLCHAR 0 3000 "," 2 Category Latin1_General_CI_AS
> 6 SQLCHAR 0 3000 "\r\n" 4 NetworkComparedTo Latin1_General_CI_AS
>
> 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
>
> Thanks a lot!!!
>
> Andrew
>
>
>
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

As a previous poster noted, Erland recently posted some useful comments on
format files which might be helpful. You might also want to consider DTS,
which is usually easier to set up than a format file (at least that's my
opinion), and is reasonably 'intelligent' about delimiters and file formats.
It also has facilities for transforming data as its loaded, if you need to
handle issues such as different date formats, for example.

But DTS is a much more complex tool, and dynamically setting source file
names and destination database names is definitely awkward unless you have
some basic VBScript/COM knowledge. If you don't do much data importing, then
it might be overkill, but if you're comfortable with that sort of
programming, it could be another option to consider.

Simon