Skip to main content

Processing large Excel files, I’ve compared performance between Excel Basic and Excel as Database.

For the file I tested with, Excel Basic took 2 minutes to process. Excel as Database too 28 Minutes!

Anybody encountered this and found a root cause?

Office version: Office 365

AA Version: A360 cloud, Bot Agent version: 22.60.0

That’s interesting, 28 minutes is way too much. Can you share some more details?

What version of the database package is your bot using?

What type of activities are you doing to process each file? Select, insert, update, maybe SQL function or filters?

Are you using the 32-bit or 64-bit version of Office 365?

What does your connection string look like? (You don’t need to include the Data Source)

 

Some of these questions might not be entirely relevant but I can’t tell for sure, I’ve never seen such a huge performance difference between Excel Basic and the ODBC driver.


What version of the database package is your bot using?

 

5.4.0

What type of activities are you doing to process each file? Select, insert, update, maybe SQL function or filters?

 

Just a select of all rows and all columns. No filters.

Are you using the 32-bit or 64-bit version of Office 365?

 

64 bit

What does your connection string look like? (You don’t need to include the Data Source)

 

Provider=Microsoft.ACE.OLEDB.12.0;Data Source="$sFileAsXLSX$";Extended Properties="Excel 12.0 Xml;HDR=NO"

 

Where $sFileAsXLSX$ is the file name we’re trying to open.

 

Some of these questions might not be entirely relevant but I can’t tell for sure, I’ve never seen such a huge performance difference between Excel Basic and the ODBC driver.

 

Agreed. And to bolster your point: this huge discrepancy is only happening on one of the Bot Runners. 


Reply