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!

Rafael Victória-Pereira has more than 20 years of IBM i experience as a programmer, analyst, and manager. Over that period, he has been an active voice in the IBM i community, encouraging and helping programmers transition to ILE and free-format RPG. Rafael has written more than 100 technical articles about topics ranging from interfaces (the topic for his first book, Flexible Input, Dazzling Output with IBM i) to modern RPG and SQL in his popular RPG Academy and SQL 101 series on mcpressonline.com and in his books Evolve Your RPG Coding and SQL for IBM i: A Database Modernization Guide. Rafael writes in an easy-to-read, practical style that is highly popular with his audience of IBM technology professionals.

Rafael is the Deputy IT Director - Infrastructures and Services at the Luis Simões Group in Portugal. His areas of expertise include programming in the IBM i native languages (RPG, CL, and DB2 SQL) and in "modern" programming languages, such as Java, C#, and Python, as well as project management and consultancy.

---

MC Press books written by Rafael Victória-Pereira available now on the MC Press Bookstore.

		Evolve Your RPG Coding: Move from OPM to ILE...and Beyond Transition to modern RPG programming with this step-by-step guide through ILE and free-format RPG, SQL, and modernization techniques. List Price $79.95 Now On Sale
		Flexible Input, Dazzling Output with IBM i Uncover easier, more flexible ways to get data into your system, plus some methods for exporting and presenting the vital business data it contains. List Price $79.95 Now On Sale
		SQL for IBM i: A Database Modernization Guide Learn how to use SQL’s capabilities to modernize and enhance your IBM i database. List Price $79.95 Now On Sale

Also Read

Advanced/Free - Function Calls and Return Values

PRACTICAL ILE - ILE Service Programs

Data-Manipulation Operations Using Free Format, Part Two