formatting question with osql batch file

Hi all,

This is my first batch file and I want to query a database and output
it in a textfile. Up to now that works, the only problem is the
formatting in the text file. It's all screewed up...lines aren't lined
up and columns aren't right...how do you format the result from a
query to a text file. Here's my code:

osql.exe -S MYMACHINE -w 30 -E -d Demo2 -Q "select * from Title where
frequency = 'monthly'" -o "C:\output.txt"

Thanks...
JMTFigured it out guys...thanks...
It was because by default it only writes 80 characters per line if you
don'T specify how wide your columns should be. I had columns about 50
characters wide in my table...

Later,
JMT

formatting numbers in Crystal Reports

:confused:

Hello,

I need to format my numbers as follows:
In my ttx file I have a field called Quantity with a datatype of Number and the value that i used is 1.00
I need to know how to how to get the value of 1 and not 1.

I also have another line
Quantity number 1.500

and when i simply put that on the report, it prints the number 2. What am i doing wrong? This is what I have as code so far.

if int({tester.quantity2}) = {tester.quantity2} then
formula = totext({tester.quantity2}, "#####.#####")
else
formula = totext({tester.quantity2})
end if

Thanks
NadishaHi,

Try to set the format at design time. Right Click the formula/database field. Goto Format option, there you can find info about formatting under "Number" tab.

Formatting Dates

I imported data from a text file, where dates are currently in the format: 050729 to represent July 29, 2005.

How could I write a sql statment to put make this show up as 07/29/05?

Thanks

How did you do the import? What is the datatype you currently have for the column?|||It's a varchar now. I tried to convert to a date, but there must be fields somewhere with bad data because it won't let me. The problem is though that this table has over 6 million rows so it's not easy to figure out where the problem is.|||

Your value of 050729 is not clear as to which part of it is month and which part of it is day.

A quick SELECT CONVER(Datetime, '050729') returns 2005-07-2900:00:00.000.Not sure if that is what you want?

|||

The select you showed me is what I was looking for. Could I then do something like this?

update RetailSalesConvert(DateTime,InvoiceDate)

|||

First, is InvoiceDate varchar? Then it makes no sense. You could add another column with Datetime data type (Set it to null initially). Update that row with the converted value from InvoiceDate, then drop the existing column, rename the new column to old.

Here's a series of steps to follow:

--Add a new columnALTER Table RetailSalesADD COLUMN InvoiceDate2DatetimeNULLGo--Update the column with the values converted to datetimeupdate RetailSalesSet InvoiceDate2 =Convert(DateTime,InvoiceDate)GO--Drop the existing InvoiceDateALTER TABLe RetailSalesDROP COLUMN InvoiceDateGo--Rename the new column to InvoiceDatesp_Rename'InvoiceDate2','InvoiceDate'

Formatting columns for a flat file destination control

So i've created a flat file destination control and have mapped the columns. At what point can you control which column shows up first?

cheston wrote:

So i've created a flat file destination control and have mapped the columns. At what point can you control which column shows up first?

In the advanced section of the Flat File Connection Manager screen.|||I do not see an option to move the columns and dragging them does not work either.
|||

cheston wrote:

I do not see an option to move the columns and dragging them does not work either.

Nope. You have to define them in the desired order first.|||If that's too painful, it might worth a try to edit the .dtsx file itself and reorder the DTS:FlatFileColumn elements.

formatted file using stored procedure

how do i create a formatted file using a stored procedure?

for eg. if i have the following...

date : 5th April : should occupy 6 spaces (numeric) (05/04/2003)
Place: London : should occupy 20 spaces (text).
Country: United Kingdom should occupy 20 spaces (text)
EOL Terminator: (1 character only)
:
:
:

the sample final output should be like:

050403London United Kingdom
050403Washington DC USA
050403Delhi India

any quick help is appreciated,earlier post discarded the spaces between the sample records... it should reas as

050403London^^^^^^^^^^^^^^United Kingdom^^^^^^
:
:

where ^ denotes a blank space.

regards|||In your SELECT statement CONVERT the text fields to fix length CHAR. So if City needs to be 40 characters it is CONVERT(CHAR(40), City). You can run the stored procedure from ISQL using -h-1 so no header get printed

C:\>isql -E -S -Q"select status,CONVERT(CHAR(35),name)+CONVERT
(char(10),crdate,103) from sysobjects where type='S'" -h-1

-2147483645 sysobjects 13/11/1998
-2147483643 sysindexes 13/11/1998
-2147483645 syscolumns 13/11/1998
-2147483645 systypes 13/11/1998
-2147483647 syscomments 13/11/1998
-2147483648 sysfiles1 13/11/1998
-2147483647 syspermissions 13/11/1998

Substitue the SELECT with your stored procedure. Or even better use BCP.

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

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.
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 ""
See later thread!
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.
> 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 ""
>
>

Format Files

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.
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 ""See later thread!
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.
> 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 ""
>
>

Format Files

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.
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 ""See later thread!
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.
> 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 ""
>
>

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