Importing large excel file

0
Hello, I am importing an excel file of 1.4MB size (2000Rows X 1500Columns). For this import, I am using advanced excel module with customized ExcelToList java action to import data cell wise.    If I import the first 10 rows with all 1500 columns, it is taking 30 seconds. If I import the first 100 rows with all 1500 columns, it is taking around 15 minutes. It is taking more than 10hrs to import the whole file.   In this case the time is increasing exponentially as the file size increases. Dear reader, do we have any approach/activity to reduce the time for importing  whole file without splitting the excel into multiple excel files?  
asked
1 answers
0

My guess is that the reason for the exponential time increase is that the whole file is being imported in a single transaction, so the database server is getting bogged keeping track of a very large transaction.

 

I would try one of the CSV import modules in the Marketplace.  This one:  https://marketplace.mendix.com/link/component/108605 enables you to read and process a CSV file line by line which means you can process each row in a separate transaction to improve performance.  It will take a bit of work, but I think this is the way you’ll need to process this file.

answered