SolvedPrivate

SQL Pipe Delimited File

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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