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 header

Is there any way of including headers when we export a table using bcp
? Can't find the argument in bcp utility books online ?

Thanks for the helpPatrik (patrik.maheux@.umontreal.ca) writes:
> Is there any way of including headers when we export a table using bcp
> ? Can't find the argument in bcp utility books online ?

BCP does not unfortunately not support headers very well. If they can
be molded into the format of the rest of the file, you can sneak by.

Here are some examples:

value1,value2,value3
12,23,234

This is easy, use the -F option to start on row 2. Here is a tricker one:

value1,value2,value3
"text",12,"more test"

For this format you need a format file anyway for the data. This format
file will specify that each record begins with an empty column with " as
terminator. The result is that the header slips into that empty column
for the first row. You should not use -F.

Here is a dead end:

value1,value2,value3
12,"my text",12

I can't find a way to bulk-load this file, because the header does not
match the format of the data rows, and you can hide it an empty column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Bcp header

I am trying to run a query and put the results into an excel spreadsheet
automatically using bcp command.
bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppasswor
d
The command works beautifully except that it overwrites the header column of
the excel file as well. Is there anyway in bcp to have the results be writte
n
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksSee this thread:
http://groups.google.ca/groups?selm...0%40tkmsftngp12
Anith|||Use a select union statement, the first select will be your header names
and the second select will be your results, this should work just fine
seeing that you are already outputting character data.
bcp "SELECT CONVERT(varchar(50),'COUMUMN_A') as 'a',
CONVERT(varchar(50),'COUMUMN_B') as 'b', CONVERT(varchar(50),'COUMUMN_C') as
'c' UNION SELECT CONVERT(varchar(50),X.id), CONVERT(varchar(50),X.name),
CONVERT(varchar(50),X.value) FROM DatabaseName.Owner.TableName X" queryout
C:\testing.xls -c -Sservername -Uusername -Ppassword
"inquisite" wrote:

> I am trying to run a query and put the results into an excel spreadsheet
> automatically using bcp command.
> bcp "sql query" queryout C:\testing.xls -c -Sservername -Uusername -Ppassw
ord
> The command works beautifully except that it overwrites the header column
of
> the excel file as well. Is there anyway in bcp to have the results be writ
ten
> leaving the header column intact. Alternatively, I would like the results
to
> be written along with the column names.
> Thanks

bcp Export to CSV with headers

I’m trying to find a way to export all data from a table in to a csv file with a header. I’m not concerned at this point with dynamic headers (that would be nice but at this point I’m not picky). I would just like to find a way to programmatically drop a table to a csv file.

use tempdb

go

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"',

'"' + convert(varchar(50), ID) + '"'

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

DECLARE @.bcpCommand varchar(2000)

SET @.bcpCommand = 'bcp tempdb..vw_export out

c:\test002.txt -c -T -S SERVER'

EXEC master..xp_cmdshell @.bcpCommand

I get this error when I run this query

Create View or Function failed because no column name was specified for column 1.

If I’m going about this the completely wrong direction let me know so I can get heading the right direction to get this done.

Try the following...

xp_cmdshell 'osql /S<ServerName> /E /Q"set nocount on select top 10 Convert(Varchar(50),name) as name, id from sysobjects" /oc:\test.txt'

|||Hi,

For your view try this and you will be alright:

create view vw_csvexport

as

select

'"' + convert(varchar(50), Name) + '"' as vwNAME,

'"' + convert(varchar(50), ID) + '"' as vwID

from TestHeaderTable

union all

SELECT top 100 percent

Name = '"' + name + '"',

ID = '"' + convert(varchar(50), ID) + '"'

FROM TestDataTable

go

Actually in the view your returning table should always have specific column name, and since you had not specify any, you had the error.

Cheers

BCP blank header line

I am using BCP to export the contents of a view into a text file. Everything is running jsut ifne, except the resulting file has a blank first line. Is there a way to preven this?

BCP statement is as follows:

bcp "select pospay from son_db.dbo.vw_pos_pay order by account desc, code asc" queryout D:\elite\USbank\PositivePay_Current\x340.d150364i. d100.txt -T -c

Thanks in advance for any help!probably the first row is a NULL. is the pospay column nullable? if so you can add a where clause to your query to omit the null.|||You can take help of :-F first_row Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.|||that only matters for importing data, Satya. the OP is exporting.|||Jezemine
Look at the BOL entry I've pasted there: Specifies the number of the first row to export from a table or import from a data file

BC30451 Name 'whatever' is not declared

I'm migrating a report from access to RS. What I want to do is for each
group header, have a formula that shows that group's total as a % of
the grand total. Example:
Name Sales % of total
Tom 5 25%
Joe 15 75%
so, in my table, I have a table footer with a textbox "textbox1" with
the formula
=SUM(Fields!SALES.Value )
and in the group1 header, i have a textbox "textbox2". For the formula,
i put:
=Fields!SALES.Value / textbox1.value
This works in access, but apparently not in RS (?). I get an error
message that textbox1 is not declared. It seems like I must be missing
something simple here. Any Ideas? I could put subqueries in my dataset
I suppose, but that seems like an awfully long way around to do what I
need. Thanks in advance...
EricWill ReportItems!Textbox1.Value work?
Steve MunLeeuw
<c-eric.geil@.mci.com> wrote in message
news:1161292630.251749.186250@.k70g2000cwa.googlegroups.com...
> I'm migrating a report from access to RS. What I want to do is for each
> group header, have a formula that shows that group's total as a % of
> the grand total. Example:
> Name Sales % of total
> Tom 5 25%
> Joe 15 75%
> so, in my table, I have a table footer with a textbox "textbox1" with
> the formula
> =SUM(Fields!SALES.Value )
> and in the group1 header, i have a textbox "textbox2". For the formula,
> i put:
> =Fields!SALES.Value / textbox1.value
> This works in access, but apparently not in RS (?). I get an error
> message that textbox1 is not declared. It seems like I must be missing
> something simple here. Any Ideas? I could put subqueries in my dataset
> I suppose, but that seems like an awfully long way around to do what I
> need. Thanks in advance...
>
> Eric
>|||that worked...thanks!
What's the deal with the expression builder? It seems like it would
have some functions and/or controls listed in there to give the user
some clue as to their verbiage...
thanks again..
eric