<aside> 💡 If you need to read a large Excel file containing a lot of data, you should consider using the database actions instead of the usual Excel actions.
</aside>
Say you need to read a large Excel file containing a lot of data.
What most people will do is to make use of the following Excel actions:
One issue is that the processing speed tends to deteriorate the larger the Excel file gets. For example, it is not uncommon to take minutes to read an Excel file containing thousands of rows of data.
A speedier and simpler solution is to make use of the database actions instead.
First, we will use the Set variable action to define the full file path of the Excel file to be read.
Next, the action Open SQL connection will be used to open a new connection to a database, in this case, our Excel file.
Note that you will need to input the following as the connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%ExcelFile%;Extended Properties="Excel 12.0 Xml;HDR=YES";
💡Pro tip: If you encounter the error message “Can't connect to data source The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.”, it means you may need to install Microsoft Access Database Engine 2010 Redistributable first.
Then, use the action Execute SQL statement.
