bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.
Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:

> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.
sql

BCP utility

In the process of exporting data from SQL data file to text file through BCP utility I am not getting the Column names.How can I get the column names through BCP utility?
I used this script
exec master..xp_cmdShell 'bcp "select * from regulator.dbo.TEMPTBLBRANCHNOTUPLOAD" QueryOUT \\indiadb\ftproot\CLIENT_BRANCH_UPLOAD\branchnotup loaded.csv -S indiadb -U sa -P sasocrates -k -r \n -c -t "," -q'It doesn't...did you look at BOL?

You can however "fake it out"

It would require a view or using QUERYOUT...

Something like

SELECT 'HEADING1','HEADING2',ect
UNION ALL
SELECT Col1, Col2, ect
FROM yourTable

Just make sure you CONVERT everyhting to varchar...

bcp utility

I'm using the bcp utility to export data to a network file. Is there a way to
output the field names in addition to the data? According to Books Online,
there isn't a switch for this.Hi Mike
Look at using the import/export wizard, DTS or SSIS to do this, you can do
it with BCP using something like:
BCP "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip,
contract FROM ( SELECT CAST(au_id as varchar(30)) as au_id, au_lname,
au_fname, phone, address, city, state, zip, CAST(contract as varchar(30)) AS
contract, 1 AS orderby FROM PUBS..Authors UNION ALL SELECT 'au_id',
'au_lname', 'au_fname', 'phone', 'address', 'city', 'state', 'zip',
'contract', 0 ) A ORDER BY orderby" QUERYOUT authors.txt -c -T -S (local)
but it's a bit of a cludge!
John
"mike" wrote:
> I'm using the bcp utility to export data to a network file. Is there a way to
> output the field names in addition to the data? According to Books Online,
> there isn't a switch for this.sql

bcp out file including all the columns names

How to bcp out file to also include all the table column names?

Thank you!

What is the purpose of you wanting to have the table column names inside the bcp file?

|||

Users ask for it. It was not my choice.

|||If you need to know the column names that exist for a given BCP file, then you can create a format file during your bcp out. For more information about format files, please see topic "Creating a Format File" in SQL Server 2005 Books Online.sql

bcp command to give colums

Can someonte tell me that if i
bcp bda..mytable out c:\discounts.xls -c -p , how can I put the first row as my column names since I get only dataIt won't...but there are other ways aroung it..you can use query out and supply a union like

SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99

Just need to make sure you're dfatatypes are converted to varchar

What about DTS to an EXCEL, or csv?|||Thanks very much it worked like magi

Originally posted by Brett Kaiser
It won't...but there are other ways aroung it..you can use query out and supply a union like

SELECT 'col1','col2','col3'...
UNION ALL
SELECT Col1,col2,col3 FROM myTable99

Just need to make sure you're dfatatypes are converted to varchar

What about DTS to an EXCEL, or csv?

bcp column names

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 -Ppassword
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 written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
Thanks
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 -Ppassword
> 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 written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

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 -Ppassword
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 written
leaving the header column intact. Alternatively, I would like the results to
be written along with the column names.
ThanksUse 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 -Ppassword
> 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 written
> leaving the header column intact. Alternatively, I would like the results to
> be written along with the column names.
> Thanks
>

bcp column names

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.
ThanksUse 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 and keyword column names

When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names... :)
"Dan Guzman" wrote:
> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> > When trying to import data from a text file using BCP (where one of the
> > column names of the table is the keyword 'Key'), I get a syntax error
> > which
> > is to be expected. For keyword table names, you use [tablename] in the
> > bcp
> > command. Is there a special command that I can use that will do something
> > similar for keyword column names?
>
>

BCP and keyword column names

When trying to import data from a text file using BCP (where one of the
column names of the table is the keyword 'Key'), I get a syntax error which
is to be expected. For keyword table names, you use [tablename] in the bcp
command. Is there a special command that I can use that will do something
similar for keyword column names?
You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
Also, it's a good practice to avoid using reserved words as column and
object names.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hielien" <Hielien@.discussions.microsoft.com> wrote in message
news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
> When trying to import data from a text file using BCP (where one of the
> column names of the table is the keyword 'Key'), I get a syntax error
> which
> is to be expected. For keyword table names, you use [tablename] in the
> bcp
> command. Is there a special command that I can use that will do something
> similar for keyword column names?
|||Thanks!! Can't believe it is that easy... If I had a choice, I wouldn't use
reserved words. Unfortunately the database that I have to connect to,
already have such columns names...
"Dan Guzman" wrote:

> You can specify the '-q' BCP parameter to SET QUOTED_IDENTIFIER ON.
> Also, it's a good practice to avoid using reserved words as column and
> object names.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hielien" <Hielien@.discussions.microsoft.com> wrote in message
> news:0B8AE489-061B-4F4E-B954-FF4439A1D5AD@.microsoft.com...
>
>

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
Lily
Actually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited file. I understand that when using BCP, one would first extract the column names and then concatenate the data. I am looking for a means to dump the column names. I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily
|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily
|||Everyone always suggests using the EM or QA tools - but they are not on every workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL SERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - with headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for future use.

BCP and Column Names

Hi -
I would like to export the column names and data from a table to a tab delim
ited file. I understand that when using BCP, one would first extract the c
olumn names and then concatenate the data. I am looking for a means to dum
p the column names. I am
an extremely novice user, so an example would be greatly appreciated.
Thanks!
LilyActually it's very easy, open query analyzer, go to options, select
Results tab, then select Results to File in Default results target drop
down box, then select the delimiter in the drop down box below, make
sure Print column headers box is checked, then click OK
then type in the follow:
set nocount on
select * from [your table]
run the query and it will ask you for the filename
Eric Li
SQL DBA
MCDBA
Lily wrote:
> Hi -
> I would like to export the column names and data from a table to a tab delimited f
ile. I understand that when using BCP, one would first extract the column names an
d then concatenate the data. I am looking for a means to dump the column names.
I a
m an extremely novice user, so an example would be greatly appreciated.
> Thanks!
> Lily|||BCP will not export the column heading. You can use OSQL to do this. Look
at Books on LIne for the arguments to pass to get it to be formated as you
want.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi,
Easy way is to use the Import and Export Utility from SQL server program
groups. Select the source as sql server and destination as Text file.
In the "destination file format" Screen in the wizard check the option
"First row has column names".
This will give you the text file out put with comma seperated with column
headings.
Thanks
Hari
MCDBA
"Lily" <anonymous@.discussions.microsoft.com> wrote in message
news:88A999B6-73A3-4612-A6CE-93F9403039D1@.microsoft.com...
> Hi -
> I would like to export the column names and data from a table to a tab
delimited file. I understand that when using BCP, one would first extract
the column names and then concatenate the data. I am looking for a means
to dump the column names. I am an extremely novice user, so an example
would be greatly appreciated.
> Thanks!
> Lily|||Everyone always suggests using the EM or QA tools - but they are not on ever
y workstation - nor should they be.
How about going into EXCEL - DATA>IMPORT EXTERNAL DATA>IMPORT DATA>NEW SQL S
ERVER CONNECTION...
Follow that through - open the table you want and you get data in EXCEL - wi
th headings.
EXCEL can save as TDF, CSV, XLS - whatever you want.
Once you create a NEW SQL SERVER CONNECTION, it remains in the list for futu
re use.