Excel ADODB with Execute Query

studio
query

#1

Hi,

I want to download data from Excel Using ADODB, but I’m struggling with connection. I’m using Execute Query with following parameters:
ConnectionString - “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + ArchivePath + "; Extended Properties=Excel 12.0 Xml;HDR=YES; "
SQL - “select * from [OldCases]”

The problem is that I receive Validation error - “None of the overload groups have all their required/optional activity arguments configured.”

When I add Provider Name - “Microsoft.ACE.OLEDB.12.0” validation error disappears, but now I get

image

I will be really grateful for any suggestions.


#2

@heynow check input and output variables, excel path and data once.


#3

I have only SQL statement in Input, parameters are empty, output is DataTable that is not initialized before.

Sheet name and path are correct, ReadRange works with the same data.


#4

@heynow what OldCases here refer in below query


#5

Sheet name, it’s correct.


#6

@heynow try below one once and let me know

“select * from [OldCases$]”


#7

Still getting Object Reference error.

I think main question is what should be typed into ProviderName field, Microsoft Ace is not available in UiPath dropdown.

I tried to do this in Invoke Code Activity, but I get deserialize JSON error.

Result = New DataTable()
Dim conn As New OleDb.OleDbConnection
Dim comm As New OleDb.OleDbCommand
Dim da As New OleDb.OleDbDataAdapter
conn.ConnectionString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + Path + “; Extended Properties=Excel 12.0 Xml;HDR=YES;”
comm.CommandText = “select * from [OldCases]”
comm.Connection = conn
da.SelectCommand = comm
da.Fill(Result)


#8

@heynow Hi i don’t know what exactly wrong with your code, but i suggest alternate connection string and provider name

connection string

“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & ExcelPath & “;Extended Properties=‘Excel 12.0 Xml;HDR=YES;IMEX=-1’;”

Provider name

“System.Data.OleDb”


#9

Getting Object reference error.


#10

@VasuNaidu Set default value for datatatable variable as New DataTable().

Try above once it may solve ur problem.


#11

I’m not able to read the .xls files using OLEDB provider. How to read such files without using Excel Application Scope activity?


#12

@VasuNaidu Sorry i dont have idea bro.