Export pipe delimited data

[Breck_Carter]

I have been exporting data as a csv file using xp_cmdshell within a stored procedure successfully for some time now in Sybase ASA 12.

I wish to export similar data, but pipe delimited instead of comma separated values.

From within ISQL the following works pefectly:

select '0300' AS HospitalCode, TRIM(URNumber), OrderType, SentenceType, OriginalDateStart,VariationNo, SentenceMonths,DateStart, DateExpiry, CLCAct, LifeIndicator, GPIndicator,TRIM(fnCurrentLocation(CMHO.URNumber)) as CurrentLocation, W.Ward from ClientMHAOrders as CMHO left outer JOIN Wards as W on W.WardName = CurrentLocation WHERE OrderType = 'FP' ORDER BY CMHO.URNumber, CMHO.DateStart; OUTPUT TO 'c:\\\\temp\\\\GLN_FORENSIC_20160220.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES; 2> 'C:\\\\temp\\\\ForensicData.log','no_output'

Now when I put the code into a stored procedure as this:

call xp_cmdshell('""%SQLANY12%\\\\Bin32\\\\dbisql" -q -c "UID=DBA;PWD=sql;Server=MyServer;Database=TestData" SELECT ''0300'' AS HospitalCode, TRIM(URNumber), OrderType, SentenceType, OriginalDateStart, VariationNo, SentenceMonths, DateStart, DateExpiry, CLCAct, LifeIndicator, GPIndicator, TRIM(fnCurrentLocation(CMHO.URNumber)) as CurrentLocation, W.Ward from ClientMHAOrders as CMHO left outer JOIN Wards as W on W.WardName = CurrentLocation WHERE OrderType = ''FP'' ORDER BY CMHO.URNumber, CMHO.DateStart; OUTPUT TO ''c:\\\\temp\\\\GLN_FORENSIC_20160220.txt'' FORMAT TEXT DELIMITED BY ''|'' WITH COLUMN NAMES; 2> C:\\\\temp\\\\ForensicData.log"','no_output')

My stored procedure creates an error entry within the log file: ''' is not recognized as an internal or external command, operable program or batch file.

If I remove the "DELIMITED BY ''|'' it works, but the output is comma delimited. Obviously I have an error in syntax possibly, can anyone help with this please.

[Breck_Carter]

Try representing the pipe character as an escaped hex character '\\xZZ' where ZZ is the hex for pipe.

[Former Member]

HI Breck.

No that didn't work, now I do get the file created, but \\xZZ becomes the delimiter, not the hex equivalent. Oh isn't 7C the hex equivalant of the vertical bar "pipe" ?. I tried that too. Interestingly if I use the "^" character it works, just not "|".

[Former Member]

I have tried this too which works for the HEx character broken pipe,

FORMAT TEXT DELIMITED BY ''\\xA6'' ESCAPES ON WITH .....

Each time I try to represent the pipe character ''\\x7C'' I get the same error in the log file.

[VolkerBarth]

Have you tried to use the UNLOAD statement instead of the "xp_cmdshell/OUTPUT TO" approach? - AFAIK, you would have to use an initial UNLOAD to add the column headers and then use UNLOAD ... APPEND or use an UNION to add column headers. (v17 has added a helpful WITH COLUMNS NAMES clause...)

Note, I have not tried to use a pipe character with UNLOAD but I guess the syntax is easier to use from within a stored procedure than your current approach.

[Former Member]

Hi Volker,

The UNLOAD statement worked. Thanks. For information it would be interesting to know why the pipe character is the only character that doesn't work in the OUTPUT statement.

[VolkerBarth]

See my other answer: It's no OUTPUT or dbisql problem, it's just the fact that you use the unmasked pipe in a command line where it has a particular meaning (i.e. to create a pipeline between two programs) and has to be escaped here if that meaning is not desired.