AS400 error

iiiiiiiiiiiii

335 pts.

Tags:

AS400 - SQL Queries

CLLE

CPYTOIMPF

RPGLE

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 server...it 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.

Asked: March 16, 2011 9:12 AM Last updated: March 16, 2011 9:33 PM

Answer Wiki

Last updated: March 16, 2011 12:47 PM GMT

CharlieBrowne62,340 pts.
History
Contributors
- Ordered by most recent
- CharlieBrowne62,340 pts.

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

TomLiotta Mar 16, 2011 9:33 PM GMT

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:

report
The Most-Watched IT Questions: March 29, 2011 - ITKE Community Blog Mar 29, 2011 6:35 AM GMT

[...] 2. CharlieBrowne and TomLiotta are on the scene again, this time helping to figure out an AS/400 error. [...]

0 pointsBadges:

report