how to create a tab delimeted file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

how to create a tab delimeted file

Author

Topic

wish24bone
Starting Member

2 Posts

Posted - 2012-03-15 : 11:57:23

Help please, I need to create a tab delimited file using sqlserver.. I don't know anything about SSIS or how to use it, so I'm just trying to write this query so that it generates a tab delimited file. I've tried the +char(9)+ but it fails because of the column aliases, if I remove those it doesn't like the = in the case section of the select..

select b.status "BillStatus"
,w.status "wfqstatus"
, b.claimsyssubset, c.claimid
, b.DocCtrlID, b.clientcode
, b.billseq, b.DosFirst,b.DosLast, b.RcvdDate "CarrierRcvdDate", b.createdate, b.reviewdate
,b.postdate, b.pmtexportdate, b.pmtacceptdate, wf.name "WFQ NAME", convert(varchar(50),w.taskmessage) "w.taskmessage"
, bc.control "PaymentExportStatus", bc.ModDate "PmtExportModDate", bc1.control "StateExportStatus", bc1.ModDate "StateExportModDate"
,b.PPONetworkID
, 'PPO BRIDGE IN PROGRESS' =
case
when w1.parameter1 is not NULL and w1.status = 'L' and w1.wftaskseq = 11 then w1.Parameter1
end
, 'Days Since Exported to Bridge' = datediff(day, w1.moddate, getdate())
, 'DateExportedtoPPO' = w1.ModDate
, b.SourceID
, b.createuserid, b.moduserid
from v40n02prod.dbo.bill b (nolock)
left join v40n02prod.dbo.claim c (nolock) on b.claimsyssubset = c.claimsyssubset and b.claimseq = c.claimseq
left join v40n02prod.dbo.wfqueue w (nolock) on b.clientcode = w.entitysubset and b.billseq = w.entityseq
left join v40n02prod.dbo.wftask wf (nolock) on w.wftaskseq = wf.wftaskseq
LEFT JOIN v40n02prod.dbo.billcontrol bc (nolock) on b.clientcode = bc.clientcode and b.billseq = bc.billseq and bc.billcontrolseq = 0
LEFT JOIN v40n02prod.dbo.billcontrol bc1 (nolock) on b.clientcode = bc1.clientcode and b.billseq = bc1.billseq and bc1.billcontrolseq = 4
LEFT join v40n02prod.dbo.wfqueue w1 (nolock) on b.clientcode = w1.entitysubset and b.billseq = w1.entityseq and w1.wftaskseq = 11 and w1.status = 'L'
where b.billseq < 900000000

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-15 : 12:31:30

Do you know what bcp and xp_cmdshell are?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-15 : 12:32:23

The shortest route perhaps is to use BCP to write the output file. This page has info and examples of using BCP: http://msdn.microsoft.com/en-us/library/ms162802.aspx

I would do the following:

1. Run the query from SQL server management studio to make sure that it runs and gives the right data.

2. Create a stored procedure using this query. All you need to do is add the following code at the beginning of your existing query and execute in a SSMS window:


CREATE PROCEDURE dbo.MyStoredProc
AS

3. From a command window, run the following:

bcp "exec MyDatabaseName.dbo.MyStoredProc" queryout MyData.txt -T -c -S MyServerName

This assumes that you have client tools installed on your computer, and that you have Windows-authentication access to the server. If not look at the web page above to see what changes you need to make.

wish24bone
Starting Member

2 Posts

Posted - 2012-03-15 : 13:51:45

@sunitabeck

That's exactly what I have, this worked great, do you know if it's possilbe to have the output display the column names?
Thank you so much

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-15 : 14:07:58

Unfortunately I don't know of a way to include column names in BCP output. There are workarounds that you will find if you google for it - for example here: http://connect.microsoft.com/SQLServer/feedback/details/288800/bcp-out-to-include-field-columns-names

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources