asked on

SQL column Headers to a TXT file

hi All

I am using the following to take data from an output file and put into a .TXT file
Is it possible within the same procedure to output the first line as the column headers ? if So how do I do it please

thanks
Mike

CREATE Procedure Z_TEST_BCP_Text_File
(
@table varchar(100),
@FileName varchar(100)
)
as
If exists(Select * from information_Schema.tables where table_name='databaselog')
Begin
Declare @str varchar(1000)
set @str='Exec Master..xp_Cmdshell ''bcp "Select * from '+db_name()+'..'+@table+'" queryout "'+@FileName+'" -c -t"|" -T '''
Exec(@str)
end
else
Select 'The table '+@table+' does not exist in the database'

to execute I run the following command
EXEC Z_TEST_BCP_Text_File 'My_File_Name','C:\My_Output_File.txt'

ASKER CERTIFIED SOLUTION

Lawrence Barnes

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MECR123

ASKER

hi

thanks for that - that is a good link

Just one further question please

I have concatenated the 2 files - Header file and Data file - however the data starts off on line 1 as well - is there a way to get this to start on line 2 so that
Line 1 = Headers - Line 2 onwards is the data

thanks
Mike

Lawrence Barnes

Sounds like you need to switch the order of the concatenation of the two files.

master..xp_cmdshell 'data.txt >> column headers.txt'
master..xp_cmdshell 'del data.txt'

MECR123

ASKER

hi

thanks for that - but I that is not the issue

Line one contains the header and first line of data - the file is coming out as 'test sample 01' attachment
I would like it to come out as 'test sample 01 good' attachment

appreciate you help

thanks
Mike
TEST-SAMPLE-01.txt
TEST-SAMPLE-01-Good.txt

Lawrence Barnes

I will look at this tonight...sorry about the delay.