Exporting summarised instances

VITOPIEPOLI

Forums

Good morning,
I have a summarisation report with 54 records each of those has a number of instances that renges from few thousands to more than two millions.
Now, what I need to do is to export each row in a separate csv or txt file that will be sent to the relevant audit unit.
Is there any script that could help me in having typefile needed and automate the export  process? 
Those 54 entities could become more than 100 at the end of the year and I need to automated the process.
Thank you very much for your help.
Vito

Brian Element Fri, 01/03/2020 - 06:54

Hello Vitopiepoli,

When you are talking about exporting are you talking about exporting the transactions?  So for the item that has two million transactions you would like to create a separate file and then save as a CSV?

I don't know of any scripts that would do this but I think one could be built fairly easily.  First step would be to use key value extraction to break-up the main file into its separate components.  The key value extraction has a limit of 500 items but since your is less than 100 there wouldn't be a problem.   Next step is to have a loop to export the files into a csv file.  Is this what you are looking for?

VITOPIEPOLI Fri, 01/03/2020 - 07:09

Hi Brian,
Yes you are correct on both points:
1) I would like to have a separate csv file for each row
2) I was just thinking about a recursive use of extractions from the summarised files but I am not sure how to get the data that actually makes it. I would like to use a "for i in .... then" type of function with ith being the name of entity the data are summarised over. Or if it is easier to run the "for i in ... then" for all the entities in the "entity name" field.
I am not so familiar with programming yet and less of all with Ideascripting.

VITOPIEPOLI Fri, 01/03/2020 - 08:09

Hi klmi,
In a way. I figured out how to import it correcly and now I am working with it.
Why?

klmi Fri, 01/03/2020 - 13:41

Why? Only to have a better idea what you are doing.
That means when you summarize that data by client / client ID with IDEA as a result you will find one row to each client / client ID, the number of items and the summed up amount.
Do you want to export only that single line for each client / client ID with the related amount OR do you need all the bookings per client / client ID? 

Brian Element Sat, 01/04/2020 - 15:08

Hi Vitopiepoli,

I put together a script for you.  It will break a file into its records based on a character field and then save each of those files to a csv file.  I think this is what you are looking for.

gamze.t Sat, 05/23/2020 - 08:47

Hi Brian,
I modified the script you shared according to my own scenario. What I am trying to do is summarising the general ledger by account number with its summation of balance. Then, I want to extract details of the specified account if its summation of balance greater than zero. I used another extraction function to use "greater than zero" criteria but I still need to use this criteria to write the result as greater than zero or not. In the Sub Main part, I could not write that properly. I got the Syntax Error related to any criteria with integer. Could you help me to find proper syntax for this issue?
Additional note: I am planning to use this script for multiple accounts when this problem is solved, so I did not cancel Array function in the script.
You may find my script attached and thanks in advance for your help.
Gamze

Brian Element Sun, 05/24/2020 - 15:41

Hi Gamze, since your syntax error references an integer problem, is the ACCOUNT field numeric?  If so they remove the "" around the ACCOUNT = as the double quotes are only needed if the field is anything but numeric.  Let me know if this is the problem.

gamze.t Wed, 05/27/2020 - 02:00

Account column is not numeric and the script is working in the way I shared with you. But, I got the syntax error when I tried to run "lRecords" part which is shown as a comment in the script. I want to count row numbers in EXTRACTION1 table and if there is a row, I want to populate message box. I guess I am missing something in that part and did not figure it out.