At HIUG Interact last week, a member asked one of my favorite questions:
"Does anyone know how to read binary Microsoft Excel files from PeopleSoft?"
Nearly 15 years ago my AP manager asked me the same question, but phrased it a little differently:
"We receive invoices as Excel spreadsheets. Can you convert them into AP vouchers in PeopleSoft?"
Of course my answer was "YES!" How? Well... that was the challenge. I started down the typical CSV/FileLayout path, but that seems to be a temporary band aid, and challenging for the best users. I wanted to read real binary Excel files directly through the Process Scheduler, or basically, with PeopleCode. But here is the reality: PeopleCode is really good with data and text manipulation, but stops short of binary operations. Using PeopleCode's Java interface, however, anything is possible. After a little research, I stumbled upon Apache POI, a Java library that can read and write binary Excel files. With a little extra Java code to interface between PeopleCode and POI's Java classes, I had a solution. Keep in mind this was nearly 15 years ago. PeopleSoft and Java were both a little different back then and today's solution is slightly simpler. Here is a summary of PeopleSoft and Java changes that simplify this solution:
- As of PeopleTools 8.54, PeopleSoft now includes POI in the App and Process Scheduler server Java class path. This means I no longer have to manage POI as a custom Java library.
How does a solution like this work? The ultimate goal is to process spreadsheet rows through a Component Interface. First we need to get data rows into a format we can process. Each language and operating environment has its strengths:
- PeopleCode can handle simple Java method invocations,
- Java is really good at working with binary files, and
- PeopleCode and Component Interfaces play nicely together.
My preference is to capitalize on these strengths. With this in mind, I put together the following flow:
- Use PeopleCode to process those rows through a component interface.
Did you see something on this blog that interests you? Are you ready to take your PeopleTools skills to the next level? We offer a full line of PeopleTools training courses. Learn more at jsmpros.com.