How to remove Carriage return character from PF

[pintusrm2010]

Hi ,

We have PF which having carraige return character.



Due to this when i am trying to export the file in .csv format using CPYTOIMPF , that particular record is getting splitted into two.

How to remove these carraige return characters.

Regards,

Pintu

[GLS400]

Re: How to remove Carriage return character from PF

Hi pintusrm2010:

check out the code in post #4 here:

http://www.code400.com/forum/showthread.php/6527-Hex-Characters-from-Data-Entry

Best of Luck
GLS

[pintusrm2010]

Re: How to remove Carriage return character from PF

I went there but not able to understand.

DO i need to write a code to download this file. My need is that number of row count in AS400 and number of row count in .csv file which i extracted using CPYTOIMPF should match.

when i am trying to export the file in .csv format using CPYTOIMPF , that particular record is getting splitted into two due to that special character.

Can we remove those characters manually using WRKQRY or STRSQL ?

Hi pintusrm2010:

check out the code in post #4 here:

http://www.code400.com/forum/showthread.php/6527-Hex-Characters-from-Data-Entry

Best of Luck
GLS

[tomliotta]

Re: How to remove Carriage return character from PF

It can probably be done with SQL:

UPDATE mylib/myfile SET Comment = REPLACE( Comment , x'0D', ' ' )

If the field name is COMMENT, it'll need to be quoted, "COMMENT", since it's a reserved word in SQL. And the X'0D' assumes that the CarriageReturn is the EBCDIC value. The third parameter of REPLACE() is a space between the apostrophes, but you could remove that byte completely if you wanted it that way.

[pintusrm2010]

Re: How to remove Carriage return character from PF

For me STRSQL is not there "Command STRSQL not valid for current release" . Can it be done using WRKQRY command??

It can probably be done with SQL:

UPDATE mylib/myfile SET Comment = REPLACE( Comment , x'0D', ' ' )

If the field name is COMMENT, it'll need to be quoted, "COMMENT", since it's a reserved word in SQL. And the X'0D' assumes that the CarriageReturn is the EBCDIC value. The third parameter of REPLACE() is a space between the apostrophes, but you could remove that byte completely if you wanted it that way.

[tomliotta]

Re: How to remove Carriage return character from PF

You don't need STRSQL.

You can use iSeries Navigator and run the statement with 'Run SQL Scripts', or put the statement into a source member and run it with a RUNSQLSTM command, or generate a QM query and run it with STRQMQRY, or put the statement into a REXX procedure and run the procedure, or a variety of other possibilities.

Any system in the AS/400 line since at least version 2 of OS/400 can run SQL statements without having the SQL product installed. It can be handy to learn how various methods can be used.

An improved version of the SQL statement could be:

UPDATE mylib/myfile SET Comment = TRANSLATE( Comment , x'40', x'0D' )
WHERE LOCATE( x'0D', Comment ) > 0

I should have been more definite.

[Robert Clay]

Re: How to remove Carriage return character from PF

It may be prudent to step back a bit and ask a few basic questions:

1. What is the machine OS level?

2. Are all PTFs up-to-date?

3. What tools do you have available? For example, what emulation software are you using? From the screen-print in the original message, it appears that you have some form of IBM i Access installed; so, do you have System i Navigator installed? If so, have you ever used it?

4. Do you have any programming experience, i.e., RPG, SQL, REXX, etc.?

5. Give the complete CPYTOIMPF command that you are using. Perhaps some tweaks are in order.

6. Have you tried any other methodology than CPYTOIMPF? If so, give the steps you have taken and the results.

Without proper tools and/or some sort of programming experience, you may find this to be a difficult task.

Robert

[pintusrm2010]

Re: How to remove Carriage return character from PF

Hi

1. Its windows 7

2. Not sure of PTF.

3. Yes I DO HAVE sql exp

4

The sugession given by Tom will work and i am trying to do using STRQMQRY but i think i am not giving right sql query.

"UPDATE mylib/myfile SET Comment = TRANSLATE( Comment , x'40', x'0D' )
WHERE LOCATE( x'0D', Comment ) > 0"

Is this query is perfect if i need to remove the carriage character present between lines like this?



I tried the above mentioned update query but nothing got changed.

It may be prudent to step back a bit and ask a few basic questions:

1. What is the machine OS level?

2. Are all PTFs up-to-date?

3. What tools do you have available? For example, what emulation software are you using? From the screen-print in the original message, it appears that you have some form of IBM i Access installed; so, do you have System i Navigator installed? If so, have you ever used it?

4. Do you have any programming experience, i.e., RPG, SQL, REXX, etc.?

5. Give the complete CPYTOIMPF command that you are using. Perhaps some tweaks are in order.

6. Have you tried any other methodology than CPYTOIMPF? If so, give the steps you have taken and the results.

Without proper tools and/or some sort of programming experience, you may find this to be a difficult task.

Robert

[Robert Clay]

Re: How to remove Carriage return character from PF

I sincerely hope that you aren't attempting to update a production file! How do you know that those carriage returns weren't added by a production application that would be broken if you update the data?

As to the answers to the questions:

1. I was referring to the IBM i machine, not your PC. Check with your System Administrator if you are unsure.

2. Check with your System Administrator concerning the PTF levels

3. What is sql exp? The question referred to the tools you had available.

4. Maybe you meant "sql exp" to be SQL experience? So, no RPG or anything else in regards to programming?

5. The screenprint of the CPYTOIMPF that you gave is nice, but incomplete. See the "More..." in the bottom right-hand corner? That means the command extends beyond what can be shown on a single screen. The easiest way to show the command you are using would be to press F14 (Shift + F2) and copy that data.

6. I'm unfamiliar with STRQMQRY so I can't help you with that. See question 3.

Is there anyone with a bit more experience and/or authority there who could help?

I'm leaving soon for the weekend but if you post answers to these questions, I'm sure someone will step in to help you.

Good Luck,
Robert

[pintusrm2010]

Re: How to remove Carriage return character from PF

I tried running these 3 update query using STRQMQRY but i am not successful.


UPDATE myTable SET myColumn = REPLACE(REPLACE(myColumn , 'r', ''), 'n', '')

Error : Token REPLACE was not valid.


UPDATE myTable SET myColumn = REPLACE( myColumn , x'0D', ' ' )

Error : Token REPLACE was not valid.



UPDATE MYTABLE SET myColumn =
TRANSLATE(myColumn , x'40', x'0D')
WHERE LOCATE(x'0D', myColumn ) > 0

This query not given me any error but again i rechecked the file and those carriage return character was not got removed. The file remains as it is. I am missing anything on this last query? IS it checking each character of the field? Please help me in this TOM

You don't need STRSQL.

You can use iSeries Navigator and run the statement with 'Run SQL Scripts', or put the statement into a source member and run it with a RUNSQLSTM command, or generate a QM query and run it with STRQMQRY, or put the statement into a REXX procedure and run the procedure, or a variety of other possibilities.

Any system in the AS/400 line since at least version 2 of OS/400 can run SQL statements without having the SQL product installed. It can be handy to learn how various methods can be used.

An improved version of the SQL statement could be:

UPDATE mylib/myfile SET Comment = TRANSLATE( Comment , x'40', x'0D' )
WHERE LOCATE( x'0D', Comment ) > 0

I should have been more definite.

[DeadManWalks]

Re: How to remove Carriage return character from PF

cool man. the "replace" is a valid function. Id check the statement closely to see if you have some typo.

For your little bad character issue, do a DSPPFM on the table then press F10 then F11. Here you can see the hex values you want to be searching for in your replace.

[pintusrm2010]

Re: How to remove Carriage return character from PF

Hi Man,

I cross checked and i am not doing any mistake.

"UPDATE myTable SET myColumn = REPLACE( myColumn , x'0D', ' ' )" . This is the exact wording and i am getting below error which clearly states REPLACE not valid.



Any idea from your side? Also can you check my 3rd query if it is right or not.

UPDATE MYTABLE SET myColumn =
TRANSLATE(myColumn , x'40', x'0D')

cool man. the "replace" is a valid function. Id check the statement closely to see if you have some typo.

For your little bad character issue, do a DSPPFM on the table then press F10 then F11. Here you can see the hex values you want to be searching for in your replace.

[Scott M]

Re: How to remove Carriage return character from PF

Not sure about the REPLACE. On the TRANSLATE, maybe try running under debug (STRDBG) and look in your job log to see if there is a message that is helpful. Did you verify with DSPPFM that the hex value you are specifying is correct.

[Scott M]

Re: How to remove Carriage return character from PF

What version is your IBM i and are you fairly current on PTF's - I found another example of using REPLACE and it looks just like what you have there. But if you are on an old version then maybe that is why it doesn't allow it.

[pintusrm2010]

Re: How to remove Carriage return character from PF

I tried DSPPFM and F10 , F11


But i didnt got what hex value i should refer. I attached screen shot. Can you help me in this?

And ya i am on older version of AS400 . Its V4R3M0 and may be PTF in not fully updated.

What version is your IBM i and are you fairly current on PTF's - I found another example of using REPLACE and it looks just like what you have there. But if you are on an old version then maybe that is why it doesn't allow it.