QuickTip: Connect to an Excel Spreadsheet As a Database

  • 14 September 2020
  • 2 replies
  • 8974 views

Userlevel 6
Badge +9

 

In this session, we'll dive into connecting to an Excel Spreadsheet using the database package - especially useful when working with large Excel datasets.

 

Video Recap

  1. Know Your Options
    1. There are several packages available for working with an Excel file
      1. 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.
      2. 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.
      3. 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.
  2. Get the Driver
    1. In order to connect to a spreadsheet using the Database package - be sure that you have 32-bit version of the Microsoft Access Database Engine 2010 Redistributable installed.
    2. 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.
  3. Connect and Execute Your Query Statement
    1. When connecting to the data-source - use the Database Connect action setting the Connection mode to Default.
    2. 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;Data Source="full path to your xlsx file goes here";Extended Properties="Excel 12.0 Xml;HDR=YES"
       
    3. 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.

Resources

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.


2 replies

Userlevel 2
Badge +8

Amazing article! Definitely a very powerful asset for automations. However, I must mention that the installation of 32-bit Microsoft Access 2010 Database Engine might not be necessary in all cases, e.g. if you run on VMs that 32-bit “Microsoft 365 Apps” are installed, this installer inherently installs a 32-bit odbc driver that can be used by bots (same connection string, same functionality), see screenshot:

 

Badge

will work this connection string for 64 bit excel?

 

Reply