Oninit cdi / Archive
Talk to an expert →

delimiter

Posted in 2013

Topics: Migration, Import/Export & Data Conversion

Peter_Logan@spartanstores.com — — source: IIUG Forums & Mailing Lists

IDS 11.70.fc3xc aix 6.1 I know a question very similar was asked about this the other day .. but I don't recall seeing the answer .. Here is my situation .. using external tables to unload a bunch of data via a pipe to the 'nzload' utility of Netezza. The issue is that I have the delimiter set to '|' .. yet I got a '|' in some of the data. Can't update it because it's from another system and valid. So, trying to get the data out of informix and over to Netezza ... Is there a way to somehow escape the '|' in the data ... Netezza can handle that . Our development team is attempting to use the external tables via pipes .. without haveing to do stuff in the middle .. obviously we could fix it at the os level .. Thanks .... Peter Logan Senior Database Administrator Phone: 616/878-8309

↪ replying to Peter_Logan@spartanstores.com
Fernando Nunes — — source: IIUG Forums & Mailing Lists

There is a bug in the documentation... Please check this: cheetah@pacman.onlinedomus.net:fnunes-> cat test.sql;echo "########################";dbaccess stores test.sql DROP TABLE test_unl; DROP TABLE test_unl_ext; CREATE TABLE test_unl ( col1 INTEGER, col2 CHAR(10) ); INSERT INTO test_unl VALUES (1,'|'); INSERT INTO test_unl VALUES (2,'a|'); INSERT INTO test_unl VALUES (3,'|a'); SELECT * FROM test_unl;CREATE EXTERNAL TABLE test_unl_ext SAMEAS test_unl USING ( DATAFILES ("DISK:/tmp/test_unl.unl"), FORMAT 'DELIMITED', ESCAPE ); INSERT INTO test_unl_ext SELECT * FROM test_unl;TRUNCATE TABLE test_unl; INSERT INTO test_unl SELECT * FROM test_unl_ext;!echo "######################################" !cat /tmp/test_unl.unl !echo "######################################" SELECT * FROM test_unl;######################## Database selected. Table dropped. Table dropped. Table created. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. col1 col2 1 | 2 a| 3 |a 3 row(s) retrieved. Table created. 3 row(s) inserted. Table truncated. 3 row(s) inserted. ###################################### 1|\\\\|| 2|a\\\\|| 3|\\\\|a| ###################################### col1 col2 1 | 2 a| 3 |a 3 row(s) retrieved. Database closed. Regards On Thu, Sep 5, 2013 at 5:01 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.fc3xc > aix 6.1 > > I know a question very similar was asked about this the other day .. but I > don't recall seeing the answer .. Here is my situation .. using external > tables to unload a bunch of data via a pipe to the 'nzload' utility of > Netezza. The issue is that I have the delimiter set to '|' .. yet I got a > '|' in some of the data. Can't update it because it's from another system > and valid. So, trying to get the data out of informix and over to Netezza > .... Is there a way to somehow escape the '|' in the data ... Netezza can > handle that . Our development team is attempting to use the external > tables via pipes .. without haveing to do stuff in the middle .. obviously > we could fix it at the os level .. > > Thanks .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b6d7aaed1b0bb04e5a6c09d

↪ replying to Peter_Logan@spartanstores.com
Fernando Nunes — — source: IIUG Forums & Mailing Lists

The documentation is only fixed in 12.10. The ESCAPE ON|OFF default was changed. In 12.1+ it's "ON". In versions before 12.10 it's OFF. This is (was) messy. And now that you mention it, I think I was asked before by a customer, but I'd say both of us forgot the issue :/ Thanks! On Thu, Sep 5, 2013 at 7:04 PM, Fernando Nunes <domusonline@gmail.com>wrote: > There is a bug in the documentation... Please check this: > > cheetah@pacman.onlinedomus.net:fnunes-> cat test.sql;echo > "########################";dbaccess stores test.sql > DROP TABLE test_unl; > DROP TABLE test_unl_ext; > CREATE TABLE test_unl > ( > col1 INTEGER, > col2 CHAR(10) > );> > INSERT INTO test_unl VALUES (1,'|'); > INSERT INTO test_unl VALUES (2,'a|'); > INSERT INTO test_unl VALUES (3,'|a');> > SELECT * FROM test_unl;> CREATE EXTERNAL TABLE test_unl_ext SAMEAS test_unl > USING ( DATAFILES ("DISK:/tmp/test_unl.unl"), > FORMAT 'DELIMITED', > ESCAPE > ); > INSERT INTO test_unl_ext SELECT * FROM test_unl;> TRUNCATE TABLE test_unl; > INSERT INTO test_unl SELECT * FROM test_unl_ext;> !echo "######################################" > !cat /tmp/test_unl.unl > !echo "######################################" > > SELECT * FROM test_unl;> ######################## > > Database selected. > > > Table dropped. > > > Table dropped. > > > Table created. > > > 1 row(s) inserted. > > > 1 row(s) inserted. > > > 1 row(s) inserted. > > > > col1 col2 > > 1 | > 2 a| > 3 |a > > 3 row(s) retrieved. > > > Table created. > > > 3 row(s) inserted. > > > Table truncated. > > > 3 row(s) inserted. > > ###################################### > 1|\\\\|| > 2|a\\\\|| > 3|\\\\|a| > ###################################### > > > col1 col2 > > 1 | > 2 a| > 3 |a > > 3 row(s) retrieved. > > > Database closed. > > Regards > > > > On Thu, Sep 5, 2013 at 5:01 PM, Peter_Logan@spartanstores.com < > Peter_Logan@spartanstores.com> wrote: > >> IDS 11.70.fc3xc >> aix 6.1 >> >> I know a question very similar was asked about this the other day .. but I >> don't recall seeing the answer .. Here is my situation .. using external >> tables to unload a bunch of data via a pipe to the 'nzload' utility of >> Netezza. The issue is that I have the delimiter set to '|' .. yet I got a >> '|' in some of the data. Can't update it because it's from another system >> and valid. So, trying to get the data out of informix and over to Netezza >> .... Is there a way to somehow escape the '|' in the data ... Netezza can >> handle that . Our development team is attempting to use the external >> tables via pipes .. without haveing to do stuff in the middle .. obviously >> we could fix it at the os level .. >> >> Thanks .... >> >> Peter Logan >> Senior Database Administrator >> Phone: 616/878-8309 >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b624cbee4843604e5a6d08f

↪ replying to Fernando Nunes
Peter_Logan@spartanstores.com — — source: IIUG Forums & Mailing Lists

Perfect ... thank you .... Peter Logan Senior Database Administrator Phone: 616/878-8309 From: "Fernando Nunes" <domusonline@gmail.com> To: ids@iiug.org, Date: 09/05/2013 02:06 PM Subject: Re: delimiter [31370] Sent by: ids-bounces@iiug.org There is a bug in the documentation... Please check this: cheetah@pacman.onlinedomus.net:fnunes-> cat test.sql;echo "########################";dbaccess stores test.sql DROP TABLE test_unl; DROP TABLE test_unl_ext; CREATE TABLE test_unl ( col1 INTEGER, col2 CHAR(10) ); INSERT INTO test_unl VALUES (1,'|'); INSERT INTO test_unl VALUES (2,'a|'); INSERT INTO test_unl VALUES (3,'|a'); SELECT * FROM test_unl;CREATE EXTERNAL TABLE test_unl_ext SAMEAS test_unl USING ( DATAFILES ("DISK:/tmp/test_unl.unl"), FORMAT 'DELIMITED', ESCAPE ); INSERT INTO test_unl_ext SELECT * FROM test_unl;TRUNCATE TABLE test_unl; INSERT INTO test_unl SELECT * FROM test_unl_ext;!echo "######################################" !cat /tmp/test_unl.unl !echo "######################################" SELECT * FROM test_unl;######################## Database selected. Table dropped. Table dropped. Table created. 1 row(s) inserted. 1 row(s) inserted. 1 row(s) inserted. col1 col2 1 | 2 a| 3 |a 3 row(s) retrieved. Table created. 3 row(s) inserted. Table truncated. 3 row(s) inserted. ###################################### 1|\\\\|| 2|a\\\\|| 3|\\\\|a| ###################################### col1 col2 1 | 2 a| 3 |a 3 row(s) retrieved. Database closed. Regards On Thu, Sep 5, 2013 at 5:01 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.fc3xc > aix 6.1 > > I know a question very similar was asked about this the other day .. but I > don't recall seeing the answer .. Here is my situation .. using external > tables to unload a bunch of data via a pipe to the 'nzload' utility of > Netezza. The issue is that I have the delimiter set to '|' .. yet I got a > '|' in some of the data. Can't update it because it's from another system > and valid. So, trying to get the data out of informix and over to Netezza > .... Is there a way to somehow escape the '|' in the data ... Netezza can > handle that . Our development team is attempting to use the external > tables via pipes .. without haveing to do stuff in the middle .. obviously > we could fix it at the os level .. > > Thanks .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b6d7aaed1b0bb04e5a6c09d ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Peter_Logan@spartanstores.com
Art Kagel — — source: IIUG Forums & Mailing Lists

In v12.10 you would just add the "ESCAPE ON" table option to the USING() clause in the external table definition. In 11.70 and earlier, "ESCAPE" takes an argument of a character to use as the escape character, so try "ESCAPE '\\\\'" Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Sep 5, 2013 at 12:01 PM, Peter_Logan@spartanstores.com < Peter_Logan@spartanstores.com> wrote: > IDS 11.70.fc3xc > aix 6.1 > > I know a question very similar was asked about this the other day .. but I > don't recall seeing the answer .. Here is my situation .. using external > tables to unload a bunch of data via a pipe to the 'nzload' utility of > Netezza. The issue is that I have the delimiter set to '|' .. yet I got a > '|' in some of the data. Can't update it because it's from another system > and valid. So, trying to get the data out of informix and over to Netezza > .... Is there a way to somehow escape the '|' in the data ... Netezza can > handle that . Our development team is attempting to use the external > tables via pipes .. without haveing to do stuff in the middle .. obviously > we could fix it at the os level .. > > Thanks .... > > Peter Logan > Senior Database Administrator > Phone: 616/878-8309 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113366bcc5af3104e5d50fb8

Glossary