How to read a field from a CSV file in to a PeopleSoft record (not in order) using File Layout

If you want to read a csv file to PeopleSoft record in a sequential order you can use the following code to do that.

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &STRING

&MYFILE = GetFile("c:\temp\vendor.csv", "R", %FilePath_Absolute);
&REC = CreateRecord(RECORD.MY_TEST_RECORD);
&ARRAY = CreateArrayRept("", 0);

If &MYFILE.IsOpen Then
   If &MYFILE.SetFileLayout(FILELAYOUT.MY_TEST_FILELAYOUT) Then
      While &MYFILE.ReadLine(&STRING);
         &ARRAY = Split(&STRING, ",");
         For &I = 1 To &REC.FieldCount
            &REC.GetField(&I).Value = &ARRAY[&I];
         End-For;
      /* do additional processing here for converting values */
         &REC.Insert();
      End-While;
   Else
      /* do error processing - filelayout not correct */
   End-If;
Else
   /* do error processing - file not open */
End-If;

&MYFILE.Close();

However, let's assume that you want to read a particular field or few fields from a CSV file to a PeopleSoft record. Then you can use another version of the above code. Here we can use an array object to store all the fields from the CSV and then you can use array index to find the particular field you are looking. Then, you can use SQLExec to insert or update that PS record you want.

Local File &MYFILE;
Local Record &REC;
Local array of string &ARRAY;
Local string &STRING
Local string &2ndFieldFromCSV

&MYFILE = GetFile("c:\temp\vendor.csv", "R", %FilePath_Absolute);
&REC = CreateRecord(RECORD.MY_TEST_RECORD);
&ARRAY = CreateArrayRept("", 0);

If &MYFILE.IsOpen Then
   If &MYFILE.SetFileLayout(FILELAYOUT.MY_TEST_FILELAYOUT) Then
      While &MYFILE.ReadLine(&STRING);
         &ARRAY = Split(&STRING, ",");
   /* lets assume that you want 2nd field from the csv file*/
          &2ndFieldFromCSV = &ARRAY[2];

      /* do additional processing here insert, update etc.. */
         SQLExec("UPDATE PS_MY_TABLE SET FIELD2 = &2ndFieldFromCSV WHERE PK_FIELD1 = 'SOME VALUE') ;
      End-While;
   Else
      /* do error processing - filelayout not correct */
   End-If;
Else
   /* do error processing - file not open */
End-If;

&MYFILE.Close();

Obviously, there are many ways to do the same thing in many different ways. But, leaveraging PeopleSoft File Layouts do the job will be much easier.

SHARE

Ayesha Wee

• 
• 
• 
• 
•