Some queries for an excel database connection do not work

Help
1

I am facing issues while trying to query data from my excel “.xlsx” spreadsheet.

I have used a connect to database activity with the following connection string and provider name…

Connection String: “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ExcelDBPath & “;Extended Properties=‘Excel 12.0 Xml;HDR=YES;IMEX=1’;”
Provider: “System.Data.OleDb”

The ExcelDBPath variable is a string with the value: “XLDB.xlsx”. The excel is just a simple practice excel file with two tables, ‘people’ and ‘pets’.

This connection appears to work fine.

I then attempted to query the results from the database using an ‘execute query’ activity with the following query string: “SELECT * FROM pets”
This functioned as expected and returned all the rows and columns from the ‘pets’ table.

I then tried the same thing with the ‘people’ table instead and to my surprise no rows were returned, only column headers. I’ve also tried putting the table names in square brackets such as ‘[people]’.
I tried remaking, renaming and duplicating the table, deleting the whole excel and recreating it, making new tables and attempting to query them… But so far the only table I’ve been able to perform a select query on has been the pets table.

The only explanation I can come up with is that my connection string or provider name is wrong. I’ve tried looking around for other ones but I haven’t found another one that will work. But of course I’m sure its going to be some other silly issue that I’ve overlooked.

This is my first attempt at using excel as a database with UiPath and I have limited experience with connecting to databases in general, so any help would be greatly appreciated.

I’m using UiPath studio version 2018.2.3

Here is my .xaml file:
excel_DB.xaml (8.5 KB)

Here is my .xlsx file:
XLDB.xlsx (9.3 KB)

Thanks in advance!

2

Problem in your excel file.

Range named “people” related to headers only. You can reassign this range to full table or just use sheetname in your query. Change it to something like that: “select * from [people$]”

It’s shame that excel table cannot use directly in this kind of query.

1 Like
3

Thank you very much!

“select * from [people$]”
This seems to have solved my problem. But I’m not sure I understand why…
I am actually doing just what you said already “or just use sheetname in your query” the sheets are named people and pets. It seems very strange that one of them works and the other doesn’t given that there’s no difference between the two except the name.

4

If you are using query = “select * From [people]”. you are asking select from named range.
If you are using query = “select * from [people$]”. You are asking select from full sheet.

2 Likes
5

Oh I understand now.

I wasn’t aware of the existence of named ranges in excel until now. The people range only included the headers of the people table but the pet range included the whole table. Thanks again for your help.