unload escape character for delimiter
Question: when UNLOADing a table, how are pipe characters inside column data handled? Answer: the backslash is the escape character — UNLOAD (from 4GL, dbaccess/isql, etc.) automatically prefixes embedded delimiters with a backslash, and literal backslashes are themselves doubled, so data round-trips correctly when reloaded with Informix tools (though other DBMSs may not understand the escapes). Alternatives offered: change the delimiter via DBDELIMITER or the UNLOAD DELIMITER clause. Jonathan Leffler pointed to SQLCMD's unload.format file for full details (escaped newlines, \\xAB hex, backslash-space for empty strings) and noted SQLCMD lets you choose the escape and delimiter.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi, i don't have an engine @ hand right now, so i cannot test it... what is the escape character for | when unloading a table (assuming a field contain some pipes...)? thanks... Jean Georges Perrin (aka jgp) IIUG (International Informix Users Group) - Board of Directors http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org -- GO FURTHER... with DB2 and Informix. Plan on attending the IDUG 2006 North America Conference. Tampa, Florida, USA. May 7-11, 2006. Visit http://www.iiug.org/conf for more information!
It should be backslashed. Testing..... Yes: This contains a pipe: \\\\| char.| Art ----- Original Message ----- From: Jean George.... <ids@iiug.org> At: 2/ 8 16:09 hi, i don't have an engine @ hand right now, so i cannot test it... what is the escape character for | when unloading a table (assuming a field contain some pipes...)? thanks... Jean Georges Perrin (aka jgp) IIUG (International Informix Users Group) - Board of Directors http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org -- GO FURTHER... with DB2 and Informix. Plan on attending the IDUG 2006 North America Conference. Tampa, Florida, USA. May 7-11, 2006. Visit http://www.iiug.org/conf for more information! ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
thanks Art - I really appreciate such a fast answer > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of ART KAGEL, .... > Sent: Wednesday, February 08, 2006 22:19 > To: ids@iiug.org > Subject: Re: unload escape character for delimiter [6328] > > > It should be backslashed. Testing..... Yes: > > This contains a pipe: \\\\| char.| > > Art > ----- Original Message ----- > From: Jean George.... <ids@iiug.org> > At: 2/ 8 16:09 > > hi, > > i don't have an engine @ hand right now, so i cannot test it... > > what is the escape character for | when unloading a table > (assuming a field contain some pipes...)? > > thanks... > > Jean Georges Perrin (aka jgp) > IIUG (International Informix Users Group) - Board of > Directors http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > -- > GO FURTHER... with DB2 and Informix. > Plan on attending the IDUG 2006 North America Conference. > Tampa, Florida, USA. May 7-11, 2006. > Visit http://www.iiug.org/conf for more information! > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Hi Jean-Georges: - if you are using 4gl, the 4gl UNLOAD stmt should automatically inserts a \\\\ escape character before any | delimiter in VARCHAR, CHAR or TEXT column data being unloaded. - if you are using DBAccess or isql, you can set the DBDELIMITER Environment Variable to some char other than "|" to be the data filed delimiter in the unload. - or you could use the DELIMITER clause of the UNLOAD stmt to specify a different field delimiter Regards, Jim Cramer > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Jean George.... > Sent: Wednesday, February 08, 2006 3:09 PM > To: ids@iiug.org > Subject: unload escape character for delimiter [6327] > > > hi, > > i don't have an engine @ hand right now, so i cannot test it... > > what is the escape character for | when unloading a table > (assuming a field contain some pipes...)? > > thanks... > > Jean Georges Perrin (aka jgp) > IIUG (International Informix Users Group) - Board of > Directors http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > -- > GO FURTHER... with DB2 and Informix. > Plan on attending the IDUG 2006 North America Conference. > Tampa, Florida, USA. May 7-11, 2006. > Visit http://www.iiug.org/conf for more information! > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Thanks Jim & Art... the next "logical" question is what about having "\\\\|" in a field? what about "\\\\" then? I know... I am annoying now... > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Jim A Cramer > Sent: Wednesday, February 08, 2006 22:31 > To: ids@iiug.org > Subject: RE: unload escape character for delimiter [6331] > > > Hi Jean-Georges: > > - if you are using 4gl, the 4gl UNLOAD stmt should > automatically inserts a \\\\ escape character before any | > delimiter in VARCHAR, CHAR or TEXT column data being unloaded. > > - if you are using DBAccess or isql, you can set the > DBDELIMITER Environment Variable to some char other than "|" > to be the data filed delimiter in the unload. > > - or you could use the DELIMITER clause of the UNLOAD stmt to > specify a different field delimiter > > Regards, > Jim Cramer > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of > > Jean George.... > > Sent: Wednesday, February 08, 2006 3:09 PM > > To: ids@iiug.org > > Subject: unload escape character for delimiter [6327] > > > > > > hi, > > > > i don't have an engine @ hand right now, so i cannot test it... > > > > what is the escape character for | when unloading a table > (assuming a > > field contain some pipes...)? > > > > thanks... > > > > Jean Georges Perrin (aka jgp) > > IIUG (International Informix Users Group) - Board of Directors > > http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > > -- > > GO FURTHER... with DB2 and Informix. > > Plan on attending the IDUG 2006 North America Conference. > > Tampa, Florida, USA. May 7-11, 2006. > > Visit http://www.iiug.org/conf for more information! > > > > > > ************************************************************** > > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Jean Georges, I think that if you just set the $DBDELIMITER variable in the environment of what you are running to some character other than "|", then "\\\\|" in the data will probably end up exported just as "|" because the "\\\\" will escape the | and treat it literally. But if you want the "\\\\" treated literally and not as an escape, then you somehow have to try to change what character is used as the escape char. Will check and get back soon on this, Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Jean George.... > Sent: Friday, February 10, 2006 2:34 AM > To: ids@iiug.org > Subject: RE: unload escape character for delimiter [6351] > > > Thanks Jim & Art... > > the next "logical" question is what about having "\\\\|" in a > field? what about "\\\\" then? I know... I am annoying now... > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of > > Jim A Cramer > > Sent: Wednesday, February 08, 2006 22:31 > > To: ids@iiug.org > > Subject: RE: unload escape character for delimiter [6331] > > > > > > Hi Jean-Georges: > > > > - if you are using 4gl, the 4gl UNLOAD stmt should automatically > > inserts a \\\\ escape character before any | delimiter in > VARCHAR, CHAR > > or TEXT column data being unloaded. > > > > - if you are using DBAccess or isql, you can set the DBDELIMITER > > Environment Variable to some char other than "|" > > to be the data filed delimiter in the unload. > > > > - or you could use the DELIMITER clause of the UNLOAD stmt > to specify > > a different field delimiter > > > > Regards, > > Jim Cramer > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > > Behalf Of > > > Jean George.... > > > Sent: Wednesday, February 08, 2006 3:09 PM > > > To: ids@iiug.org > > > Subject: unload escape character for delimiter [6327] > > > > > > > > > hi, > > > > > > i don't have an engine @ hand right now, so i cannot test it... > > > > > > what is the escape character for | when unloading a table > > (assuming a > > > field contain some pipes...)? > > > > > > thanks... > > > > > > Jean Georges Perrin (aka jgp) > > > IIUG (International Informix Users Group) - Board of Directors > > > http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > > > -- > > > GO FURTHER... with DB2 and Informix. > > > Plan on attending the IDUG 2006 North America Conference. > > > Tampa, Florida, USA. May 7-11, 2006. > > > Visit http://www.iiug.org/conf for more information! > > > > > > > > > ************************************************************** > > > ***************** > > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > > > > > > > > > > > ************************************************************** > > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >
Jean Georges, About your question of having just '\\\\' as data that is being unloaded, the Informix UNLOAD or other Informix tool will precede any backslash characters by another backslash character, which will be used to treat it as literal data when you reload. So, in the unload file 1 backslash will appear as 2, 2 consecutive ones will appear as 4 in a row, and so forth. These extra escape backslashes get removed when you use an Informix tool to reload and you are left with what you are started with originally. However, if you are importing the unload into some other venders data base, then it may not be the case that that DBMS understands those backslash escape characters. Jim > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Jim Cramer > Sent: Friday, February 10, 2006 9:06 AM > To: ids@iiug.org > Subject: RE: unload escape character for delimiter [6352] > > > Jean Georges, > > I think that if you just set the $DBDELIMITER variable in the > environment of what you are running to some character other > than "|", then "\\\\|" in the data will probably end up exported > just as "|" because the "\\\\" will escape the | and treat it literally. > > But if you want the "\\\\" treated literally and not as an > escape, then you somehow have to try to change what character > is used as the escape char. > > Will check and get back soon on this Jim > > Jim > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of > > Jean George.... > > Sent: Friday, February 10, 2006 2:34 AM > > To: ids@iiug.org > > Subject: RE: unload escape character for delimiter [6351] > > > > > > Thanks Jim & Art... > > > > the next "logical" question is what about having "\\\\|" in a > field? what > > about "\\\\" then? I know... I am annoying now... > > > > > -----Original Message----- > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > > Behalf Of > > > Jim A Cramer > > > Sent: Wednesday, February 08, 2006 22:31 > > > To: ids@iiug.org > > > Subject: RE: unload escape character for delimiter [6331] > > > > > > > > > Hi Jean-Georges: > > > > > > - if you are using 4gl, the 4gl UNLOAD stmt should automatically > > > inserts a \\\\ escape character before any | delimiter in > > VARCHAR, CHAR > > > or TEXT column data being unloaded. > > > > > > - if you are using DBAccess or isql, you can set the DBDELIMITER > > > Environment Variable to some char other than "|" > > > to be the data filed delimiter in the unload. > > > > > > - or you could use the DELIMITER clause of the UNLOAD stmt > > to specify > > > a different field delimiter > > > > > > Regards, > > > Jim Cramer > > > > > > > -----Original Message----- > > > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > > > Behalf Of > > > > Jean George.... > > > > Sent: Wednesday, February 08, 2006 3:09 PM > > > > To: ids@iiug.org > > > > Subject: unload escape character for delimiter [6327] > > > > > > > > > > > > hi, > > > > > > > > i don't have an engine @ hand right now, so i cannot test it... > > > > > > > > what is the escape character for | when unloading a table > > > (assuming a > > > > field contain some pipes...)? > > > > > > > > thanks... > > > > > > > > Jean Georges Perrin (aka jgp) > > > > IIUG (International Informix Users Group) - Board of Directors > > > > http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > > > > -- > > > > GO FURTHER... with DB2 and Informix. > > > > Plan on attending the IDUG 2006 North America Conference. > > > > Tampa, Florida, USA. May 7-11, 2006. > > > > Visit http://www.iiug.org/conf for more information! > > > > > > > > > > > > ************************************************************** > > > > ***************** > > > > Forum Note: Use "Reply" to post a response in the > > discussion forum. > > > > > > > > > > > > > > > > > ************************************************************** > > > ***************** > > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > > > > > > > > > > > ************************************************************** > > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > >
On 2/10/06, Jean George.... <jgp@iiug.org> wrote:
>>> [...questions and answers about UNLOAD format...]
> the next "logical" question is what about having "\\\\|" in a field? what about
> "\\\\" then? I know... I am annoying now...
I suggest downloading the source for SQLCMD and reading the file
unload.format in there. It documents the details of the UNLOAD
format.
The short answer - any backslashes are escaped with a backslash. Any
newlines that are part of the data for a field are escaped with a
backslash, to separate them from the end of record. Under some
circumstances, \\\\xAB denotes a hexadecimal character (dbaccess -X), and
backslash-space as the sole contents of a field denotes a non-null
empty string (which is significant for VARCHAR types).
SQLCMD allows you to select the escape and the delimiter (and the
quote character if you're dealing with CSV format).
You might care to observe that DB2's DEL format does not allow you to
load data that contains the delimiter.
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/
Both a backslash would be quoted with another backslash, so the "\\\\|" would be rendered as"\\\\\\\\\\\\|" in the unload file. Art ----- Original Message ----- From: Jean George.... <ids@iiug.org> At: 2/10 3:34 Thanks Jim & Art... the next "logical" question is what about having "\\\\|" in a field? what about "\\\\" then? I know... I am annoying now... > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of Jim A Cramer > Sent: Wednesday, February 08, 2006 22:31 > To: ids@iiug.org > Subject: RE: unload escape character for delimiter [6331] > > > Hi Jean-Georges: > > - if you are using 4gl, the 4gl UNLOAD stmt should > automatically inserts a \\\\ escape character before any | > delimiter in VARCHAR, CHAR or TEXT column data being unloaded. > > - if you are using DBAccess or isql, you can set the > DBDELIMITER Environment Variable to some char other than "|" > to be the data filed delimiter in the unload. > > - or you could use the DELIMITER clause of the UNLOAD stmt to > specify a different field delimiter > > Regards, > Jim Cramer > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On > Behalf Of > > Jean George.... > > Sent: Wednesday, February 08, 2006 3:09 PM > > To: ids@iiug.org > > Subject: unload escape character for delimiter [6327] > > > > > > hi, > > > > i don't have an engine @ hand right now, so i cannot test it... > > > > what is the escape character for | when unloading a table > (assuming a > > field contain some pipes...)? > > > > thanks... > > > > Jean Georges Perrin (aka jgp) > > IIUG (International Informix Users Group) - Board of Directors > > http://www.iiug.org <http://www.iiug.org/>; jgp@iiug.org > > -- > > GO FURTHER... with DB2 and Informix. > > Plan on attending the IDUG 2006 North America Conference. > > Tampa, Florida, USA. May 7-11, 2006. > > Visit http://www.iiug.org/conf for more information! > > > > > > ************************************************************** > > ***************** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > ************************************************************** > ***************** > Forum Note: Use "Reply" to post a response in the discussion forum. > > ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.