Skip to main content

 

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.

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"”

Can we delete the data from Excel using Excel as a database?

I tried different queries, but it didn't work. For now, I am copying the data that i wanted  into another sheet and doing the remaining data manipulation from the copied file. 

Could you please confirm whether it's possible to delete data directly from Excel using SQL queries? Any guidance or tips would be greatly appreciated.

What to do for xlsb files

Can we delete the data from Excel using Excel as a database?

I tried different queries, but it didn't work. For now, I am copying the data that i wanted  into another sheet and doing the remaining data manipulation from the copied file. 

Could you please confirm whether it's possible to delete data directly from Excel using SQL queries? Any guidance or tips would be greatly appreciated.

When connected to excel as a database You cannot use delete query

Reply


Terms & ConditionsCookie settings