Take data from Excel and pass in SQL Extract Query

I am trying to automate a report generation i want to get the input data from en excel sheet column and pass this in the SQL query

Steps Performed
Step 1 – Read the data from excel and store in datatable “account”
Step 2 - Database Connection done

Steps Failed
Step 3 - Use Execute Query and pass the unique emp_id in the sql query

Query : select * from emp where emp_id in (data from datatable)
Note : emp_id is of number data type in DB
Sheet :

Capture

Hi @Muhammad_Abdul_Basee

Use

“Select * from emp where emp_id='+Row(EmpID”).ToString+'"

Thanks
Ashwin S

“Row” which you have mentioned is it the output variable of the read range activity of excel

hi @Muhammad_Abdul_Basee
Yes based on for each i have passed it as it is

Thanks
Ashwin S

I am getting the below exception

Note : emp_id is a an number datatype in DB and i have multiple emp_id in sheet

RemoteException wrapping System.Data.OracleClient.OracleException: ORA-01722: invalid number

Server stack trace: at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleDataReader.ReadInternal() at System.Data.OracleClient.OracleDataReader.Read() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.LoadAdapter.FillFromReader(DataTable dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) at UiPath.Database.DatabaseConnection.ExecuteQuery(String sql, Dictionary`2 parameters, Int32 commandTimeout, CommandType commandType) at UiPath.Database.Activities.ExecuteQuery.<>c__DisplayClass38_0.b__0() at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object args, Object server, Object& outArgs) at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)Exception rethrown at [0]: at UiPath.Database.Activities.ExecuteQuery.EndExecute(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result) at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)

Solution :
Step 1 – Read data from excel column and assign a output variable – Read Column Activity
Step 2 – Convert the ienumerated output variable to comma separated string – Assign Activity
Step 3 – Connect to database – Connect Activity
Step 4 – Pass the string variable to the extract query – Extract Sql

SQL – “select * from abasir.profile_correction09042019 where account_id in (” + strAccountID+ “)”
strAccountID – converted string variable

Attached Robot for details databaseConnection.zip (25.1 KB)

1 Like

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