[posted and mailed, please reply in news]

Andrew Slentz (ajslentz@yahoo.com) writes:
> Would it be possible to use FIRSTROW=2? I am actually using Bulk Insert
> and it's an available option. I tried it with no luck and got so far as
> to have the first column in the header row in the first column of the
> database but the rest of the data was right.
>
> Also... If I could get something like a "|" or something like that
> placed at the end of the header column would that work as a distinct
> delimeter???

Here are some more suggestions how you could change the file. This was
was your latest posting, that I have seen:

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

This could work:

ID,"NAME",SPEC_ORIGINAL,SPEC,CATEGORY,NetworkCompa redTo
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

This would work, because the header row now has the same delimiters
as the rest of the file. In this case you would use FIRSTROW=2 to
skip the headerline.

Here's another one:

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

Here you would add one more field to the format file, which would be
termimated by ! and where the database column would be 0. In this case,
you should leave FIRSTROW=1. The entire header row, including the newline
would then be the first field of the first record, which you would simply
ignore.

Yet a twist:

NAME,SPEC_ORIGINAL,SPEC,CATEGORY,NetworkComparedTo ,ID
"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp1,1
"Aaron, Arnold H, DO",Family Practice General Practice,FP,PCP,netcomp1,2
"Aaron, Arnold H, DO",General Practice,GP,PCP,netcomp1,3
"Abae, Mick, MD",Reproductive Endocrinology,OBEN,OB,netcomp1,4
"Abanilla, Fernando M, MD",Nephrology,IMNE,SPEC,netcomp1,5
"Abaunza, Ramiro J, MD",Obstetrics/Gynecology,OBGY,OB,netcomp1,6
"Abaunza-Fiallos, Yanina J, MD",Pediatrics,PD,PED,netcomp1,7
"Abbas, Rahat, MD",Internal Medicine,IM,PCP,netcomp1,8

Again, you would add one more column to the format file, this time
terminated by ", and again you should have FIRSTROW=1.

The actual format file for these cases are left as an exercise to the
reader. :-)

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

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