PeopleCode to load from CSV file and split 1 field into multiple columns

Question

I am not familiar with Application Engine or PeopleCode but inherited this project when someone left. Seems simple but I'm not sure how to approach it.

I have to load a CSV file that has 5 fields. The last field has multiple values separated by a comma and it is qualified with quotes.

file example:

ID   , YEAR, VALUE1   , VALUE2, CODE
87778, 2022, processed, none  , 100,40
93332, 2022, processed, none  , 60
76633, 2022, error    , none  , 55,35,9

I have created a File Layout definition and set the qualifier and I can load the file into a staging table but now I want to split the last column (CODE) into individual codes.

I have created 2 PeopleTools Record definitions with a parent/child relationship:

• parent Record definition with ID,YEAR,VALUE1,VALUE2, and
• child Record definition with ID,YEAR,CODE

I have found that I can use the PeopleCode split function to break the CODE column out into an array containing each value in an element. I'm not sure what the best way to structure the program is though.

• Is the staging table necessary?
• Or can I use the split function as I read the CSV file in and update the parent/child tables?
• Or do I need to keep the staging table and then read out the fields for the parent record and move them to the permanent table and then do the same for the child after using the split function and then loop through the array?

Just looking for some guidance so my first AE project is not a mess.

Answer 1

IMO, there are always multiple ways to achieve the same thing(especially in AE). we choose one based on our requirements and efficiency.

for staging table: In your case, you can ignore the staging table unless you are expecting to load a huge set of data every time or want to do parallel processing. In other words, you can have staging table if you think loading takes a lot of time and you don't want to risk failing that due to other errors.

You can even achieve this whole thing in one peoplecode action without a staging table.

or,

1. Load the data into staging table and commit.
2. loop through the data from staging table in AE (having the data in state rec)
3. Do the transformation as required using peoplecode action
4. insert data in necessary tables
5. update status(have a field in staging table) field in staging table, this may come in handy for any analysis/issue in production