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
Save and register the App Engine.
Run it through the Process Scheduler or manually in PeopleSoft. Do not run in two tier app designer.
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();
