Execute Query Error: Undefined function 'convert' in expression

Help Studio
, , ,
1

Hi, I am using an excel file as database.

“Execute Query” activity is throwing the following error.

ERROR [42000] [Microsoft][ODBC Excel Driver] Undefined function ‘convert’ in expression.

Connection string - “Dsn=Excel Files;dbq=”+str_filePath+“;defaultdir=C:\FinalOutput;maxbuffersize=2048;pagetimeout=5”

Provider: “System.Data.Odbc”

Query: “Select top 100 [ABC], convert(datetime, [XYZ DATE], 101) from [Report$] where [ACCOUNT] like ‘%1200400%’ or [ACCOUNT] like ‘%1201800%’ or [ACCOUNT] like ‘%1202500%’ or [ACCOUNT] like ‘%1201100%’ or [ACCOUNT] like ‘%1152600%’ or [ACCOUNT] like ‘%1151900%’”

What am I missing here?

TIA.

2

Use the Read Range activity to read the Excel file into a datatable. Execute Query is used for running queries against a database, rather than reading from a file.

3

May i know Why you use Execute query for Excel ?

4

Hi, Actually there are over 500,000 records in the excel file. Hence I thought I’d use queries here. Besides, the file is of .xlsb extension.
Will read range activity be efficient here?

Thanks.

5

Read range will be slow if you have many columns. There is no local solution for this. If you need to speed up reading of the data, I recommend having a process set up agnostic of UiPath which loads the data to an SQL datatable and reading it from there.