Let's wrap up our three-part FTP series by performing an import.
In part 1 of this TechTip series, I explained how to automate FTP transfers. In part 2, I showed how to use the procedure in a real-life situation. Now, part 3 will focus on how to import a text file into a physical file.
It all depends on how the input file is formatted. Basically, it comes down to a very simple thing: it either has fields with fixed lengths or uses some sort of character to separate the values (like a CSV uses the comma).
My approach to the first type would be to create a physical file with the same field structure (i.e., same field lengths and types) as your text file and transfer the text file directly into the physical file using FTP. This doesn't require any programming, just the steps described in part 1 with a little twist: instead of changing the directory to an IFS folder, you'll need to change to the library where your physical file resides.
To import the second type, two steps are required: the text file is sent to an IFS folder (via FTP, for example), and then it's copied to the receiving physical file via the CPYFRMIMPF command. Let's assume for a moment that I want to transfer file myfile.txt to a physical file called IMPORTFILE and that the text file uses the semi-colon (;) as the field separator. The command to import the file would be this:
CPYFRMIMPF FROMSTMF('/textfiledir/myfile.txt') TOFILE(IMPORTFILE) MBROPT(*REPLACE) RCDDLM(*CRLF) FLDDLM(';') RPLNULLVAL(*FLDDFT)
Since IMPORTFILE is a temporary input file, I'm using MBROPT(*REPLACE), which will clear the first member of the file (default value of the TOFILE parameter for the file's member) before copying the data.
Then, RCDDLM(*CRLF) means that each line, terminated by a hard return + line feed or [ENTER] character, represents a record. If the input file is generated by a program, this is the most usual "record delimiter character."
Next, the most important parameter: FLDDLM(';') indicates that the field delimiter character is the semi-colon. This is the parameter that varies from one file format to another. In some cases, it might be a comma; in others, the semi-colon or some other character. It doesn't matter, as long as you change the FLDDLM parameter to the appropriate value.
Last, but not least, RPLNULLVAL(*FLDDFT) tells the system to replace the NULL values of the input file's fields with the temporary physical file's fields' default value. For instance, if there's no value for an integer field on the physical file, it will be replaced by (or converted to) zero.
Now, how to avoid the repetitive task of copying the text file into the physical file?
The objective is to get the data into the database. Obviously, there will be some sort of field validation/business rules checks. For that, there will be an RPG program. We can use the same program to transfer the data, validate it, and import it to the database, but there's a catch: once you declare the import file and the program starts running, the file will be locked and the CPYFRMIMPF command will not run. The trick is declaring the file as user-open (USROPN keyword in the file declaration line):
FIMPORTFILEIF E Disk UsrOpn
Then, copy the data using CPYFRMIMPF before opening the file (as shown below).
...
//Prepare the data copy command
P_Cmdlin = 'CPYFRMIMPF FROMSTMF(' + '''' + '//textfiledir/myfile.txt' +
'''' + ') TOFILE(IMPORTFILE) MBROPT(*REPLACE) ' +
'RCDDLM(*CRLF) FLDDLM(' + '''' + ';' + '''' + ') ' +
'DECPNT(*COMMA) RPLNULLVAL(*FLDDFT)';
//Execute the data copy command
ExecCmd(P_CmdLin);
//Open the file to process the data
Open IMPORTFILE;
...
I'm using procedure ExecCmd, but you can also use QCMDEXC to execute the command. Just remember to close the file before the end of the program!
This concludes the second method of getting data into System i and processing it automatically.
Note that the procedures described in these three TechTips have some drawbacks. Keep in mind that storing a user's password in a text file (I mean the FTP script file) is a security risk, which can be mitigated with adequate file access authorities and file concealing tricks, like hiding it with an inconspicuous name among other files or changing the extension (it's not required to be .scp) to nfo, dll, or whatever you prefer.
There are other alternatives to transfer files, like dedicated FTP and SFTP software, available on the market. If it can be invoked via a command line, it can be used in a batch file, like the plain and simple FTP used in part 2.
Then, email is not 100 percent reliable, so weigh carefully the pros and cons of the solution proposed in part 2.
The bottom line is that you can automate those tedious file-transfer-run-program tasks by using any kind of FTP data transfer that can be invoked via the command line and RMTCMD (or its equivalent on non-iSeries Access software).
Next, I'll explain how to invoke programs from a PC-based application and get the results in real-time using ODBC!
LATEST COMMENTS
MC Press Online