problem about unload text data

I am using ids 9.4. when I try to unload text data to file, it showes a "backslash" in the end of row. For example, create table test ( rpt_id char(10),rpt text ); load from "/home/informix9/tmp/text" insert into test(rpt); update test set rpt_id='text' where rpt_is is null ; unload to "/home/informix9/tmp/text.rpt" DELIMITER "^A" select rpt from test where rpt_id='text' the result I got was #cat text hello test test #cat text.rpt hello test\\\\ test\\\\ How to avoid this backslash? thanks for your help in advance

take another Delimiter instead of^A, Unix shows it as an escaped sign. Mfg Joerg Volz Am 26.04.2012 um 10:15 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: > I am using ids 9.4. when I try to unload text data to file, it showes a > "backslash" in the end of row. > For example, > > create table test > ( > rpt_id char(10),> rpt text > ); > > load from "/home/informix9/tmp/text" > insert into test(rpt);> > update test > set rpt_id='text' > where rpt_is is null > ; > > unload to "/home/informix9/tmp/text.rpt" DELIMITER "^A" > select rpt from test > where rpt_id='text'> > the result I got was > #cat text > hello test > test > #cat text.rpt > hello test\\\\ > test\\\\ > > How to avoid this backslash? > > thanks for your help in advance > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > IT Handel und Beratung Jorg Volz Bernhard-Fruh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de

if i take another delimiter instead of "^A", the result is still the same. unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" select rpt from test where rpt_id='text' cat text.rpt hello test\\\\ test\\\\ ;

if i take another delimiter instead of "^A", the result is still the same. unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" select rpt from test where rpt_id='text' #cat text.rpt hello test\\\\ test\\\\ ;

can youdo an od -c instead of a cat? Mfg Joerg Volz Am 26.04.2012 um 10:45 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: > if i take another delimiter instead of "^A", the result is still the same. > > unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" > select rpt from test > where rpt_id='text'> > #cat text.rpt > hello test\\\\ > test\\\\ > ; > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > IT Handel und Beratung Jorg Volz Bernhard-Fruh-Str. 7 77855 Achern GERMANY Tel: +49 (0)7841-681651 Fax: +49 (0)7841-681654 Mobil: +49 (0)170-2989757 VAT-ID: DE201383541 http://www.it-volz.de

Change the delimiter to something printable. The backslash is quoting the delimiter (^A). 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, Apr 26, 2012 at 1:08 AM, FAN YANG <fania0324@yahoo.com.tw> wrote: > I am using ids 9.4. when I try to unload text data to file, it showes a > "backslash" in the end of row. > For example, > > create table test > ( > rpt_id char(10),> rpt text > ); > > load from "/home/informix9/tmp/text" > insert into test(rpt);> > update test > set rpt_id='text' > where rpt_is is null > ; > > unload to "/home/informix9/tmp/text.rpt" DELIMITER "^A" > select rpt from test > where rpt_id='text'> > the result I got was > #cat text > hello test > test > #cat text.rpt > hello test\\\\ > test\\\\ > > How to avoid this backslash? > > thanks for your help in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d59c1600104be94a148

Ahh, but now we can see that the backslash is actually quoting embedded newlines within your data. It had looked like we were seeing two rows when actually the engine was unloading a single row with two embedded newlines! When you imported you did not set the delimiter and the default delimiter is the pipe character (|) so the two newlines in the file were interpreted as embedded characters within a single column in a single row. 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, Apr 26, 2012 at 1:36 AM, FAN YANG <fania0324@yahoo.com.tw> wrote: > if i take another delimiter instead of "^A", the result is still the same. > > unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" > select rpt from test > where rpt_id='text'> > #cat text.rpt > hello test\\\\ > test\\\\ > ; > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae93403c320d91704be94ae44

the result from od -c: 0000000 h e l l o t e s t \\\\ \\ j i j i 0000020 j i \\\\ \\ ; \\ 0000026 >can youdo an od -c instead of a cat? >Mfg >Joerg Volz >Am 26.04.2012 um 10:45 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: >> if i take another delimiter instead of "^A", the result is still the same. >> >> unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" >> select rpt from test >> where rpt_id='text'>> >> #cat text.rpt >> hello test\\\\ >> test\\\\ >> ; >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >>

Like I said, embedded decline characters. Art On Apr 26, 2012 6:54 PM, "FAN YANG" <fania0324@yahoo.com.tw> wrote: > the result from od -c: > 0000000 h e l l o t e s t \\\\ \\ j i j i > 0000020 j i \\\\ \\ ; \\ > 0000026 > > >can youdo an od -c instead of a cat? > > >Mfg > >Joerg Volz > > >Am 26.04.2012 um 10:45 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: > > >> if i take another delimiter instead of "^A", the result is still the > same. > >> > >> unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" > >> select rpt from test > >> where rpt_id='text'> >> > >> #cat text.rpt > >> hello test\\\\ > >> test\\\\ > >> ; > >> > >> > >> > > ******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. > >> > >> > >> > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340661ae06cc04be9f691b

thanks for art's reply, but sorry i still don't know how to aviod this. could u help me with that? thanks so much >Like I said, embedded decline characters. >Art >On Apr 26, 2012 6:54 PM, "FAN YANG" <fania0324@yahoo.com.tw> wrote: >> the result from od -c: >> 0000000 h e l l o t e s t \\\\ \\ j i j i >> 0000020 j i \\\\ \\ ; \\ >> 0000026 >> >> >can youdo an od -c instead of a cat? >> >> >Mfg >> >Joerg Volz >> >> >Am 26.04.2012 um 10:45 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: >> >> >> if i take another delimiter instead of "^A", the result is still the >> same. >> >> >> >> unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" >> >> select rpt from test >> >> where rpt_id='text'>> >> >> >> #cat text.rpt >> >> hello test\\\\ >> >> test\\\\ >> >> ; >> >> >> >> >> >> >> >> ******************************************************************************* >> >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> >> >> >> >> >> >> >> >> ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >>

In the current engines (11.50 & 11.70) there are ways to handle this using LOAD, in 9.40 there is none. You will have to write your own loader and unloader that understands the file format you want to use in Perl, Java, ESQL/C, or some other host language. You will either have to work out both a column delimiter character and a record delimiter character that are not contained in any of the TEXT column records (say carriage return as a record delimiter and form feed as a field deliminter) or output the fixed size portion of rows to the file in a filed column length format and precede the variable length portion (ie the TEXT column data) with a fixed length size field so your load program can count off the characters to the beginning of the next record, or use a tagged value format like XML. Something like: <record> <field1>record 1, field1 value<f/field1> <field2>This is the data for field2.>/field2> </record> <field1>record 2, field1 value</field1> <field2>This is the data for field2. </field2> <record> This wouldn't be hard as there are XML parsing libraries out there for most programming environments. You could use a true full standard CSV file format, though that hasn't been documented since before there was a WWWeb. The full standard required that string fields be quoted so that embedded delimiters (both field and record delimiters) can be recognized since they would be inside the quoted strings. That format would look like: "record 1, field1 value"|"This is the data for field2. " "record 2, field1 value"|"This is the data for field2. " 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, Apr 26, 2012 at 7:08 PM, FAN YANG <fania0324@yahoo.com.tw> wrote: > thanks for art's reply, but sorry i still don't know how to aviod this. > could > u help me with that? > > thanks so much > > >Like I said, embedded decline characters. > > >Art > >On Apr 26, 2012 6:54 PM, "FAN YANG" <fania0324@yahoo.com.tw> wrote: > > >> the result from od -c: > >> 0000000 h e l l o t e s t \\\\ \\ j i j i > >> 0000020 j i \\\\ \\ ; \\ > >> 0000026 > >> > >> >can youdo an od -c instead of a cat? > >> > >> >Mfg > >> >Joerg Volz > >> > >> >Am 26.04.2012 um 10:45 schrieb "FAN YANG" <fania0324@yahoo.com.tw>: > >> > >> >> if i take another delimiter instead of "^A", the result is still the > >> same. > >> >> > >> >> unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" > >> >> select rpt from test > >> >> where rpt_id='text'> >> >> > >> >> #cat text.rpt > >> >> hello test\\\\ > >> >> test\\\\ > >> >> ; > >> >> > >> >> > >> >> > >> > >> > > ******************************************************************************* > >> >> 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. > > --e89a8f3ba959cfb83d04bea07665

On 26/04/2012 09:36, FAN YANG wrote: > if i take another delimiter instead of "^A", the result is still the same. > > unload to "/home/informix9/tmp/text.rpt" DELIMITER ";" > select rpt from test > where rpt_id='text'> > #cat text.rpt > hello test\\\\ > test\\\\ > ; > The \\\\ is to escape the linefeeds that are in rpt