Read Multiple Files Using App Engine and FileLayout

I want to process several files using PeopleSoft's filelayout.

Assuming you have created your Application Engine, here's a step by step guide.

Create an SQL step to get your output directory.

%Select (PRCSOUTPUTDIR) 
SELECT PRCSOUTPUTDIR 
FROM PS_CDM_LIST 
WHERE PRCSINSTANCE = %Bind(PROCESS_INSTANCE)

Create a PeopleCode step and follow the code flow below.
Customize to your liking.

You have your output directory on state record.
Assign it to a variable.

&FilePath = MYAETRECORD.PRCSOUTPUTDIR.Value;

Assuming you have multiple files, the list will be stored in a stand-alone rowset.
Fetch that list.

&FileRS = CreateRowset(Record.MYRECORD);
&FileRS.Fill("WHERE OPRID = :1 AND RUN_CNTL_ID = :2", &Oprid, &RunControlID);

Now, loop through the stand-alone rowset using a for-loop.

For &i = 1 To &FileRS.ActiveRowCount

ATTACHSYSFILENAME is a unique filename and store in MYRECORD. Note that &Slash here will be based on server, UNIX or NT.

&SysFileName = &FileRS(&i).GetRecord(1).ATTACHSYSFILENAME.Value;
&LocalFileName = &FilePath | &Slash | &SysFileName;

MYURL is set up online; check earlier blog posts for screenshot.
This code will get the file from the record and place it on the server based on &LocalFileName value.

&ReturnCode = GetAttachment(URL.MYURL, &SysFileName, &LocalFileName);
If &ReturnCode <> 0 Then
 MessageBox(0, "", 0, 0, "File Transfer Failed.");
 Exit (1);
Else
 MessageBox(0, "", 0, 0, "File Transfer Successful.");
 
 If FileExists(&LocalFileName, %FilePath_Absolute) Then
   MessageBox(0, "", 0, 0, "File Exists.");

If file exists, proceed to retrieve the file and assign it a file variable.

 &LocalFile = GetFile(&LocalFileName, "R", "A", %FilePath_Absolute);
 If &LocalFile.IsOpen Then
  MessageBox(0, "", 0, 0, "File Opened.");

If file is open proceed to read it using your file layout.
We also declared the Insert SQL which will be used later.

  
 &LocalFile.SetFileLayout(FileLayout.MYFILELAYOUT);
 &DataREC = CreateRecord(Record.MYSTAGINGRECORD);
 &DataRS = &LocalFile.CreateRowset();
 &DataSQL = CreateSQL("%Insert(:1)");

This will be used to read the first line.

&DataRS = &LocalFile.ReadRowset();

And use a while-loop to process each line until end of file.

While &DataRS <> Null

Because we are using filelayout, value should be mapped to its respective field in the standalone rowset.

&Emplid = &DataRS(1).GetRecord(1).GetField(Field.EMPLID).Value;
MessageBox(0, "", 0, 0, "&Emplid: " | &Emplid);

You can manipulate the data before we insert to the staging record.

&DataRS(1).GetRecord(1).GetField(Field.EMPLID).Value; = "00000";

Once you are happy with the changes, copy data from rowset to your staging record.
In this case, MYSTAGINGRECORD is assigned to &DataREC.

&DataRS(1).GetRecord(1).CopyFieldsTo(&DataREC);
&DataSQL.Execute(&DataREC);

Don't forget to read the next line to continue the while-loop.

 &DataRS = &LocalFile.ReadRowset();
 
End-While;

This is an alternative way of reading the file.
Useful if you want to get the entire line, no mapping involved.

While &LocalFile.ReadLine(&strVar)
 MessageBox(0, "", 0, 0, "&strVar: " | &strVar);
End-While; 

And some closing stuff.

   MessageBox(0, "", 0, 0, "End of File.");
   
  End-If; /* If file is open */

 End-If; /* If file exists */

End-If; /* If file transfer is successful */

End-For;