How often we have worked or seen a requirement to read/write data from or to a file. The file can be of any format , be it CSV,Fixed width, Excel or XML.
PeopleSoft provides us a peopletools object :”FileLayout ” which definitely does our work in most of cases and makes the read/write a much more simpler task.
But it is not compulsory that we have to use File Layouts when working with files.
When the requirement is simple like reading data from a .csv or any delimiter file and load into a table, we can forego File Layout and do our task.
This tutorials explains how can we read a .csv file without using File Layout.
Let’s take a hypothetical requirement:
Let’s suppose the “PS_SETID_TBL” where all Setids are stored iss loaded by file which comes from a third party. The third party send .csv file with all the details to be loaded in setid table.
The PS_SETID_TBL has five columns
So, our input file also has five columns and looks like the below set:
SETID , DESCR , DESCRSHORT , PARTIAL_SHARING , DESCRLONG
CAN0 , Canada Table Sets , Canada – 0 , N , NULL
CAN1 , Setid for CAN1 , Canada – A , N , NULL
CAN2 , Setid for CAN1 , Canada – B , N , NULL
CAN3 , Setid for CAN1 , Canada – C , N , NULL
CAN4 , Setid for CAN1 , Canada – D , N , NULL
CAN5 , Setid for CAN1 , Canada – E , N , NULL
CAN6 , Setid for CAN1 , Canada – F , N , NULL
CAN7 , Setid for CAN1 , Canada – G , N , NULL
CAN8 , Setid for CAN1 , Canada – H , N , NULL
CAN9 , Setid for CAN1 , Canada – J , N , NULL
CAN10 , Setid for CAN1 , Canada – K , N , NULL
The first row is optional, sometime we need header row to help the user identify the columns.
We will see how we can skip the header row and read the rows below.
We will create an application engine which when triggered will read the .csv file and then load it into the table.
We have added just one step and one action of peoplecode which will read the file and load the data into PS_SETID_TBL.
Now we have to execute certain steps to read and load the file.
1. Get the file from your client or server, you are either running the application engine from 2 tier or 3 tier and accordingly keep the file either on your local machine or server.
Here is the data which is in the file, I have remove the extra spaces.
SETID,DESCR,DESCRSHORT,PARTIAL_SHARING,DESCRLONG
CAN0,Canada Table Sets,Canada – 0,N,NULL
CAN1,Setid for CAN1,Canada – A,N,NULL
CAN2,Setid for CAN1,Canada – B,N,NULL
CAN3,Setid for CAN1,Canada – C,N,NULL
CAN4,Setid for CAN1,Canada – D,N,NULL
CAN5,Setid for CAN1,Canada – E,N,NULL
CAN6,Setid for CAN1,Canada – F,N,NULL
CAN7,Setid for CAN1,Canada – G,N,NULL
CAN8,Setid for CAN1,Canada – H,N,NULL
CAN9,Setid for CAN1,Canada – J,N,NULL
CAN10,Setid for CAN1,Canada – K,N,NULL
I will be running the AE from 2-tier .So, I kept the file in my local and will provide the path to the Peoplecode.
To Retrieve a file from a path we use the “GetFile” function.
The GetFile function takes the file’s path and mode in which we want to pull.
We also pass 2 more parameters to tell whether the file is for reading or Writing and its path type.
Reading: R
Writing: W
In this case we only want to read the file so we will pass the file’s absolute path and R as the parameter.
Local File &FileSetid;
&FileSetid = GetFile(“c:tempsetid.csv”, “R”, %FilePath_Absolute);
Be careful about the file and parameters passed.
The parameters values can be passed in multiple ways in different scenario.
2. Read the file row by row till the end.The ReadLine() function.
We will use the ReadLine() function to read the file row by row.
ReadLine() function reads a row of data as a single string object and move the cursor the next line.
We can use the while loop as below read the file row by row till the end of data.We can ignore the first line which contains the header values by simply calling the ReadLine function once .
&FileSetid.ReadLine(&RowSetid);/*Ignore first header row*/
/*Start from 2nd row which contains data*/
While &FileSetid.ReadLine(&RowSetid);
End-While;
3. Split each row into respective columns.
The ReadLine() function reads the complete row of data at once.
So, in order to retrieve all our column values , we need to split the row into columns.
We may use the Split() function here.
Split() function takes two parameters, the string to be splitted and separator by which the string should be splitted and returns an array of splitted string.
Split(string, separator)
Our row of data is separated by a ‘,’ so, we will pass comma as separator.
&Columns_array = Split(&String, “,”);
So, our above code will be changed to :
&FileSetid.ReadLine(&RowSetid);/*Ignore the first line*/
While &FileSetid.ReadLine(&RowSetid);
&Columns_array = Split(&RowSetid, “,”);
End-While;
&Column_array is an array which contains all the columns for a particular row.
4. Load the data into SETID_TBL from the array.
The easiest way we can do is by writing a SqlExec and passing these array values to the SQLExec.
While &FileSetid.ReadLine(&RowSetid);
&Columns_array = Split(&RowSetid, “,”);
SQLExec(“INSERT INTO PS_SETID_TBL (SETID,DESCR,DESCRSHORT,PARTIAL_SHARING,DESCRLONG) VALUES (:1,:2,:3,:4,:5)”, &Columns_array [1], &Columns_array [2], &Columns_array [3], &Columns_array [4], &Columns_array [5]);
End-While;
So, the whole peopleocode will be as below:
Local SQL &SQL;
Local File &FileSetid;
Local array of string &Columns_array;
&FileSetid = GetFile(“c:tempsetid.csv”, “R”, %FilePath_Absolute);
While &FileSetid.ReadLine(&RowSetid);
&Columns_array = Split(&RowSetid, “,”);
SQLExec(“INSERT INTO PS_SETID_TBL (SETID,DESCR,DESCRSHORT,PARTIAL_SHARING,DESCRLONG) VALUES (:1,:2,:3,:4,:5)”, &Columns_array [1], &Columns_array [2], &Columns_array [3], &Columns_array [4], &Columns_array [5]);
End-While;
Lets run the Application Engine.
Backend Values after the run:
And That’s it,, In few lines of code we have read a file and loaded into a physical table.
With use of only three functions Getfile (), ReadLine () and Split () , it was indeed a cakewalk.
The above code can be customized to read any delimited file, FIxed width file or XML file.
We may have to use some other way to split those kind of files.
