How To get list of sheets in excel using a query

Help Studio
, ,
1

Hello guys,

Before now i used this query to read data from large excel files “Select * from [”+ in_CompilationSheetName+“$]” and i use the following to connect to the excel using uipath data base activity “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=”+ExcelFilePath+“;Extended Properties=”“Excel 12.0 Xml;HDR=YES”“”, this works because i have Microsoft access installed in my system.
Now my issue is that i am looking for a query that could get the list of sheets in the excel.

Thanks a lot .

Regards

2

A simple way would be to use an Excel Scope activity and point to the file you want then in the Output property you use Control+K to create a variable for your Workbook and inside the activity you can get the Sheet names by using assign activity and the function: varMyWorkbook.GetSheets().

1 Like
3

hello thanks, but the excel is so large and the excel scope takes a lot of time . that is why I started using query to read the excel, and the query is really fast.
But now i need to get the list of sheets using the query too

4

It takes a lot of time without any activity inside?

1 Like
5

Yes just trying to open the excel takes a lot of time

6

This is what my new sequence to read Large excels looks like image ReadLargeExcel (1).zip (18.2 KB)

7

I will do a little test and be right back. This how i could do, without uipath connection activity (maybe you can adjust if want to use it):
1.
myConn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\UserData\Downloads\Busca_Email_20200708_2017.xlsx;Extended Properties='Excel 12.0 Xml;HDR=YES';")
2. Invoke Method
Target: myConn
Method: Open
3.
myDataTable = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, Nothing)
4. For Each Data Row
4.1 varSheetName = row("TABLE_NAME").ToString

1 Like
8

Hello, thank you bcorrea… can you please share me your sample sequence , i am having some validation errors -

  • New cannot be used on an interface
  • GetOleDbSchemaTable is not a member of UiPath.database.databaseConnection.
9

This is the connection class to be used System.Data.OleDb.OleDbConnection and not uipath.database.databaseconnection

1 Like
10

okay thank you one of the errors are gone . now i have OleDbConnection is Ambiguous imported from a namespace or type ‘System.data.oleDb’ image

11

use like i showed above…

1 Like
12

Oh thanks it worked but the data table is empty

image
image1425×763 214 KB
whereas the excel has 3sheets

13

I used a xlsx file and all worked fine for me…

1 Like
14

Heyyyy Bcorrea it worked , it didn’t work the first time because i was using the wrong excel path . I am really grateful

1 Like
15

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.