Format of the initialization string does not conform to specification starting at index 0.

can't figure out this error. i think i'm writing something wrong in the connnection string. i'm using SQL Server Express, if that helps.

in my config file:

<connectionStrings>
<add name="OfficialScribblerConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OfficialScribbler.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

in my aspx file:

Dim DBConnection = New OleDbConnection("OfficialScribblerConnectionString")

DBConnection.Open()

Dim SQLString As String = "SELECT PostedDate From Entries_tbl ORDER BY PostedDate"

Try this way

Dim DBConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("OfficialScribblerConnectionString").ConnectionString)

Hope this will help you

|||thanks for the tip. i will try later on and let you know how it goes.|||

i tried that and now the error says this:

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

|||

Change the connection string as

<

addname="OfficialScribblerConnectionString"connectionString="Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\OfficialScribbler.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient"/>Change the code to

Dim

DBConnectionAsNew OleDbConnection(ConfigurationManager.ConnectionStrings("OfficialScribblerConnectionString").ConnectionString)

DBConnection.Open()

Hope this will help you.

Let me know if you need any further help

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 ""
> >>
> >>
> >>
>
>

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...[vbcol=seagreen]
> 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:
|||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...
>
>

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 a
re
> 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 datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> 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 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 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 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 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 a
re
> 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 datafield
s
> for them.
> Here is mine
> 8.0
> 13
> 1 SQLCHAR 0 510 "," 2 Surna
me
> 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 DODYe
ar
> ""
> 11 SQLCHAR 0 12 "," 12
> DODMonth ""
> 12 SQLCHAR 0 12 "," 13 DODDa
y
> ""
> 13 SQLCHAR 0 3 "," 14 IsMon
th
> ""
> 14 SQLCHAR 0 3 "/r/n" 15
> IsYear ""
> Here is the original
> 8.0
> 20
> 1 SQLCHAR 0 12 "\t" 1 Name
ID
> ""
> 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 DODD
ay
> ""
> 14 SQLCHAR 0 3 "\t" 14
> IsMonth ""
> 15 SQLCHAR 0 3 "\t" 15 IsYe
ar
> ""
> 16 SQLCHAR 0 12 "\t" 16
> DOBYear ""
> 17 SQLCHAR 0 12 "\t" 17
> DOBMonth ""
> 18 SQLCHAR 0 12 "\t" 18 DOBD
ay
> ""
> 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...[vbcol=seagreen]
> 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:
>|||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...
>
>