SolvedPrivate

SQL Pipe Delimited File

Posted on 2014-04-30
3
49 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
  • 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

821 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