• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Integration / Customization IPA/ProcessFlow

Sorting and summarising a csv file in IPA

 8 Replies
 2 Subscribed to this topic
 52 Subscribed to this forum
Sort:
Author
Messages
Quarantine
Basic Member Send Private Message
Posts: 24
Basic Member
5/21/2020 11:57 PM
As part of our migration to Cloudsuite we need to convert a 4GL program that reads a CSV file, sorts it by certain fields like company and accounting unit and then passes the summarized data for that company and accounting unit etc as journal entries into Cloudsuite.

This was very simple in 4GL but not sure how to do this in IPA. Any other way using a Infor provided tool that can do this like application studio or birst is also welcome.
David Williams
Veteran Member Send Private Message
Posts: 1127
Veteran Member
5/22/2020 1:10 AM
If you load the file into memory with a File Access and Assign it to a variable, you can use JavaScript to .sort() the data. Without additional keys it will sort by the field order of the data (field 1, field 2, etc.). I think it requires a custom function to sort by specific fields (you can search online), but if your data can be sorted by the order of the fields in the file; then the simple sort will do the trick.
David Williams
David Williams
Veteran Member Send Private Message
Posts: 1127
Veteran Member
5/22/2020 1:16 AM

I forgot to mention that you need to split your data file by line first. c=csv.split("\n" )

David Williams
Quarantine
Basic Member Send Private Message
Posts: 24
Basic Member
5/22/2020 3:52 AM
The file in question has a huge volume like upto half a million transactions in one run. I tried storing it in File storage by copying and pasting on screen and it hung the browser while it did copy it completely.

Does a custom sort handle such huge data? That's why I was wondering if Birst or application studio was the answer
ION or IPA don't seem to the ideal case for this issue.
Kristi_ANTHC
New Member Send Private Message
Posts: 3
New Member
6/1/2020 4:36 PM
If it were me, I might try a Perl script to do the sorting and write the results to a newly sorted csv file. In theory an IPA could call the Perl script with a SysCommand node, Perl could do the sorting work and write the results to the file system, then a File Access node could pick up the results and complete the processing.
John Henley
Send Private Message
Posts: 3351
6/1/2020 4:43 PM
Depending on which cloudsuite access to syscommand node may not be available.
Thanks for using the LawsonGuru.com forums!
John
David Williams
Veteran Member Send Private Message
Posts: 1127
Veteran Member
6/1/2020 5:00 PM
You could create a custom business class and use IPA to run the database import and then have the flow read through the records to process your JE's.
David Williams
pjnowak
Send Private Message
Posts: 3
6/1/2020 5:27 PM

If your platform is Windows, You can run the Powershell command in a SysCommand node.

e.g. - read inputfile.csv and write to outputfile.csv with header record = "a,b,c,d,e" and sort by c as [int]

  • import-csv -header a,b,c,d,e inputfile.csv | sort {$_.c -as [int]} | %{write-output ($_.a + "," + $_.b + "," + $_.c + "," + $_.d + "," + $_.e) } | out-file outputfile.csv -encoding ASCII
Quarantine
Basic Member Send Private Message
Posts: 24
Basic Member
6/8/2020 1:29 PM

We are on MT. We decided to sort and summarize outside of  IPA using PL/SQL and trigger the flow via a File Channel to load the JEs into CSF.