Bulk Data Excel File Generate

Hello,


We are using a timer to save the binary data of an Excel File in a table. We did this because the the query gets the data used in the file took too much time (around 30s), which at the screen action would make time out, and the data that is utilized in the table is created through a timer everyday, so it sounded logical to put it in a timer. 


To Make the Excel File we are using an extension called OfficeUtils, to give a custom template to our Excel.

To give a gist of the idea, the action first will Run the query (to get the data), then will use the RecordListToExcel (From outsystems), Then will use Export_SetTable (From OfficeUtils), Then will use Export_SetTemplate (From OfficeUtils), Then will use Export_GenerateFile (From OfficeUtils), Finally we save the binary data that comes from Export_GenerateFile on a table.


The problem is on the action Export_GenerateFile, on the service center it appears the error "Error Executing Request" and the timer restarts just to fail again in the same action. We did this too other Excel Files (that were also quite large +- 1.7MB) and it worked well, and the only difference between them is the size. The size of the data we are trying to generate the file is around 15MB.


we tried to search for solutions but the only thing we found that could be applied to our problems was to turn the list into a CSV file and then turn the CSV file to Excel (we saw that in this issue Excel Export Bulk Data | OutSystems ), but the forge component we tried to use (Convert CSV File to Excel File - Overview | OutSystems ) gave error in the compilation and we couldn't use it.


Is there another way that we can generate the Excel File?



Samuel Alves


 


mvp_badge
MVP

Hi,

If the difference between the failed file and the successful processed file is just size, my first two guess es are 1) timer timeout setting is to small,. 2) data quality issue ( for example loading text in numeric attributes)

Check service center error log, there should be the error message that will give you a clue of what went wrong.

Regards,

Daniel



hello Daniel,


On the first point, I doubt the error is in the timeout. The timer has the default value of 20 minutes, and the timer restarts at a 1m:30s - 2m mark, so it never reaches the 20 minutes.


On the second point I went and double checked the assignment of the data, and the attributes seems to match. And I think if the problem was the data quality it would make an error before reaching the action Export_GenerateFile.


Also we have 3 environments, dev, qua and pdr where the dev is where we develop our functionalities, qua where we test the work done and pdr is the production environment. right now the code in the dev and qua is the same, but the error only occurs in qua (qua have way more data than dev) 


I attached a ZIP file with 2 images that have the errors that appeared in the service center, maybe it will help to identify the issue.


Thank you for your help

Samuel


EDIT: Added new info

Error.zip

mvp_badge
MVP

Hi Samuel,

As you say the code in DEV and QA is equal, and the problem occurs only in QA, that strengthens my assumption that it is the data you are processing. If you process the SAME data in DEV does it work?

If that is the case then there is something else wrong, the error details you shared report that there is a connection problem.

It says: An existing connection was foricibly closed by the remote host.

Regards,

Daniel

Hi Daniel


The problem that we have is the quantity of the data, at the moment, the same query returns 240 records in DEV, 130656 in QUA and 132000 in PRD. So its a bit hard for us to test the same data in DEV.

The query returns a table with 39 different attributes, in which 31 are type Text, 6 are Type Date, 1 integer and 1 is an identifier from a different table.

The majority of the attributes are Text because it was asked of us to replace the "." for "," and to show the value with the currency Symbol. So we have a lot of Replaces,  Concats and Rounds (because we needed to only have 2 decimals). We also have a Convert on the dates because it was requested the date to have a specific format. 

When we run the query it gives the results without problems, in all environments. So we don't known if any of this type of data manipulation could result in the error.

In terms of time, the query give the results instantly it takes 0 seconds in DEV,  in QUA it takes between 20-15 seconds, in PDR it takes between 15-13 seconds.


Thanks you for your support

Samuel Alves

mvp_badge
MVP

Hi Samual,

The volume of data isn't that much to justify the issues you have. I have processes way bigger excel files without problems using OutSystems. 

Again, you should see if on every run of the timer you get the same error, the previously shared error details mentioned:

It says: An existing connection was foricibly closed by the remote host. 


Hello Daniel

I've ran the timer several times and its always the same error.

To see if the error was from the data, we saved the formatted data in new attributes of the type text and used the  those attributes to create the excel. We also turned the data type of the excel template to text, so that the data  type was equal in all places. the error still persists. So we doubt the problem is in the data.

The error happens on the action Export_GenerateFile which turns the excel into binary Data. 

When we used the outsystems RecordListToExcel we could save the binary without a problem. But when we try to set a template (we even tried a blank template) the error occurs again in the Export_GenerateFile.



Hi Samuel,

1. If it is timer issue,  please follow this best practise for building timers: https://www.outsystems.com/training/lesson/1133/master-class-on-best-practices-and-timers

2.  Others, take an incremental debugging:

2.a Populate with small data, and incrementally  add the volumes to track the data integrity issues 

2.b Use fewer columns can identify the broken column

3.  I ever experienced a bad day when using these widgets:

when i copy an existing action, this is the space goat, and by deleting it and drag again, the problem is finished.

So, put breakpoints and check whether this is the cause in your problem.

4. Finding the heathy of your environment in lifetime (database is full or not, locking, et cetera) and may be some settings those are neglected could be the root cause of the different size and running environment problem.

best regard

Hello Indra,

what do you mean by broken columns? 


Thank you for your help

hi Samuel,

I meant by adding one column each debugging, you know which column value will be the error root cause.

regards

Hello Indra,


We have been running a few Tests on the timer.

We tried to add a column one by one while debugging but the timer gave an error by the 1m:30s mark. We have 39 columns and it didn't matter which column we had, as long as we were returning 1 column the error occurred. (Our SELECT looked a bit like  (SELECT X.X, '','','', ... FROM X JOIN Y ON ....))

Then we tried to return only blank field (we were returning '' in every column), but the timer gave Timeout 1 hour later (we upped the limit of the timeout to 1 hour). We only have 127080 records (The reason the records a different from the initial value of 130656 is because the source of the data is deleted then created again everyday through another timer) 1 hour should be enough, specially if we are only returning blank spaces.


Then we tried to increment the number of rows

The first time we gave a max records of 100 to the SQL and the timer worked, it took 2 seconds and created the binary for the excel.

The second time we gave a max records of 1000 to the SQL and the timer worked, it took 24 seconds to created the binary for the excel

The third time we gave a max records of 5000 to the SQL and the timer gave timeout, since it was such a low number of records we putted the timeout limit to 10 minutes, but it gave timeout.


The weird thing is that the timeout occurs on the action that generates the binary from the excel files.

If we use the binary data that comes from 'Records List to Excel' widget directly we have no problem (and it is quite fast, +- 40 seconds), but the moment we put the template on the errors start occurring. 


I uploaded the errors that that appeared to me at 1min:30s and at the 1 hour marks.

sorry the file wasn't there when i posted the response
Here a the prints

Errors.zip

Hi Samuel,

Do you really need to use an Excel template to download the data? From what I understood, I'm unable to understand why you really need it.

Have you tried to use the binary data from RecordListToExcel directly? You may want to try it, using an output structure with "friendly" attribute names for the colum headers on the Excel file created, so you can better use them later.

If you don't really need an Excel template, you may want to simplify your logic and dismiss the overhead of using it.

Best regards,

Paulo

Hi Paulo,


We wanted to use the template because we have several reports and all have templates, so we wanted this one to have a template too.

Since this issue is taking to much time we decided to remove the template for this report in particularly until we find a solution for this problem.


Regards

Samuel

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.