Hi there,
I have an excel file with almost a million records.
Here's what I did.
Upload the excel file as binary to a temporary table - > Trigger a timer to get the binary and convert to records -> save records to a Temporary Table with same columns.
However, it can handle that large amount of rows.
If I have a success on that, there is another timer that will run getting 1000 records each to save to respective tables. (This is working fine)
Is there a good way to do this? OML will be a great help.
Hello @Esrom Galang
I have not used it myself, but a popular choice appears to be the BulkInsert forge component. It comes with a demo as well, so I hope it is everything that you need.
Regards,
AJ
Hi Esrom,
That Forge component AJ refers to sounds like exactly what you need, but if you want to do it yourself, you can split your timer logic of your first job up in chunks of 1000 records as well.
All you would need for that is an extra control attribute for counting how many you have processed, you could have it in the entity where you store the full binary.
At end of each run of your first timer, as long as count < rows in the excel, update count and wake timer 1, else wake timer 2.
This is assuming the problem is with the million inserts, not with opening the excel.
You could also play around with doing commits after every 1000 records, but continue processing until you get close to some time limit before actually storing count, stopping and waking itself.
Dorine
Hello Dorine, 'will try this if the bulkinsert won't work.
Thank you!
The most performant and scalable solution is, if you combine both suggestions use the bulkinsert forge component in the solution that Dorine described.
Not sure if bulkinsert will handle insert of 1m records at once, it builds a list in memory before insert.