If you want to read a csv file to PeopleSoft record in a sequential order you can use the following code to do that.
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.
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.
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.
