How to Convert CSV to XLSX in PeopleSoft Using the PSSpreadsheet Class

 Working with large datasets often requires transitioning data between formats. In PeopleSoft, you can efficiently convert a CSV file to an XLSX file using the PSSpreadsheet class. This guide will walk you through how to automate this conversion with a simple PeopleCode program.

Why Convert CSV to XLSX?

• Better Data Handling: Excel handles large datasets more effectively than CSV, especially when working with formulas, charts, and formatting.

• Improved Readability: XLSX allows for better organization with multiple sheets and styling options.

• Integration with PeopleSoft: The PSSpreadsheet class makes it easy to programmatically generate Excel files.

Prerequisites

• Access to PeopleSoft Application Designer

• Basic knowledge of PeopleCode

• Required permissions to run App Engine programs

Step-by-Step Implementation

1. Create an App Engine Program

Open PeopleSoft Application Designer and create a new App Engine program. Add a PeopleCode action where the conversion logic will be placed.

2. PeopleCode to Convert CSV to XLSX

 Run the App Engine Program

1. Save and register the App Engine.

2. Run it through the Process Scheduler or manually in PeopleSoft. Do not run in two tier app designer.

3. The Excel file (Output.xlsx) will be generated in the defined directory.

/* Set the file paths */
/* Hardcoded Input CSV File Path /

&csvFileName = "<Path_to_dir>test_csv.csv"; / Replace with actual CSV path */
/* Set Output Excel File Name */

&outFileName = "<Path_to_dir>Output.xlsx";/ *Replace with actual xlsx path */

/* Create Spreadsheet using PSSpreadsheet class */

Local object &ss;

&ss = CreateObject("PSSpreadsheet");

&ss.Open(&outFileName, True);

/* Open the CSV File for Reading */

Local File &file;

&file = GetFile(&csvFileName, "R", "A", %FilePath_Absolute);
If &file.IsOpen Then
   Local string &line;
   Local number &row = 1;
   Local array of string &columns;
   
   /* Read each line from the CSV */
   While &file.ReadLine(&line)
      /* Split the line by comma (CSV) */
      &columns = Split(&line, ",");
      
      /* Write data to Excel */
      Local number &col;
      For &col = 1 To &columns.Len
         &ss.SetCellString(&row, &col, &columns [&col]);
      End-For;
      
      &row = &row + 1;
   End-While;
   
   /* Close the file */
   &file.Close();
End-If;

/* Save the Excel file */
&ss.Save();