SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 export data to pipe delimited text file
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

laddu
Constraint Violating Yak Guru

USA
331 Posts

Posted - 05/18/2011 :  12:25:57  Show Profile  Reply with Quote
Hi,

I am trying to export the sql script data to pipe delimited text file.

I did below steps

1. JOined the sql tables and wrote the sql script.
2. export the results to global temp table.
3. then BCP the results to pipe delimited text file.

Here is my select query
Select dbo.FN_Decrypt(@master, Login.LoginSkey,PatientDisplayID) as 'Client ID', ISNULL(SugarEntryCount,0) as 'BG Count',
ISNULL(CarbsEntryCount,0) as 'Carb Count', ISNULL(MedsEntryCount,0) as 'Med Count'

INTO ##PIPE_REPORT
FROM #temp_patients inner join Login ON Login.LoginID = #temp_patients.PatientID

EXECUTE master.dbo.xp_cmdshell 'bcp "SELECT * FROM ##PIPE_REPORT " queryout C:\Data\Objects.txt -t"|" -c -T '

DROP TABLE #temp_patients


I have successfully exported data into pipe delimited text file. but only data not the columns names

I need columns names in the first row, please let me know how can i do this?



Thank you

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 05/18/2011 :  12:29:42  Show Profile  Visit robvolk's Homepage  Reply with Quote
You can get column headings using sqlcmd, but you also get a separator line that you can't suppress, you'd have to remove it later. The syntax would be:

EXECUTE master.dbo.xp_cmdshell 'sqlcmd -S. -E -Q"SELECT * FROM ##PIPE_REPORT" -oC:\Data\Objects.txt -s"|" -W'
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/18/2011 :  12:41:12  Show Profile  Reply with Quote
How about



CREATE TABLE ##PIPE_REPORT (
			  [Client ID]	varchar(50)
			, [BG Count]	varchar(50)
			, [Carb Count]	varchar(50)
			, [Med Count]	varchar(50)
)


INSERT INTO ##PIPE_REPORT (
			  [Client ID]
			, [BG Count]
			, [Carb Count]
			, [Med Count]
)
SELECT 		  [Client ID]
			, [BG Count]
			, [Carb Count]
			, [Med Count]
  FROM (
	SELECT	  'Client ID'		AS [Client ID]
			, 'BG Count'		AS [BG Count]
			, 'Carb Count'		AS [Carb Count]
			, 'Med Count'		AS [Med Count]
			, 0					AS Row_Order
 UNION ALL
	SELECT	  CONVERT(varchar(50),dbo.FN_Decrypt(@master, Login.LoginSkey,PatientDisplayID))	
								AS [Client ID]
			, CONVERT(varchar(50),COALESCE(SugarEntryCount,0))									
								AS [BG Count]
			, CONVERT(varchar(50),COALESCE((CarbsEntryCount,0))									
								AS [Carb Count]
			, CONVERT(varchar(50),COALESCE((MedsEntryCount,0))									
								AS [Med Count]
			, 1					AS Row_Order
	  FROM   #temp_patients 
INNER JOIN	 [Login] 
		ON   [Login].[LoginID] = #temp_patients.PatientID
) AS XXX
ORDER BY Row_Order



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/



Edited by - X002548 on 05/18/2011 12:44:55
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000