Generate sql: bcp

Showing posts with label bcp. Show all posts

Sunday, February 26, 2012

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value â' â'5600055â'
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:
> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> > Hi,
> >
> > I want to write a BCP format file, which uses one data column Value to map
> > to
> > Two Column Fields in the table.
> >
> > For Example:
> > Data File: Pin Value - "5600055"
> > Table Columns
> > 1) tmp_pin
> > 2) per_pin
> >
> > Is the above possible to specify in the format file? Does this flexibility
> > is provided by BCP Utility to load the data.
> >
> > Regards
> > Govardhan MV
> >
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:
> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value â' â'5600055â'
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
>> What are the datatypes and what are the widths, i.e. how do you map
>> the 5600055 to the two values?
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> This type of functionality is best done with DTS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinpub.com
>> ..
>> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in
>> message news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>> Hi,
>> I want to write a BCP format file, which uses one data column Value
>> to map to
>> Two Column Fields in the table.
>> For Example:
>> Data File: Pin Value â' â'5600055â'
>> Table Columns
>> 1) tmp_pin
>> 2) per_pin
>> Is the above possible to specify in the format file? Does this
>> flexibility is provided by BCP Utility to load the data.
>> Regards
>> Govardhan MV

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:

> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV
|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:

> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>
|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>
|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]

Format Files - Bcp

Hi,
I want to write a BCP format file, which uses one data column Value to map t
o
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MVThis type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||HI Tom,
But I want this on BCP not on DTS.
Regards
Govardhan MV
"Tom Moreau" wrote:

> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||What are the datatypes and what are the widths, i.e. how do you map the
5600055 to the two values?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
This type of functionality is best done with DTS.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
Hi,
I want to write a BCP format file, which uses one data column Value to map
to
Two Column Fields in the table.
For Example:
Data File: Pin Value – “5600055”
Table Columns
1) tmp_pin
2) per_pin
Is the above possible to specify in the format file? Does this flexibility
is provided by BCP Utility to load the data.
Regards
Govardhan MV|||Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Yes you can define a fixed length file with fixed length fields.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value - "5600055"
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Hi Wayne Snyder,
How do we write the format file for this,
Can you please explane me in detail.
Data File contain
560030
Table abc
a1
b1
are teh columns
I want to load 560030 to a1, b1 using BCP . please Help me in writting
Format file for this.
Regards
Govardhan MV
"Wayne Snyder" wrote:

> Yes you can define a fixed length file with fixed length fields.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
>
>|||That's nice. What do you mean by "same" - int, char(5)... ? How about
giving us DDL and how you want the string 5600055 to be divided up?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
news:18508099-5AA8-4C0C-9444-B6198F3DC099@.microsoft.com...
Hi Tomm
Both the datatype and size are same.
Regards
Govardhan MV
"Tom Moreau" wrote:

> What are the datatypes and what are the widths, i.e. how do you map the
> 5600055 to the two values?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OtOMKfQZFHA.3320@.TK2MSFTNGP12.phx.gbl...
> This type of functionality is best done with DTS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Govardhan MV" <GovardhanMV@.discussions.microsoft.com> wrote in message
> news:93A5A369-32DA-4859-BAAF-06CB36301C38@.microsoft.com...
> Hi,
> I want to write a BCP format file, which uses one data column Value to map
> to
> Two Column Fields in the table.
> For Example:
> Data File: Pin Value – “5600055”
> Table Columns
> 1) tmp_pin
> 2) per_pin
> Is the above possible to specify in the format file? Does this flexibility
> is provided by BCP Utility to load the data.
> Regards
> Govardhan MV
>|||Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>|||If that's the original intent, I'd wonder myself. Let's get some real specs
and then we can figure it out.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Robert Klemme" <bob.news@.gmx.net> wrote in message
news:uYu%23CUcZFHA.1412@.TK2MSFTNGP12.phx.gbl...
Tom Moreau wrote:
> That's nice. What do you mean by "same" - int, char(5)... ? How
> about giving us DDL and how you want the string 5600055 to be divided
> up?
As I understand the question OP doesn't want to split the single string
but to put the same data into two columns. I wonder though what's the
benefit of two columns with exactly the same value...
Kind regards
robert
[vbcol=seagreen]
>

Format Files

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

Sunday, February 26, 2012

Format Files - Bcp

Format Files - Bcp

Format Files - Bcp

Format Files

Format Files

Format Files

Format File Problem - sorry did the first post wrong

Format File Problem - sorry did the first post wrong

Format File Problem - sorry did the first post wrong

Generate sql

Blog Archive

About Me