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
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
    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
      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
        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
          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
            Posts: 3353
            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
              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
                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
                  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.