BCP output with header and trailer

I'm copying data out to a file with pipe delimiters. I would like to
add a header and trailer.

Is this possible? If so, please help me with the steps.

Also, is it possible to append to a text file while doing the bcp? If
so, how?"sbh" <stephanie.herbert@.tdh.state.tx.us> wrote in message
news:1d68006.0403040850.23a28ccb@.posting.google.co m...
> I'm copying data out to a file with pipe delimiters. I would like to
> add a header and trailer.
> Is this possible? If so, please help me with the steps.
> Also, is it possible to append to a text file while doing the bcp? If
> so, how?

Not directly, but you might be able to use -queryout or osql.exe -o to
execute a query or stored procedure which returns your header and trailer
information as well as the data. Since you don't say what you want in your
header/trailer, it's hard to be more precise. As for appending, I believe
that both bcp and osql overwrite any existing file, but a simple script in
Perl, VBScript etc. would be a more flexible solution.

Simonsql

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David Lozzi
Hi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John
|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John
|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:

> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We nee
d
> to use another delimiter and I'm wondering if we can use multiple characte
rs
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:

> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

BCP delimiters

Howdy,
I have a fmt file used for importing data from a IBM database into SQL.
Currently we're using the pipe | as a delimiter and it works great. We need
to use another delimiter and I'm wondering if we can use multiple characters
like %$. If so, is it just as easy as changing the delimiter in the fmt
file?
Thanks,
David LozziHi David
"David Lozzi" wrote:
> Howdy,
> I have a fmt file used for importing data from a IBM database into SQL.
> Currently we're using the pipe | as a delimiter and it works great. We need
> to use another delimiter and I'm wondering if we can use multiple characters
> like %$. If so, is it just as easy as changing the delimiter in the fmt
> file?
> Thanks,
> David Lozzi
>
The field terminator can be a string of up to 10 printable characters. See
http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
file is a text file so it can be edited with any text editor.
John|||My b, did I mention its SQL 7? Does that change anything?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> Hi David
> "David Lozzi" wrote:
>> Howdy,
>> I have a fmt file used for importing data from a IBM database into SQL.
>> Currently we're using the pipe | as a delimiter and it works great. We
>> need
>> to use another delimiter and I'm wondering if we can use multiple
>> characters
>> like %$. If so, is it just as easy as changing the delimiter in the fmt
>> file?
>> Thanks,
>> David Lozzi
> The field terminator can be a string of up to 10 printable characters. See
> http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> file is a text file so it can be edited with any text editor.
> John|||Hi David
"David Lozzi" wrote:
> My b, did I mention its SQL 7? Does that change anything?
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:D91F9576-9BFD-49DD-ABA6-1170B8B03CAA@.microsoft.com...
> > Hi David
> >
> > "David Lozzi" wrote:
> >
> >> Howdy,
> >>
> >> I have a fmt file used for importing data from a IBM database into SQL.
> >> Currently we're using the pipe | as a delimiter and it works great. We
> >> need
> >> to use another delimiter and I'm wondering if we can use multiple
> >> characters
> >> like %$. If so, is it just as easy as changing the delimiter in the fmt
> >> file?
> >>
> >> Thanks,
> >>
> >> David Lozzi
> >>
> > The field terminator can be a string of up to 10 printable characters. See
> > http://msdn2.microsoft.com/en-us/library/aa173859(SQL.80).aspx and
> > http://msdn2.microsoft.com/en-us/library/aa196735(SQL.80).aspx. The format
> > file is a text file so it can be edited with any text editor.
> >
> > John
>
I don't have a SQL 7 system to check, but you may want to look up BCP in
books online.
John

Bcp and column delimiters

Where can I get the complet info on Bcp parameters ?
I want to export a table with "$" delimiting columns
what would be the syntax of the bcp command ?You need to use a format file...

Do you know what one looks like?

What version of SQL Server?|||Brett,

If he were executing bcp through query analyzer could he not just use the -t field terminator?

e.g.

bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername|||Originally posted by rocket39
Brett,

If he were executing bcp through query analyzer could he not just use the -t field terminator?

e.g.

bcp database.dbo.tablename out -c -t'$' -r'\n' -Uusername -Ppassword
-Sservername

oops mispoke, bcp is not run through query analyzer...and documentation for all the of the flags for bcp can be found in BOL by searching on bcp.|||Been working with mainframe flatfiles too long..

And who says you can't do it out of QA?

(Still need to tweak the paramters I'm afraid)

USE Northwind
GO

DECLARE @.cmd varchar(8000), @.username varchar(30), @.password varchar(30), @.File varchar(255), @.Table sysname

SELECT @.username = 'sa', @.password = 'xxx', @.Table = 'Orders', @.File = 'D:\Tax\Order.dat'

SET @.cmd = 'bcp '+ db_name() + '.dbo.' + @.Table + ' out '
+ @.File
+ ' -c -t'
+ '''' + '$' + ''''
+ '-r'
+ '''' + '\nl' + ''''
+ '-U' + @.username +' -P' + @.password +' -S' + @.@.servername

EXEC master..xp_cmdshell @.cmd|||Thanks to both of you,
that will work great I think !

Hey, look I've got a OneStar promotion ! :))|||Originally posted by Karolyn
Hey, look I've got a OneStar promotion ! :))


Who da man...uhh...woman(?)

You go (fill in gender)!

Lots of great SQL stuff out here...

check out..

http://www.sqlteam.com/Default.asp