SolvedPrivate

SQL Pipe Delimited File

Posted on 2014-04-30
3
57 Views
Last Modified: 2016-02-10
I need to get the following results into a Pipe Delimited file format.  I the 'HEADER' columns to be the start of a new row followed by each column pipe delimited even if the column is blank.  

Here are the results:

Authorization	AUN	APN	CAN	AUB	MACHINE_HEADER	MSN	MODEL_HEADER	ESN	ENM	DETAIL_HEADER	FAD	RED	EHU	CAO	FAILURE_HEADER	PPF	FC1	FC2	JOC	MIN	HOU	CSN	RPB	NOTES_HEADER	PRC	WOP	PARTS_HEADER	PAN	QTY	TON	WARRANTY_END_HEADER
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-09 07:30:00.000	NULL	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-09 13:00:00.000	1099	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>
<AUTHORIZATION>	Authorization Number	||	||	||	<MACHINE>	5912	<MODEL>	||	||	<DETAILS>	2014-04-04 10:07:24.000	2014-04-24 09:15:00.000	1123	||	<FAILURE>	Primary Part of Failure	Failure Code	||	Job Code	Labor Minutes	Labor Hours	||	||	<NOTES>	Service Bunker rake....	Work Performed	<PARTS>	Part Number	Quantity	||	<WARRANTY END>

Open in new window


Here is the query:
DECLARE @casenumber VARCHAR(5)
SET @casenumber='14059'
SELECT  [AUTHORIZATION_HEADER]='<AUTHORIZATION>',
		[AUN]='Authorization Number',
		[APN]='||',
		[CAN]='||',
		[AUB]='||',
		[MACHINE_HEADER]='<MACHINE>',
		[MSN]=b.tsp_serialnumber,
		[MODEL_HEADER]='<MODEL>',
		[ESN]='||',
		[ENM]='||',
		[DETAIL_HEADER]='<DETAILS>',
		[FAD]=a.createdon,
		[RED]=c.scheduledstart,
		[EHU]=c.tsp_hours,
		[CAO]='||',
		[FAILURE_HEADER]='<FAILURE>',
		[PPF]='Primary Part of Failure',
		[FC1]='Failure Code',
		[FC2]='||',
		[JOC]='Job Code',
		[MIN]='Labor Minutes',
		[HOU]='Labor Hours',
		[CSN]='||',
		[RPB]='||',
		[NOTES_HEADER]='<NOTES>',
		[PRC]=a.description,
		[WOP]='Work Performed',
		[PARTS_HEADER]='<PARTS>',
		[PAN]='Part Number',
		[QTY]='Quantity',
		[TON]='||',
		[WARRANTY_END_HEADER]='<WARRANTY END>'
		
		
        
FROM    FilteredIncident AS a
        LEFT OUTER JOIN FilteredTSP_WorkOrder AS b ON a.incidentid = b.tsp_casenumberid
        LEFT OUTER JOIN FilteredServiceAppointment AS c ON b.tsp_workorderid = c.regardingobjectid
WHERE   ( a.ticketnumber LIKE '%' + @casenumber + '%' )
ORDER BY c.scheduledstart		

Open in new window


Here is an example format:
<AUTHORIZATION>|DRN001|FAN001|xxxxx|Martyn
<MACHINE>|EA000500
<MODEL>|ESN001|ENM001
<DETAILS>|11-NOV-2007|12-NOV-2007|5|Lorry
<FAILURE>|MBX7024A|Electrical|X1|X2|2|15|CSN001|Owner
<NOTES>|It Broke|I fixed it
<PARTS>|MBA7024A|2|
<PARTS>|MCS12345|1|
<MISCELLANEOUS>|OIL|12.34
<WARRANTY END>

Open in new window

0
Comment
Question by:r270ba
[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
  • 2
3 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40033491
I would suggest you use SSIS or (if it's a one off task) SQL Server Import/Export utility and export the data into a flat file.  At the point of defining the Intended file, you can choose the delimiter (e.g. "|" instead of comma).
0
 

Author Comment

by:r270ba
ID: 40034518
ProjectChampion thanks.  Any direction on how to do that?
0
 
LVL 8

Accepted Solution

by:
ProjectChampion earned 500 total points
ID: 40034617
If it's not a one off task the I strongly suggest you use SSIS, otherwise in Management Studio right click on the database, select Tasks > Export Data...
In the dialog box that opens select next, in "Data Source" tab enter name of the server and DB, then go to the destination tab, select "Flat File Destination", chose a file name and location, choose the appropriate code page, and set "Format" to Delimited and if needed a text qualifier (I recommend double quote ") and move to the next page, select a table or query as appropriate and move to the next tab (if you selected query you need to write the actual query in the next tab then...) on the "Configure Flat File Destination" tab you have the option for Row/line delimiter and column delimiter where you can select one of the option or add  a custom delimiter of your choice... and from this point onward everything should be clear. If you wan you can save your package to not have to go through this again next time...

Let me know if I can be of further assistance.
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

623 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