Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL column Headers to a TXT file

Posted on 2013-11-27
5
Medium Priority
?
386 Views
Last Modified: 2013-12-16
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'
0
Comment
Question by:MECR123
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
Lawrence Barnes earned 1200 total points
ID: 39681291
Two options I think.
1. Create a union query that joins the column names with the data and then BCP that out.  The data would have to be converted to character in the union.
2. BCP out the column names, BCP out the data into a temporary file, then join the two files. The benefit here is that you don't have to convert the data into char.

There's a conversation about this here:  http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=127655 and at the bottom there's a link to an Excel version.

LVBarnes
0
 

Author Comment

by:MECR123
ID: 39689522
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
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39692281
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'
0
 

Author Comment

by:MECR123
ID: 39698107
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
0
 
LVL 5

Expert Comment

by:Lawrence Barnes
ID: 39702488
I will look at this tonight...sorry about the delay.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question