Re. carriage return
A user on IDS 9.4/AIX unloaded a table to a flat file and found one VARCHAR column contained embedded newline characters, breaking records across lines; vi and tr replaced newlines on every line, which wasn't wanted. Suggestions included writing a small C/Perl filter that only strips newlines not after the last field separator, and a vi substitute on ^M at line end. The accepted fix was Mark Tyrer's awk script that joins lines until one ends with the pipe delimiter; it worked since the newlines were at column end. Jonathan Leffler later noted that UNLOAD actually escapes embedded newlines with a backslash, and that counting backslashes (odd vs even) is needed to distinguish an embedded newline from trailing backslashes.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello Everybody, Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file we found that one varchar column has "end of line" character and we want to remove it while unloading. We do not want to change the data in the database, I tried to find/replace via "VI"/"tr" it didn't help because it would replace for all the lines. Any tips ? Thanks in advance, Sushil...
Write your own small C/Perl program to replace the newline unless it appears after the Nth (last) field separator. Art S. Kagel ----- Original Message ----- From: Sushil Shir.... <ids@iiug.org> At: 1/23 9:37 Hello Everybody, Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file we found that one varchar column has "end of line" character and we want to remove it while unloading. We do not want to change the data in the database, I tried to find/replace via "VI"/"tr" it didn't help because it would replace for all the lines. Any tips ? Thanks in advance, Sushil... ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Sushil, In vi you can use the substitute command: If you are using vi 1,$s/<CTL><V><CTL><M>$//g If you are using vim, as this aliases <CTL><V> to <CTL><Q> 1,$s/<CTL><Q><CTL><M>$//g Regards Kenneth Penza Technical Services Officer Systems and Database Services Service Management Department Malta Information Technology & Training Services Ltd Please read our Legal Notice: http://emailpolicy.mitts.gov.mt P Please consider your environmental responsibility before printing this e-mail. END OF TEXT -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Sushil Shir.... Sent: 23 January 2006 15:37 To: ids@iiug.org Subject: Re. carriage return [6245] Hello Everybody, Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file we found that one varchar column has "end of line" character and we want to remove it while unloading. We do not want to change the data in the database, I tried to find/replace via "VI"/"tr" it didn't help because it would replace for all the lines. Any tips ? Thanks in advance, Sushil... ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.
If you copy the following into a file, name_of_file BEGIN{outline=""} { if ($0 ~ /|$/) { print outline $0 outline="" } else { outline=outline $0 } } and run awk -f name_of_file name_of_flat_file > name_of_new_flat_file it should take out the new line characters that you do not want - I am sure that you can find neater solution if you searched for one though. The assumptions that I have made are: That you are using the unload sql command and that the delimiter is a '|'. You can still experience problems with this script if the newline character occurs as the first character of the field. Regards Mark >From: "Sushil Shir...." <sushilps@hotmail.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: Re. carriage return [6245] >Date: Mon, 23 Jan 2006 09:37:01 -0500 (EST) > >Hello Everybody, > >Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file >we >found that one >varchar column has "end of line" character and we want to remove it while >unloading. >We do not want to change the data in the database, I tried to find/replace >via "VI"/"tr" >it didn't help because it would replace for all the lines. >Any tips ? > >Thanks in advance, >Sushil... > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Fast. Simple. Personal. Visit the all-new MSN South Africa. http://za.msn.com/
Hi Mark, Thanks you !!!!! It worked the way I was expecting the data output, fortunately the carriage return was at the end of column. Thanks to Art, Ken and everybody for your input too. Regards Sushil..... >From: "Mark Tyrer" <m_tyrer@hotmail.com> >Reply-To: ids@iiug.org >To: ids@iiug.org >Subject: RE: Re. carriage return [6248] >Date: Mon, 23 Jan 2006 11:11:12 -0500 (EST) >Received: from perform.iiug.org ([216.177.38.211]) by >bay0-mc2-f4.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.211); Mon, 23 >Jan 2006 08:11:03 -0800 >Received: by perform.iiug.org (Postfix, from userid 60001)id B9BB5A27B; >Mon, 23 Jan 2006 11:11:18 -0500 (EST) >Received: from perform.iiug.org (localhost [127.0.0.1])by perform.iiug.org >(Postfix) with ESMTP id 9C308A246;Mon, 23 Jan 2006 11:11:14 -0500 (EST) >Received: by perform.iiug.org (Postfix, from userid 60001)id 6C208A23D; >Mon, 23 Jan 2006 11:11:12 -0500 (EST) >X-Message-Info: N4u0pqWW+O2MxZP+2SBixj2QCZDEX6/ao+jVdAX3Ypo= >X-Spam-Checker-Version: SpamAssassin 3.1.0 (2005-09-13) on perform.iiug.org >X-Spam-Level: X-Spam-Status: No, score=-4.7 required=7.5 >tests=ALL_TRUSTED,AWL,BAYES_00,FORGED_HOTMAIL_RCVD2 autolearn=unavailable >version=3.1.0 >X-Original-To: ids@iiug.org >Delivered-To: sig_user@iiug.org >X-IIUG: ids@iiug.org >X-BeenThere: ids@iiug.org >X-Mailman-Version: 2.1.6 >Precedence: list >List-Id: <ids.iiug.org> >List-Unsubscribe: ><http://www.iiug.org/mailman/listinfo/ids>,<mailto:ids-request@iiug.org?subject =unsubscribe> >List-Archive: <http://www.iiug.org/pipermail/ids>; >List-Post: <mailto:ids@iiug.org> >List-Help: <mailto:ids-request@iiug.org?subject=help> >List-Subscribe: ><http://www.iiug.org/mailman/listinfo/ids>,<mailto:ids-request@iiug.org?subject =subscribe> >Errors-To: ids-bounces@iiug.org >Return-Path: ids-bounces@iiug.org >X-OriginalArrivalTime: 23 Jan 2006 16:11:08.0900 (UTC) >FILETIME=[9EF84A40:01C62037] > >If you copy the following into a file, name_of_file > >BEGIN{outline=""} >{ > >if ($0 ~ /|$/) { > >print outline $0 > >outline="" > >} else { > >outline=outline $0 > >} >} > >and run > >awk -f name_of_file name_of_flat_file > name_of_new_flat_file > >it should take out the new line characters that you do not want - I am sure >that you can find neater solution if you searched for one though. > >The assumptions that I have made are: > >That you are using the unload sql command and that the delimiter is a '|'. > >You can still experience problems with this script if the newline character >occurs as the first character of the field. > >Regards > >Mark > > >From: "Sushil Shir...." <sushilps@hotmail.com> > >Reply-To: ids@iiug.org > >To: ids@iiug.org > >Subject: Re. carriage return [6245] > >Date: Mon, 23 Jan 2006 09:37:01 -0500 (EST) > > > >Hello Everybody, > > > >Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file > >we > >found that one > >varchar column has "end of line" character and we want to remove it while > >unloading. > >We do not want to change the data in the database, I tried to >find/replace > >via "VI"/"tr" > >it didn't help because it would replace for all the lines. > >Any tips ? > > > >Thanks in advance, > >Sushil... > > > > > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > >_________________________________________________________________ >Fast. Simple. Personal. Visit the all-new MSN South Africa. >http://za.msn.com/ > > >******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
On 1/23/06, Sushil Shir.... <sushilps@hotmail.com> wrote: > Env. IDS 9.4 FC4 AIX 5.2, while unloading one of the table on a flat file we > found that one > varchar column has "end of line" character and we want to remove it while > unloading. > We do not want to change the data in the database, I tried to find/replace > via "VI"/"tr" > it didn't help because it would replace for all the lines. > Any tips ? I should probably keep my mouth shut since the problem appears to have been resolved. However, I'd expect that the unloaded data contains a backslash, newline and then the delimiter (pipe). As far as I can see, none of the solutions dealt with the backslash - which puzzles me. However, if you're happy with the solutions, then go with it. (Oh, if you have really horrid data, worry about three backslashes followed by a newline - the unloaded output contains 7 backslashes and the newline. If it contained 6 backslashes, then you'd simply have a line that ended with three backslashes - and no delimiter (which is legitimate, though not normally generated by UNLOAD statements these days). So, how do you know whether to treat it as an embedded newline or as a field ending in backslashes? Count the backslashes - odd means embedded newline; even means no delimiter. For all the nasty low-down details, see the file 'unload.format' in the SQLCMD source code. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/