Read / Write CSV File PeopleCode Example

If you want to read / write comma separated file (CSV) using PeopleCode (Either in Page or Application Engine program), then this example is for you.

PeopleSoft does offer File layouts to parse CSV files in native format, but I would like to introduce you to an open source library, OpenCSV which can simplify the work for you. What's more, it is open source and you can use it on the fly with less Java knowledge.The tutorial assumes you have basic knowledge on PeopleCode. Let us get started.


1. Writing CSV File in PeopleCode


Let us assume you want to write the output of SQL query to CSV file for this example. We use CSVWriter class defined in au.com.bytecode.opencsv.CSVWriter for this purpose. Using the writeNext method on this class, you can write CSV data line by line from SQL cursor. This method takes a Java string array as input, so you have to pass an array to this suitably. The complete PeopleCode example to create CSV file is shown below:

/* Create CSV File in PeopleSoft - OpenCSV Example */

/*  Create java.io.Writer with CSV File Details */
Local JavaObject &ioWriter = CreateJavaObject("java.io.FileWriter", "PeopleSoft_CSV_File.CSV");

/* Create a CSV Writer Object Now */
Local JavaObject &CSVWriter = CreateJavaObject("au.com.bytecode.opencsv.CSVWriter", &ioWriter);

/* Write SQL Query for CSV Output */
Local SQL &dumpSQL = CreateSQL("SELECT OPRID||','||VERSION FROM SYSADM.PSOPRDEFN");

/* Fetch Data */
Local string &csvLine;

/* We create a Java Array that we can pass to CSV Writer */
Local JavaObject &Jarray = CreateJavaArray("java.lang.String[]", 1);

While &dumpSQL.Fetch(&csvLine)
   
   /* Push the input to Peoplecode Array */
   Local array of any &classListArray = CreateArrayAny();
   &classListArray.Push(&csvLine);
   /* Copy input to Java Array */
   CopyToJavaArray(&classListArray, &Jarray);
   /* Write Java Array to CSV File */
   &CSVWriter.writeNext(&Jarray);
   
End-While;
/* Close SQL Cursor */
&dumpSQL.Close();
/* Close CSV Writer */
&CSVWriter.close();

You can change the separator, SQL query, target CSV file location suitably by changing them in the program. This would be handy for AE programs.

2.Reading CSV File in PeopleCode


Reading and parsing a CSV file in PeopleCode program is a real piece of cake. No complex File Layout required. You start with a CSVReader object and pass the file name you want to parse into it. From here, you can read each line to a Java Array in PeopleCode, from there you can convert it into PeopleCode array and print on the screen or Application engine program. An example code that does this is shown below:
/* Read / Parse CSV File PeopleCode Example */

/* Read Input File through FileReader Object */
Local JavaObject &ioReader = CreateJavaObject("java.io.FileReader", "PeopleSoft_CSV_File.CSV");

/* Create a CSV Reader Object Now */
Local JavaObject &CSVReader = CreateJavaObject("au.com.bytecode.opencsv.CSVReader", &ioReader);

/* Create Java String Array that can read CSV Data */
Local JavaObject &Jarray;


/* Read File line by line now */
While True
   
   &Jarray = &CSVReader.readNext();
   
   /* You have the line in a Java Array now */
   
   /* You can now use CopyFromJavaArray and take it to PeopleCode Array */
   
   
End-While;
&CSVReader.close();

As an activity for you, I have left the last two parts blank - you just have to apply the reverse of write method that we saw above to get the file data into PeopleCode. Give a try and post us a comment if you are stuck.

Setup:
Just download openCSV and copy the JAR file to your classpath.

1 comment: