March 22, 2004 at 12:22 pm
I'm exporting query data to a flat text file using bcp. I need to export column headers into the file. Does anyone know how to do that?
Thanks!
March 22, 2004 at 12:33 pm
Can't do it directly. BCP doesn't copy column names.
Maybe if you create a temp table with the column names as records and also the data you need to export.
You can create a file with columns names using isqlw (QA by the command line) but you won't be able to import from it.
Use :
isqlw -Uuser -Ppassword -iPath To Query.sql -oOutput File
March 22, 2004 at 12:40 pm
Is the flat file an intermediate format or a final format? Why are the column names so important to embedd in the data? Answering these might give you better alternatives.
You might try creating a query that unions the column names row with the result set. However, it might be too expensive to convert all your native data rows to text befor exporting them.
Peter Evans (__PETER Peter_)
March 22, 2004 at 2:54 pm
Use isqlw in command line instead.
Configuration file (-C) can help you.
Check syntax with isqlw /? from prompt.
March 23, 2004 at 5:16 am
rachel,
here's what i use and works just fine. follow the instructions and you shouldn't have any problems. if you have any questions feel free to ask ones.
--1) create view
CREATE VIEW v_BcpOutMyTableWithColumnNames
AS
SELECT TOP 100 PERCENT Col01, Col02, Col03...
FROM (
SELECT 1 seq,
'Col01' Col01, 'Col02' Col02, 'Col03' Col03, ...
UNION ALL
SELECT 2 seq,
cast(Col01 as varchar(100)),
cast(Col02 as varchar(100)),
cast(Col03 as varchar(100)),...)
FROM db_name..table_name
) x
--2)bcp out results from this view
EXEC master..xp_cmdshell 'bcp db_name..v_BcpOutMyTableWithColumnNames out destination_path\FileExtract.txt -Sserver_name -Uuser_name -Ppassword -c'
October 14, 2004 at 7:31 am
First, thank you very much to Newbie!, I tried for months to find something and you answered the question.
I waned to put an example on here, because it took me a little bit to figure out how exactly to manipulate it to export exactly what I wanted in a .csv file.
Using Query Analyzer:
CREATE VIEW NPD_SNAPSHOT_VIEW
AS SELECT TOP 100 PERCENT FIRST_NAME, LAST_NAME, DOB
FROM (SELECT 1 seq, 'FIRST_NAME' FIRST_NAME, 'LAST_NAME' LAST_NAME, 'DOB' DOB
UNION ALL
SELECT 2 seq,
cast(FIRST_NAME as varchar(50)),
cast(LAST_NAME as varchar(50)),
cast(DOB as varchar(8))
FROM NPD_SNAPSHOT) x
Then, once the view is saved, build a package or add to an existing package an 'Execute SQL Task' as such:
DECLARE @FileName varchar(50),
@bcpCommand varchar(2000)
SET @FileName = REPLACE ('\\server1\folder1\weekly\NPD'+(CONVERT(char(8),GETDATE(),1)+'.csv'),'/','-')
SET @bcpCommand = 'bcp "SELECT * FROM COLLECTIONS..NPD_SNAPSHOT_VIEW" queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -U ReportsUser -P ReportsUser -w'
EXEC master..xp_cmdshell @bcpCommand
Using this will create a file that will contain the current date so that you never have to worry about managing the output process. The only thing I couldn't figure out was how to format the date to show as NPD20041014.csv instead of NPD10-14-04.csv.
Owner & Principal SQL Server Consultant
Im Your DBA, Inc.
https://www.imyourdba.com/
February 27, 2007 at 8:59 am
I created the following DOS based script to solve this issue. This also enters in a datestamp for the file name:
BCP "specific table" out "filename.csv" -c /t, -Sserver -Uuser -Ppassword
REM Column Headers go here
ECHO Loan Number,ACH Number,ACH Amount,Effective Date,Created By > "filename_%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%.csv"
TYPE "filename.csv" >>"filename_%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%.csv"
DEL "filename.csv"
Let me know if this is helpful to you!
And they say DOS doesn't have its usefulness any more!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply