Format File Problem - sorry did the first post wrong

I have generated a format file with bcp (SQL Server 2000) which generates a
file which would work if there was a column to column relationship between
the csv file and the table. My csv, however is in a different order.
Basically the table has an identity field but the csv file doesn't. They are
pretty similar other than that. I need the identity column in the table to
be ignored but datafield 1 to map to column 2 and so. All's I get is an
invalid column number error. Can anyone point me in the right direction?
Here is the format file, which is failing. There are also some columns at
the end which have been missed off as there are no corresponding datafields
for them.
Here is mine
8.0
13
1 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 3
Forename Latin1_General_CI_AS
3 SQLCHAR 0 12 "," 4 Age
""
5 SQLCHAR 0 12 "," 6
InscriptionID ""
6 SQLCHAR 0 12 "," 7
KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "," 9
ReligionID ""
8 SQLCHAR 0 12 "," 10
CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear
""
11 SQLCHAR 0 12 "," 12
DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay
""
13 SQLCHAR 0 3 "," 14 IsMonth
""
14 SQLCHAR 0 3 "/r/n" 15
IsYear ""
Here is the original
8.0
20
1 SQLCHAR 0 12 "\t" 1 NameID
""
2 SQLCHAR 0 510 "\t" 2
Surname Latin1_General_CI_AS
3 SQLCHAR 0 510 "\t" 3
Forename Latin1_General_CI_AS
4 SQLCHAR 0 12 "\t" 4 Age
""
5 SQLCHAR 0 12 "\t" 5 DOD
""
6 SQLCHAR 0 12 "\t" 6
InscriptionID ""
7 SQLCHAR 0 12 "\t" 7
KeyNameGroupID ""
8 SQLCHAR 0 12 "\t" 8
KeyForenameGroupID ""
9 SQLCHAR 0 12 "\t" 9
ReligionID ""
10 SQLCHAR 0 12 "\t" 10
CemeteryID ""
11 SQLCHAR 0 12 "\t" 11
DODYear ""
12 SQLCHAR 0 12 "\t" 12
DODMonth ""
13 SQLCHAR 0 12 "\t" 13 DODDay
""
14 SQLCHAR 0 3 "\t" 14
IsMonth ""
15 SQLCHAR 0 3 "\t" 15 IsYear
""
16 SQLCHAR 0 12 "\t" 16
DOBYear ""
17 SQLCHAR 0 12 "\t" 17
DOBMonth ""
18 SQLCHAR 0 12 "\t" 18 DOBDay
""
19 SQLCHAR 0 12 "\t" 19
KeyforenameID ""
20 SQLCHAR 0 12 "\r\n" 20
KeysurnameID ""Its really tough to figure this issue out without seeing the table structure
or having a file. Something that has helped me out in this situation is to
get it set up in DTS first. That way maybe you can visually see where
something isnt matched up. Just an idea.
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They are
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafields
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay
> ""
> 13 SQLCHAR 0 3 "," 14 IsMonth
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 NameID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODDay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYear
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBDay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||Hi Chris
Try something like:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 3 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I have generated a format file with bcp (SQL Server 2000) which generates a
> file which would work if there was a column to column relationship between
> the csv file and the table. My csv, however is in a different order.
> Basically the table has an identity field but the csv file doesn't. They are
> pretty similar other than that. I need the identity column in the table to
> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> invalid column number error. Can anyone point me in the right direction?
> Here is the format file, which is failing. There are also some columns at
> the end which have been missed off as there are no corresponding datafields
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 3
> Forename Latin1_General_CI_AS
> 3 SQLCHAR 0 12 "," 4 Age
> ""
> 5 SQLCHAR 0 12 "," 6
> InscriptionID ""
> 6 SQLCHAR 0 12 "," 7
> KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "," 9
> ReligionID ""
> 8 SQLCHAR 0 12 "," 10
> CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay
> ""
> 13 SQLCHAR 0 3 "," 14 IsMonth
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 NameID
> ""
> 2 SQLCHAR 0 510 "\t" 2
> Surname Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "\t" 3
> Forename Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "\t" 4 Age
> ""
> 5 SQLCHAR 0 12 "\t" 5 DOD
> ""
> 6 SQLCHAR 0 12 "\t" 6
> InscriptionID ""
> 7 SQLCHAR 0 12 "\t" 7
> KeyNameGroupID ""
> 8 SQLCHAR 0 12 "\t" 8
> KeyForenameGroupID ""
> 9 SQLCHAR 0 12 "\t" 9
> ReligionID ""
> 10 SQLCHAR 0 12 "\t" 10
> CemeteryID ""
> 11 SQLCHAR 0 12 "\t" 11
> DODYear ""
> 12 SQLCHAR 0 12 "\t" 12
> DODMonth ""
> 13 SQLCHAR 0 12 "\t" 13 DODDay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYear
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBDay
> ""
> 19 SQLCHAR 0 12 "\t" 19
> KeyforenameID ""
> 20 SQLCHAR 0 12 "\r\n" 20
> KeysurnameID ""
>
>|||I a slightly staggered sequence where I want 2 to go to 3 etc. Would
something like this work? I ask before I spend ages barking up the wrong
tree!
1 SQLINT 0 0 "" 0 id ""
1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
2 SQLCHAR 0 510 "," 2 Forename
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...
> Hi Chris
> Try something like:
> 8.0
> 14
> 1 SQLINT 0 0 "" 0 id ""
> 2 SQLCHAR 0 510 "," 2 Surname
> Latin1_General_CI_AS
> 3 SQLCHAR 0 510 "," 3 Forename
> Latin1_General_CI_AS
> 4 SQLCHAR 0 12 "," 4 Age ""
> 5 SQLCHAR 0 12 "," 6 InscriptionID ""
> 6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
> 7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
> 8 SQLCHAR 0 12 "," 9 ReligionID ""
> 9 SQLCHAR 0 12 "," 10 CemeteryID ""
> 10 SQLCHAR 0 12 "," 11 DODYear ""
> 11 SQLCHAR 0 12 "," 12 DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDay ""
> 13 SQLCHAR 0 3 "," 14 IsMonth ""
> 14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
> John
> "Chris" wrote:
>> I have generated a format file with bcp (SQL Server 2000) which
>> generates a
>> file which would work if there was a column to column relationship
>> between
>> the csv file and the table. My csv, however is in a different order.
>> Basically the table has an identity field but the csv file doesn't. They
>> are
>> pretty similar other than that. I need the identity column in the table
>> to
>> be ignored but datafield 1 to map to column 2 and so. All's I get is an
>> invalid column number error. Can anyone point me in the right direction?
>> Here is the format file, which is failing. There are also some columns at
>> the end which have been missed off as there are no corresponding
>> datafields
>> for them.
>> Here is mine
>> 8.0
>> 13
>> 1 SQLCHAR 0 510 "," 2
>> Surname
>> Latin1_General_CI_AS
>> 2 SQLCHAR 0 510 "," 3
>> Forename Latin1_General_CI_AS
>> 3 SQLCHAR 0 12 "," 4 Age
>> ""
>> 5 SQLCHAR 0 12 "," 6
>> InscriptionID ""
>> 6 SQLCHAR 0 12 "," 7
>> KeyNameGroupID ""
>> 7 SQLCHAR 0 12 "," 8
>> KeyForenameGroupID ""
>> 9 SQLCHAR 0 12 "," 9
>> ReligionID ""
>> 8 SQLCHAR 0 12 "," 10
>> CemeteryID ""
>> 10 SQLCHAR 0 12 "," 11
>> DODYear
>> ""
>> 11 SQLCHAR 0 12 "," 12
>> DODMonth ""
>> 12 SQLCHAR 0 12 "," 13
>> DODDay
>> ""
>> 13 SQLCHAR 0 3 "," 14
>> IsMonth
>> ""
>> 14 SQLCHAR 0 3 "/r/n" 15
>> IsYear ""
>> Here is the original
>> 8.0
>> 20
>> 1 SQLCHAR 0 12 "\t" 1
>> NameID
>> ""
>> 2 SQLCHAR 0 510 "\t" 2
>> Surname Latin1_General_CI_AS
>> 3 SQLCHAR 0 510 "\t" 3
>> Forename Latin1_General_CI_AS
>> 4 SQLCHAR 0 12 "\t" 4 Age
>> ""
>> 5 SQLCHAR 0 12 "\t" 5 DOD
>> ""
>> 6 SQLCHAR 0 12 "\t" 6
>> InscriptionID ""
>> 7 SQLCHAR 0 12 "\t" 7
>> KeyNameGroupID ""
>> 8 SQLCHAR 0 12 "\t" 8
>> KeyForenameGroupID ""
>> 9 SQLCHAR 0 12 "\t" 9
>> ReligionID ""
>> 10 SQLCHAR 0 12 "\t" 10
>> CemeteryID ""
>> 11 SQLCHAR 0 12 "\t" 11
>> DODYear ""
>> 12 SQLCHAR 0 12 "\t" 12
>> DODMonth ""
>> 13 SQLCHAR 0 12 "\t" 13
>> DODDay
>> ""
>> 14 SQLCHAR 0 3 "\t" 14
>> IsMonth ""
>> 15 SQLCHAR 0 3 "\t" 15
>> IsYear
>> ""
>> 16 SQLCHAR 0 12 "\t" 16
>> DOBYear ""
>> 17 SQLCHAR 0 12 "\t" 17
>> DOBMonth ""
>> 18 SQLCHAR 0 12 "\t" 18
>> DOBDay
>> ""
>> 19 SQLCHAR 0 12 "\t" 19
>> KeyforenameID ""
>> 20 SQLCHAR 0 12 "\r\n" 20
>> KeysurnameID ""
>>|||Hi Chris
If your column order is forename before surname then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
3 SQLCHAR 0 510 "," 2 Forename
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
If your column order is forename before surname and the file order is the
same then use:
8.0
14
1 SQLINT 0 0 "" 0 id ""
2 SQLCHAR 0 510 "," 2 Forename
3 SQLCHAR 0 510 "," 3 Surname
Latin1_General_CI_AS
Latin1_General_CI_AS
4 SQLCHAR 0 12 "," 4 Age ""
5 SQLCHAR 0 12 "," 6 InscriptionID ""
6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
8 SQLCHAR 0 12 "," 9 ReligionID ""
9 SQLCHAR 0 12 "," 10 CemeteryID ""
10 SQLCHAR 0 12 "," 11 DODYear ""
11 SQLCHAR 0 12 "," 12 DODMonth ""
12 SQLCHAR 0 12 "," 13 DODDay ""
13 SQLCHAR 0 3 "," 14 IsMonth ""
14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
John
"Chris" wrote:
> I a slightly staggered sequence where I want 2 to go to 3 etc. Would
> something like this work? I ask before I spend ages barking up the wrong
> tree!
>
> 1 SQLINT 0 0 "" 0 id ""
> 1 SQLCHAR 0 510 "," 2 Surname Latin1_General_CI_AS
> 2 SQLCHAR 0 510 "," 2 Forename
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:ABDF1747-B357-4117-9CAB-5317039F956A@.microsoft.com...
> > Hi Chris
> >
> > Try something like:
> >
> > 8.0
> > 14
> > 1 SQLINT 0 0 "" 0 id ""
> > 2 SQLCHAR 0 510 "," 2 Surname
> > Latin1_General_CI_AS
> > 3 SQLCHAR 0 510 "," 3 Forename
> > Latin1_General_CI_AS
> > 4 SQLCHAR 0 12 "," 4 Age ""
> > 5 SQLCHAR 0 12 "," 6 InscriptionID ""
> > 6 SQLCHAR 0 12 "," 7 KeyNameGroupID ""
> > 7 SQLCHAR 0 12 "," 8 KeyForenameGroupID ""
> > 8 SQLCHAR 0 12 "," 9 ReligionID ""
> > 9 SQLCHAR 0 12 "," 10 CemeteryID ""
> > 10 SQLCHAR 0 12 "," 11 DODYear ""
> > 11 SQLCHAR 0 12 "," 12 DODMonth ""
> > 12 SQLCHAR 0 12 "," 13 DODDay ""
> > 13 SQLCHAR 0 3 "," 14 IsMonth ""
> > 14 SQLCHAR 0 3 "\r\n" 15 IsYear ""
> >
> > John
> >
> > "Chris" wrote:
> >
> >> I have generated a format file with bcp (SQL Server 2000) which
> >> generates a
> >> file which would work if there was a column to column relationship
> >> between
> >> the csv file and the table. My csv, however is in a different order.
> >> Basically the table has an identity field but the csv file doesn't. They
> >> are
> >> pretty similar other than that. I need the identity column in the table
> >> to
> >> be ignored but datafield 1 to map to column 2 and so. All's I get is an
> >> invalid column number error. Can anyone point me in the right direction?
> >> Here is the format file, which is failing. There are also some columns at
> >> the end which have been missed off as there are no corresponding
> >> datafields
> >> for them.
> >>
> >> Here is mine
> >> 8.0
> >> 13
> >> 1 SQLCHAR 0 510 "," 2
> >> Surname
> >> Latin1_General_CI_AS
> >> 2 SQLCHAR 0 510 "," 3
> >> Forename Latin1_General_CI_AS
> >> 3 SQLCHAR 0 12 "," 4 Age
> >> ""
> >> 5 SQLCHAR 0 12 "," 6
> >> InscriptionID ""
> >> 6 SQLCHAR 0 12 "," 7
> >> KeyNameGroupID ""
> >> 7 SQLCHAR 0 12 "," 8
> >> KeyForenameGroupID ""
> >> 9 SQLCHAR 0 12 "," 9
> >> ReligionID ""
> >> 8 SQLCHAR 0 12 "," 10
> >> CemeteryID ""
> >> 10 SQLCHAR 0 12 "," 11
> >> DODYear
> >> ""
> >> 11 SQLCHAR 0 12 "," 12
> >> DODMonth ""
> >> 12 SQLCHAR 0 12 "," 13
> >> DODDay
> >> ""
> >> 13 SQLCHAR 0 3 "," 14
> >> IsMonth
> >> ""
> >> 14 SQLCHAR 0 3 "/r/n" 15
> >> IsYear ""
> >>
> >> Here is the original
> >>
> >> 8.0
> >> 20
> >> 1 SQLCHAR 0 12 "\t" 1
> >> NameID
> >> ""
> >> 2 SQLCHAR 0 510 "\t" 2
> >> Surname Latin1_General_CI_AS
> >> 3 SQLCHAR 0 510 "\t" 3
> >> Forename Latin1_General_CI_AS
> >> 4 SQLCHAR 0 12 "\t" 4 Age
> >> ""
> >> 5 SQLCHAR 0 12 "\t" 5 DOD
> >> ""
> >> 6 SQLCHAR 0 12 "\t" 6
> >> InscriptionID ""
> >> 7 SQLCHAR 0 12 "\t" 7
> >> KeyNameGroupID ""
> >> 8 SQLCHAR 0 12 "\t" 8
> >> KeyForenameGroupID ""
> >> 9 SQLCHAR 0 12 "\t" 9
> >> ReligionID ""
> >> 10 SQLCHAR 0 12 "\t" 10
> >> CemeteryID ""
> >> 11 SQLCHAR 0 12 "\t" 11
> >> DODYear ""
> >> 12 SQLCHAR 0 12 "\t" 12
> >> DODMonth ""
> >> 13 SQLCHAR 0 12 "\t" 13
> >> DODDay
> >> ""
> >> 14 SQLCHAR 0 3 "\t" 14
> >> IsMonth ""
> >> 15 SQLCHAR 0 3 "\t" 15
> >> IsYear
> >> ""
> >> 16 SQLCHAR 0 12 "\t" 16
> >> DOBYear ""
> >> 17 SQLCHAR 0 12 "\t" 17
> >> DOBMonth ""
> >> 18 SQLCHAR 0 12 "\t" 18
> >> DOBDay
> >> ""
> >> 19 SQLCHAR 0 12 "\t" 19
> >> KeyforenameID ""
> >> 20 SQLCHAR 0 12 "\r\n" 20
> >> KeysurnameID ""
> >>
> >>
> >>
>
>