Question Re: Export pipe delimited data in Technology Q&A

Export pipe delimited data

Former Member — Fri, 19 Feb 2016 22:32:07 GMT

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.

Re: Export pipe delimited data

Breck_Carter — Sat, 20 Feb 2016 09:28:12 GMT

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

Re: Export pipe delimited data

Former Member — Sat, 20 Feb 2016 18:27:19 GMT

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 "|".

Re: Export pipe delimited data

Former Member — Sat, 20 Feb 2016 18:48:01 GMT

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.

Re: Export pipe delimited data

VolkerBarth — Sat, 20 Feb 2016 19:41:08 GMT

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.

Re: Export pipe delimited data

Former Member — Sat, 20 Feb 2016 23:17:23 GMT

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.

Re: Export pipe delimited data

VolkerBarth — Mon, 22 Feb 2016 03:40:23 GMT

While I highly recommend to use UNLOAD instead of a cmd shell and a DBISQL session to output data from a stored procedure, I guess the following should do the trick:

In your original statement the '|' seems to be interpreted as a command line operator, whether it is used within quotes or not. (Note: That's my humble impression, I'm not command line expert at all...). Here's an attempt for a similar query run against the v12 demo database, directly on the command line (and therefore without doubled quotes and the like...)

"%SQLANY12%\\Bin64\\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" select * from sysusertype order by type_name; OUTPUT TO 'c:\\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES; 2> C:\\PipeDelim
itedExportError.log

This raises the same error you mentioned.

However, if you put the whole SQL statement within a double quote, the export works just fine in my tests with 12.0.1.4314 - and I would think it is because that way the command shell does not try to interpret the pipe character itself:

"%SQLANY12%\\Bin64\\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" "select * from sysusertype order by type_name; OUTPUT TO 'c:\\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '|' WITH COLUMN NAMES;" 2> C:\\PipeDelim
itedExportError.log

Or, alternatively, you can escape the pipe character on the command shell with a leading caret:

"%SQLANY12%\\Bin64\\dbisql" -q -c "UID=DBA;PWD=sql;DSN=SQL Anywhere
 12 Demo" select * from sysusertype order by type_name; OUTPUT TO 'c:\\PipeDelim
itedExport.txt' FORMAT TEXT DELIMITED BY '^|' WITH COLUMN NAMES; 2> C:\\PipeDelim
itedExportError.log

Re: Export pipe delimited data

VolkerBarth — Mon, 22 Feb 2016 03:49:02 GMT

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.

Re: Export pipe delimited data

Former Member — Mon, 22 Feb 2016 16:58:38 GMT

Hi Volker,

That was actually my thinking too, that some how the command was being misinterpreted.

Thanks for your help.