carriage returns within a column
The poster had text columns containing embedded carriage returns/newlines, so unloading query results to a file produced broken multi-line records that couldn't be loaded into another database; REPLACE in SQL didn't help. Doug Lawry suggested IFX_ALLOW_NEWLINE('T'), but that addressed the opposite need. Jonathan Leffler suggested post-processing the output with tr (for ^M) or Perl to join lines ending in an odd number of backslashes. The poster resolved it by unloading to a file and cleaning it up with a small awk script.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, I have a problem with a database. Within some columns there are carriage returns. When I do a query on the database and output the data to a file I have many rows that are not necessary because of the carriage return. I tried to replace the <cr>\\ with the string manipulation "replace" but it did not work out. Does someone know an efficient way to get rid of these <cr>\\? Greetings
I think it would also work out if i could change the record-delimiter --> e.g.: columndelimiter = $ and record-delimiter = ~ the end of a record would look like this "$~" I hope you get what I mean ...
Chris,
If the REPLACE built-in function is available, then I expect your Informix
version is recent enough for:
EXECUTE PROCEDURE IFX_ALLOW_NEWLINE('T');
You can then have newlines within quotes in your update statement.
See my posting of April 22nd for a full example.
--
Regards,
Doug Lawry
www.douglawry.webhop.org
"christrier" <chrishunnell@gmail.com> wrote in message
news:1115109616.810973.91910@z14g2000cwz.googlegroups.com...
> Hi,
>
> I have a problem with a database. Within some columns there are
> carriage returns.
> When I do a query on the database and output the data to a file I have
> many rows that are not necessary because of the carriage return.
> I tried to replace the <cr>\\ with the string manipulation "replace" but
> it did not work out. Does someone know an efficient way to get rid of
> these <cr>\\?
>
> Greetings
Doug, I don´t want to have newlines within qoutes, in fact i need not to have newlines within quotes. The Problem is: somebody allowed newlines within quotes and I have to do querys that are exactly one line because they will be inserted in another database. At the moment I am not able to insert the resultsets of the query into the other database. Regards, Chris Hunnell P.S. your link was not available anymore
christrier wrote: > I have a problem with a database. Within some columns there are > carriage returns. > When I do a query on the database and output the data to a file I have > many rows that are not necessary because of the carriage return. > I tried to replace the <cr>\\ with the string manipulation "replace" but > it did not work out. Does someone know an efficient way to get rid of > these <cr>\\? Are these carriage returns, CR = ^M, or newlines, NL = ^J? If they're ^M and you're on Unix, pipe the output file through 'tr' to erase the ^M \\015 characters: cat data.out | tr -d '\\015' > data.in If they're newlines, you have a bigger problem - you need to replace the backslash newline with nothing. Unless you've got an astronomically large data file, I'd be sorely tempted to slurp the entire file into Perl, do a global search and replace for an odd number of backslashes followed by a newline, and replace the odd backslash and newline with nothing - or a blank or your chosen substitute. Alternatively, read the file line at a time, concatenating the next line each time you come across an odd number of backslashes followed by a newline. Or any other inventive method you care to consider. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.01 -- http://dbi.perl.org/
there are some CR and NL in these columns! the tables are very large and i found a way to avoid this problem. i am outputing the select statement into a file and then i replace all the "wrong" stuff with a little awk-script - so the problem could be solved. but thanks for your help!!