QuickTip: Connect to an Excel Spreadsheet As a Database
In this session, we’ll dive into connecting to an Excel Spreadsheet using the database package – especially useful when working with large Excel datasets.
Know Your Options
There are several packages available for working with an Excel file
Excel Advanced – great for advanced spreadsheet manipulations. This package has the most actions by far, and is great for taking advanced operations on your spreadsheets.
Excel Basic – great for quick operations. This package is great because it has no requirement for Excel to be installed on the machine – note that it only works on xlsx files though.
Database Package – great for working with huge datasets. While its primarily designed to connect to actual databases, it works in this case because of the Microsoft Access driver being used. Of the available options, this has the least “Excel Friendly” packages – and is more for users who are comfortable writing their own SQL queries.
Note that this would also be required for any bot runners that would ultimately be running this bot – something to keep that in mind as you plan for migrations.
Connect and Execute Your Query Statement
When connecting to the data-source – use the Database Connect action setting the Connection mode to Default.
In the connection string field, use the following as a template – entering the full path to your xlsx file for the Data Source parameter:
Provider=Microsoft.ACE.OLEDB.12.0;DataSource="full path to your xlsx file goes here";ExtendedProperties="Excel 12.0 Xml;HDR=YES"
Once the connection has been established – use the various database package actions to interact with your data. For the video example, the “Export to data table” action was used in conjunction with a SELECT statement to return the results of the query as a data format that easy to use within the bot.
Looking to dig deeper into connecting to an Excel file as a database? Check out the developer portal article on Working with Large Excel Datasets in a Bot to learn about the speed testing that was done in connecting to the same spreadsheet to solve the same problem in multiple ways. Additionally, check out the Excel as a DB GitHub project for sample code on connecting to your own spreadsheet as a database.