AS400 error

335 pts.
AS400 - SQL Queries
Hi I have cpy the fiel to csv file using command Cpytoimpf.. CPYTOIMPF FROMFILE(ARCPGM/&QFLENM) TOSTMF(&CSVDIR) + MBROPT(*ADD) STMFCODPAG(*PCASCII) + RCDDLM(*CRLF) DTAFMT(*DLM) NULLIND(*YES). As this file when we are loading in oracle is not loading few bad records,bad records are created due to double quotes,comma in middle of the string field. Is there any common or dynamic program which we can write in RPGLE or CLLE.where we can read the table check for the special characters for each and every fields. pls let me know ur thoughts on this...thanks for ur help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

You can write and RPG program and have the file internally defined as one field.
Then read a record, do and use BIF to examing the field and replace the invalid characters.

Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • TomLiotta
    What is your operating system version/release? It's hard to know what to suggest without knowing what's available. For example, at V5R4, there is the STRESCCHR() parameter that can help with embedded quotes and some other problems. If you don't have a current release, consider running with parms like STRDLM(X'3E') and FLDDLM(X'3D'). Create the output streamfile with the data that you need but with some temporary delimiters. Then run a QShell script that replaces all embedded quotes with duplicated quotes before replacing your temporary delimiters with "real" delimiters. I used x'3E' and x'3D' as examples, but you can use any character or value that isn't a valid character in your strings. Or you might create a LF over your file that uses a translation table to output some character different from quotes in your string fields. Run CPYTOIMPF over the LF, and again run a basic script to convert the translated characters to doubled embedded quotes or whatever you need them to be. An advantage of doing this with just CPYTOIMPF and QShell might be that the same solution could be used in the future. A RPG program might be unique for every future file. But be aware that a program could be the best choice. Tom
    125,585 pointsBadges:
  • The Most-Watched IT Questions: March 29, 2011 - ITKE Community Blog
    [...] 2. CharlieBrowne and TomLiotta are on the scene again, this time helping to figure out an AS/400 error. [...]
    0 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: