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.

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.
      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.

      Provider=Microsoft.ACE.OLEDB.12.0;Data Source="full path to your xlsx file goes here";Extended Properties="Excel 12.0 Xml;HDR=YES"

      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.

      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:

      

      will work this connection string for 64 bit excel?

      I have office 2021 installed on my machine. and I also tried installing latest Microsoft access DB engine 2016. I tried below connection string

      Provider=Microsoft.ACE.OLEDB.16.0;Data Source="full path to your xlsx file goes here";Extended Properties="Excel 16.0 Xml;HDR=YES"

      But its not working and I am getting error as

      Could not Connect to Database. Provider cannot be found. It may not be properly installed. Location: ExcelAsDatabase

      Can you please help with this

      I have office 2021 installed on my machine. and I also tried installing latest Microsoft access DB engine 2016. I tried below connection string

      Provider=Microsoft.ACE.OLEDB.16.0;Data Source="full path to your xlsx file goes here";Extended Properties="Excel 16.0 Xml;HDR=YES"

      You may be running 64-bit drivers. So try ticking the ‘Use ODBC 64-bit driver for connection’ in the Database: Connect command.

      My connection string remained: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PATH_TO_EXCEL_File;Extended Properties="Excel 12.0 Xml;HDR=YES"”