?
SolvedPrivate

SQL Pipe Delimited File

Posted on 2014-04-30
3
Medium Priority
?
66 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 2000 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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

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

MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

592 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