How to read from flat file

255 pts.
Tags:
AS/400
as/400 files
How to read from flat file For eg: The data in one flat file is like this
D|EMPN0001_001_01|C55BFIT|.00|.00|GBP|Y|1.00|20030101|20130301|IT|||||LC|100 D|EMPN0002_001_01|C55BFIT|.00|.00|GBP|Y|1.00|20030101|20130301|ECE|||||LC|100 
The data in another flat file is like this:
D|EMPN0001|N||.048448|.048448|100.0000|0.0000|100.0000|77.7782|EMP02|CME02|LM D|EMPN0002|N||1.000000|1.000000|100.0000|0.0000|100.0000|35.8566|EMP02|CME02| D|EMPN0003|N||.025600|.025600|100.0000|0.0000|100.0000|100.0000|EMP02|CME02|L 

I want to read these two files and stores these values in a separate Physical files. Main purpose is that to compare the values in the physical files like before and after changes to the values.
ASKED: March 28, 2013  5:49 AM
UPDATED: March 28, 2013  2:23 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 26  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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    You can use the CPYFRMIMPF command to import data from a pipe-delimited streamfile or physical file member into a data base file. -- Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    What are the parameters should i given in the CPYFRMIMPF. In the from file I give the name of the existing flat file, and in the to file I give the name of the physical file. What are the other parameters should I need to give? The physical file that I have created, contains the exact data type and length.
    255 pointsBadges:
    report
  • Prashanth123
    Flat file which I am using is a multi-member file and I compiled the physical file with *NOMAX in the members option.
    255 pointsBadges:
    report
  • TomLiotta
    What are the parameters should i given in the CPYFRMIMPF.   I don't know. It depends on the attributes of your input file members and what your chosen output database file looks like. The [help] for the command should guide you in what parameter values you give.   Why do you have this kind of data in a multi-member physical file instead of a streamfile where it probably ought to be?   Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    This is the command I gave.  CPYFRMIMPF FROMFILE(BASEL2/RPOOL *ALL) TOFILE(PRASH/RPOOL1 *ALL) RCDDLM(|STRDLM(|) FLDDLM('|')         It displayed error ->    Message . . . . :   To-file RPOOL1 in PRASH not allowed.                     Cause . . . . . :   File RPOOL1 in PRASH is not the correct file type for the  copy command.                                                                                          Since it is a exisitng business logic the data are present like this. Kindly guide me in this. TIA
    255 pointsBadges:
    report
  • Prashanth123
    This is the command I gave.  CPYFRMIMPF FROMFILE(TESTB/RPOOL *ALL) TOFILE(PRASH/RPOOL1 *ALL) RCDDLM(|STRDLM(|) FLDDLM('|')         It displayed error ->    Message . . . . :   To-file RPOOL1 in PRASH not allowed.                     Cause . . . . . :   File RPOOL1 in PRASH is not the correct file type for the  copy command.                                                                                          Since it is a exisitng business logic the data are present like this. Kindly guide me in this. TIA
    255 pointsBadges:
    report
  • TomLiotta
    The TOFILE() must be a database file with fields that can accept each of the values between the delimiters. In your first example, the first field looks like a CHAR(1) field, the second field looks like it might be a CHAR(15), and so forth. What are the field definitions in file PRASH/RPOOL1? -- Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    R RPOOL1R                                     RPOPF0         1A     RPOPF1        10A     RPOPF2         1A                           RPOPF3         4A     RPOPF4         7S 6   RPOPF5         7S 6   RPOPF6         9P 4   RPOPF7         9P 4   RPOPF8         7P 4   RPOPF9         7P 4   RPPF10         5A     RPPF11         5A     RPPF12         5A   RPPF13         7P 6RPPF14         7P 6RPPF15         5A  RPPF16         9P 4RPPF17         9P 4RPPF18         5A  RPPF19         7P 4RPPF20         7P 4RPPF21         5A  RPPF22         4P 0RPPF23        17P 2RPPF24        17P 2RPPF25        17P 2 The above is my physical file structure.
    255 pointsBadges:
    report
  • TomLiotta
    This example from your original question almost matches okay with your PF description:
    D|EMPN0001|N||.048448|.048448|100.0000|0.0000|100.0000|77.7782|EMP02|CME02|LM 
    At least, it matches as far as it goes. It's not long enough to match the final 13 fields. I can't tell if you left off the end of your example or your PF doesn't match the flat record.This line from your original question doesn't match your PF fields at all after the first field:
    D|EMPN0001_001_01|C55BFIT|.00|.00|GBP|Y|1.00|20030101|20130301|IT|||||LC|100 
    I don't know what data you're using for the from-file input, so I can't say what's wrong. If you showed real data in the original question and the entire lines were shown, then the PF doesn't match either of your example flat-files. And if you didn't show the entire lines from the flat-file, then I can't compare it to your PF.So, either correct your PF or show entire lines from the flat-file.Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    Sorry. The file description which I have given is for this example : D|EMPN0001|N||.048448|.048448|100.0000|0.0000|100.0000|77.7782|EMP02|CME02|LM D|EMPN0002|N||1.000000|1.000000|100.0000|0.0000|100.0000|35.8566|EMP02|CME02| D|EMPN0003|N||.025600|.025600|100.0000|0.0000|100.0000|100.0000|EMP02|CME02|L The input files are normal physical files. In the above example if you can see "||" a value will come in between this. As employeed id's dont have value present so it is blank. I have prepared RPOOL1 based on the flat file structure given in the system design document. Should the length match between the values stored in flat file and new physical file created (RPOOL1) ?. Is it mandatory? Thanks for your help.
    255 pointsBadges:
    report
  • TomLiotta
    The lines that you are showing only include enough data to match fields up through RPPF12. There is nothing shown to match the remaining fields from RPPF13 up through RPPF25. Unless you show complete lines, there's no way to match up the data. -- Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    The physical file which have created is based on the system design document of the flat file. Even if there are 25 fields only 20 or less will be populated to the flat file by the program. One flat file is like this: D|CBLMBB0012|N||.003189|.003189|100.0000|0.0000|100.0000|94.6058|LMP02|LME02|LML02|||||||100.0000|96.6667|LML01 D|CBLMBB0013|N||.004716|.004716|100.0000|0.0000|100.0000|97.8765|LMP02|LME02|LML02|||||||100.0000|96.6667|LML01 D|CBLMBB0014|N||.006408|.006408|100.0000|0.0000|100.0000|94.0898|LMP02|LME02|LML02|||||||100.0000|108.0031|LML01 D|CBLMBB0015|N||.009106|.009106|100.0000|0.0000|100.0000|94.1861|LMP02|LME02|LML02|||||||100.0000|94.2362|LML01 D|CBLMBB0016|N||.012925|.012925|100.0000|0.0000|100.0000|91.9083|LMP02|LME02|LML02|||||||100.0000|96.6982|LML01 D|CBLMBB0017|N||.017816|.017816|100.0000|0.0000|100.0000|94.7015|LMP02|LME02|LML02|||||||100.0000|96.6982|LML01 D|CBLMBB0018|N||.025604|.025604|100.0000|0.0000|100.0000|106.0950|LMP02|LME02|LML02|||||||100.0000|108.0031|LML01 D|CBLMBB0019|N||.036408|.036408|100.0000|0.0000|100.0000|93.0000|LMP02|LME02|LML02|||||||100.0000|96.6982|LML01 D|CBLMBB0020|N||.047702|.047702|100.0000|0.0000|100.0000|95.6152|LMP02|LME02|LML02|||||||100.0000|96.6982|LML01
    255 pointsBadges:
    report
  • TomLiotta
    Having full rows for the pipe-delimited file and the DDS for the PF let me do a visual check and a run test. I put your sample data into a streamfile (which should normally be used, but isn't required) and ran a couple variations of CPYFRMIMPF. This is what eventually worked: CPYFRMIMPF FROMSTMF(pipes.psv) TOFILE(TSTCSV) RCDDLM(*CRLF) STRDLM(*NONE) FLDDLM('|') RPLNULLVAL(*FLDDFT) There are a couple differences from yours. I didn't notice the first one until now. You have member *ALL specified for your to-file. It seems very unlikely that you're using a partitioned file, so you probably should either leave that at the default or specify *FIRST. You might need *FROMMBR or a named member, but it seems unlikely since an import hasn't worked yet on your system. You don't have a member structure in place yet that you might be required to use in the future. Next, you have RCDDLM(|) and STRDLM(|). Those aren't appropriate. They can't make sense if you also have FLDDLM(‘|’). The same delimiter wouldn't make sense for all three records, strings and fields. The conversion would never be able to determine when a pipe was delimiting a record, a string or a field. The data example shows that FLDDLM(‘|’) is correct, but there is no string delimiter. Use STRDLM(*NONE). (Using quotes for strings is most common.) You also need to use RCDDLM(*EOR); that's the default and your input is a physical file, so you can let it default. (The help text for the parameter tells you that.) The last item is the RPLNULLVAL(*FLDDFT) parameter. You aren't using null-capable fields in your PF, so you should read the help text for that parameter for an explanation. Using your data and your PF DDS, the command worked for me. Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    okay.. Sure will check this and let you know. Thanks a ton for your help.
    255 pointsBadges:
    report
  • Prashanth123
    CPYFRMIMPF FROMFILE(PRASH/RPOOL *FIRST) TOFILE(PRASH/RPOOL1 *FIRST) RCDDL M(*EOR) DTAFMT(*DLM) STRDLM(*NONE) FLDDLM('|') Used this command. It say's Zero records copied.
    255 pointsBadges:
    report
  • TomLiotta
    You still don't seem to be including the RPLNULLVAL(*FLDDFT) parameter. -- Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    Thanks a ton. that field did it.. Really thanks for all the help.
    255 pointsBadges:
    report
  • Prashanth123
    Main purpose of this question is because of the the below reason. If I make changes to the exsisting program then some values in the output file gets affected like for eg: Employee Salary and other financial details. I will create a new program with the command below. It is will be basically comparing the before and after values of the physical files. so that it will be useful for testing.
    255 pointsBadges:
    report
  • Prashanth123
    If I have flat file like this how can i convert them to normal file D|AD41001900_001_00|AD41001900_001_00|CBGBSGB D|AD48001200_001_00|AD48001200_001_00|CBGBSGB There are two delimiters here. How can I do it in CPYFRMIMPF command?
    255 pointsBadges:
    report
  • TomLiotta
    Hard to say. What are you using for delimiters? I see three 'pipe', "|", symbols and four 'underscore', "_", symbols. Are you saying that you have two different kinds of field delimiters in a single line? . There is no good way to do it with just CPYFRMIMPF. You could use something like QShell's tr - Translate characters utility to change all 'pipes' to 'underscores' or vice versa. Then run CPYFRMIMPF over the result. But that depends on what the different delimiters mean. . You're probably better off not using CPYFRMIMPF at all. Most likely you should just read the file and process each line according to whatever the instructions are for the file. . Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    This file's output is being generated by a program for which i have no access. I have access only to the output files. There are two delimiters as shown below. CPYFRMIMPF command worked perfectly when there is one Delimiter and it is the best way to do it as you said. I have not worked on qshell - translate characters. Don't know how to do QSHELL IN rpg.
    255 pointsBadges:
    report
  • TomLiotta
    You wouldn't use QShell "in RPG" normally. There are APIs, but the STRQSH command is better in CL before calling any RPG program, unless you become very familiar with QShell. . I was first suggesting CL with a STRQSH command for the tr utility that would change all of one delimiter to the other. Then run CPYFRMIMPF over the streamfile that now has one delimiter. . As an alternative, you would not use QShell and you wouldn't use CPYFRMIMPF. You would do everything with RPG programming. (Or use COBOL or C or CL or Java or whatever language you prefer. All of the laguages can your input file, and you can process each line any way you want. . You are not limited to what CPYFRMIMPF can do. You should only use CPYFRMIMPF for actual import files, not for files that have custom structure. Your input file is not an "import" file. It's a data transfer file for an application. . The documentation for the tr utility will let you run tests on a copy of your input file until you understand how it works. . Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    Thanks Tom
    255 pointsBadges:
    report
  • Prashanth123
    I gave STRQSH in the command line and it opened the QSH command entry session if my file is there PRASH library and main flat file is there in PROD. can u give sample command using the TR.? wrote a command on my own below. tr '_' '\n' /PROD/FCLT1
    255 pointsBadges:
    report
  • TomLiotta
    The appropriate place for a file like yours is in a subdirectory in the /root file system, rather than in a library. I'm not sure if the tr utility would work well in a library file, and the /QSYS.LIB file system was unexpected. . Since you're trying to do this in a library, you might find it easier to use a SQL UPDATE statement than to use tr. If the file is in library PROD and is named FCLT1, a SQL statement might be: . == > UPDATE PROD/FCLT1 SET FCLT1 = REPLACE( FCLT1 , '_', '|' ) That would replace all underscores with pipe characters. . You really should start using IFS facilities for files like this. . Tom
    125,585 pointsBadges:
    report
  • Prashanth123
    thanks a lot tom :)
    255 pointsBadges:
    report

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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

Following