Re: Replacing carriage returns in a data file

Thanks for the reply
Will try tomorrow

----- Original Message -----
From: Monnier, Gary [mailto:Gary.Monnier@xxxxxxxxx]
Sent: Wednesday, August 14, 2013 05:38 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Replacing carriage returns in a data file

Alan,

Do you have an example you can post? Something like this...

5C5C5C5C 5C5C4040 40404040 40404040 40404040 ******
6E2C452D 4D61696C 0D0A n,E-Mail
6E69733F 6177222C 2C0D0A nis?aw",,

Note the 0D0A at the end of lines 2 and 3. They are carriage return / line feed characters. Using ENDLINFMT(*CRLF) in the CPYFRMSTMF should eliminate them for you.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Wednesday, August 14, 2013 2:24 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

Thanks for the reply Eric - but here's the problems It's not just 1 or 2 files - but can be any one or more of thousands PLUS The carriage return is NOT at the end of the record, but somewhere before then

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of DeLong, Eric
Sent: Wednesday, August 14, 2013 4:57 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

Alan, can you EDTF your file, then use F15 Services and change Option 5 to *LF?

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
Sent: Wednesday, August 14, 2013 3:52 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

This day just keeps getting better and better (sarcasm switch off) I have now realized that the file (with the carriage return) is created using the command CPYFRMSTMF And the carriage return is in the stream file This then causes the one record to be split into 2 (or more) in the table.
I have to replace the carriage return in the ifs prior to the CPYFRMSTMF command I cannot find any examples of using SQL on an ifs file.


Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: Alan Shore
Sent: Wednesday, August 14, 2013 4:20 PM
To: Midrange Systems Technical Discussion
Subject: RE: Replacing carriage returns in a data file

Thanks Luis
Let me have a test using what you supplied MUCH appreciated

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Luis Rodriguez
Sent: Wednesday, August 14, 2013 4:13 PM
To: Midrange Systems Technical Discussion
Subject: Re: Replacing carriage returns in a data file

Alan,

You could try executing the following SQL statement:

update @test/alan_cr set alan_cr = replace(alan_cr, X'0D', '\')

IIRC, the hex for a CR is X'0D', so this would place a '\' in its place (or whatever character you need). You could call this inside a CL using the QSH
DB2 instruction.

HTH,
Luis Rodriguez
IBM Certified Systems Expert - eServer i5 iSeries