Export pipe delimited data

[Former Member]

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]

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

[Former Member]

Hi Volker,

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

Thanks for your help.

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