Input File using File Layout

The Input File

In this example the input file is a typical comma separated CSV. The requirement is to read the input file and with each line do some type of processing and determine if some of the data is needed to be written to the database. My example is a postal code CSV where we are only interested in a few of the columns.

CSV Example

PostalCode,City,Province,AreaCode,Latitude,Longitude,CityMixedCase 
"L7L 0A1","MYCITY","ON","905","47.386131","-76.774994","Mycity" 
"L7L 0A2","MYCITY","ON","905","47.416172","-76.821386","Mycity" 
"L7L 0A3","MYCITY","ON","905","47.380328","-76.764695","Mycity" 
"L7L 0A4","MYCITY","ON","905","47.374206","-76.760002","Mycity"

Record and File Layout

The first thing I created was a derived record MCM_CC_PSTCDWRK with only the fields I'm interested in from the CSV. I'm going to use postal code, mixed case city and the province. I used the following existing peoplesoft fields:

1. POSTAL
2. CITY
3. STATE

Now I created a File Layout Definition using this record.  In the file layout I add additional FileFields to fill in the for the unused data in the CSV.  Make sure your file layout is defined as a CSV format and if you file uses quotes around the data you need to setup a Default Qualifier as double quote in your file layout segment properties.  Because there are 2 city names in the file I put the matching work record city name in the position of the city name I intend to keep.  In my case the mixed case city position.

Peoplecode

Here we use the File.ReadRowset to get in row in the csv and I knew in my case there was always a header row at the beginning so before starting the loop I perform and extra read from the CSV.  Each data row is then copied to our derived record and only common named fields are copied.  Then we can process the record any way we want before getting the next row from the CSV.

Local File &PostalFile;
Local Record &rec = CreateRecord(Record.MCM_CC_PSTCDWRK);
Local string &sFileName;
Local Rowset &rowSet;

&PostalFile = GetFile(&sFileName, "R", %FilePath_Absolute);
If &PostalFile.IsOpen Then;
   If &PostalFile.SetFileLayout(FileLayout.MCM_CC_PSTCD) Then
      /* Skip the File Header row by reading 2 rows. */
      &rowSet = &PostalFile.ReadRowset();
      &rowSet = &PostalFile.ReadRowset();
      While &rowSet <> Null
         &rowSet.GetRow(1).MCM_CC_PSTCDWRK.CopyFieldsTo(&rec);
         /* some kind of &rec processing */ 
         updatePostalCode(&rec);
         &rowSet = &PostalFile.ReadRowset();
      end-while;
   end-if;
end-if;
&PostalFile.Close();