Question

Documentation regarding which SQL commands to use when accessing an Excel Sheet as a Database

  • 28 December 2023
  • 2 replies
  • 63 views

Badge +1

Hello everyone!

 

I am trying to use the MIN() and MAX() SQL commands using an excel sheet as my database, however i cant seem to make it work, my bot returns a wrong syntax error.

 

I was wondering if there were any links or official documentation regarding which SQL commands are confirmed to be working when used to acces excel sheets as databases, as im very interested in using this approach on a large bot.

 

Thanks!


2 replies

Badge +3

Hello everyone!

 

I am trying to use the MIN() and MAX() SQL commands using an excel sheet as my database, however i cant seem to make it work, my bot returns a wrong syntax error.

 

I was wondering if there were any links or official documentation regarding which SQL commands are confirmed to be working when used to acces excel sheets as databases, as im very interested in using this approach on a large bot.

 

Thanks!

Hi @NicolasFernandez1 ,

Here are some links for Excel as a DB and DB command.

Share the screenshot of the query so that I can get more idea if there is any issue with the query.

Database command (automationanywhere.com)

Using Connect action for database (automationanywhere.com)

QuickTip: Connect to an Excel Spreadsheet As a Database | Community (automationanywhere.com)

Example of migrating data from Excel to a database (automationanywhere.com)

Badge +1

Hello Syed,

 

Thank you for the links you’ve provided, I got very useful tips from them.

Regarding my query, I was writing a SELECT statement with the MIN and MAX operators, like so:

 

SELECT MIN(Banking Total) FROM [Sheet1$$]

 

But the Query was returning a Syntax error, I tried looking around on forums but found no concrete reference to making MIN() or MAX() work on AA360 using Excel as a database, so I dont think its possible to use them at the time.

In my use case, I needed a fast way to get the Maximum and Minimum ammounts on a given Excel column that had only numbers in order to set a filter, after some investigation and giving up on using MIN() MAX() I changed my approach and used an SQL Export to Database action with a SELECT ORDER BY and the  ASC and DESC operators, then set the maximum volume of records to fetch a single record from the query, like this:
 

SELECT [Banking Total] FROM [Sheet1$$] ORDER BY [Banking Total] DESC

 

After that, I just used a variable operation to assign the single record from the datatable I got from the query to a variable and finally get my Maximum and Minimum ammounts.

I have read that the SQL Querys you can make are very limited for Excel as DB at the moment, so I hope this post can be helpful to others!

Reply