PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 06/08/2020 9:29 AM by  Quarantine
Sorting and summarising a csv file in IPA
 8 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Quarantine
Developer
Private
Basic Member
(27 points)
Basic Member
Posts:13


Send Message:

--
05/21/2020 7: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
    Private
    Private
    Veteran Member
    (3307 points)
    Veteran Member
    Posts:1113


    Send Message:

    --
    05/21/2020 9:10 PM
    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
    Private
    Private
    Veteran Member
    (3307 points)
    Veteran Member
    Posts:1113


    Send Message:

    --
    05/21/2020 9:16 PM

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

    David Williams
    Quarantine
    Developer
    Private
    Basic Member
    (27 points)
    Basic Member
    Posts:13


    Send Message:

    --
    05/21/2020 11:52 PM
    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
    Senior Computer Programmer Analyst
    Private
    New Member
    (4 points)
    New Member
    Posts:2


    Send Message:

    --
    06/01/2020 12: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
    Private
    Private
    Senior Member
    (9794 points)
    Senior Member
    Posts:3282


    Send Message:

    --
    06/01/2020 12:43 PM
    Depending on which cloudsuite access to syscommand node may not be available.
    Thanks for using the LawsonGuru.com forums!
    John
    David Williams
    Private
    Private
    Veteran Member
    (3307 points)
    Veteran Member
    Posts:1113


    Send Message:

    --
    06/01/2020 1: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
    Application Analyst
    Private
    New Member
    (6 points)
    New Member
    Posts:2


    Send Message:

    --
    06/01/2020 1:27 PM

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

    • https://stackoverflow.com/questions/2917535/powershell-read-text-regex-sort-write-output-to-file-and-formatting

    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
    Developer
    Private
    Basic Member
    (27 points)
    Basic Member
    Posts:13


    Send Message:

    --
    06/08/2020 9:29 AM

    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.

    You are not authorized to post a reply.