Take data (unique IDs in a column) from excel and pass as parameters in SQL Query (Execute Query)

excel
database
studio

#1

Hi All,

I have an excel spreadsheet with a customer ID column. I need to take distinct customer IDs from excel and use these values in the filter part of the sql query to determine if any of the IDs are invalid.

Below are the steps that I wish to perform:

  1. Stored excel data in a datatable ‘dt’
  2. Connected to the database
  3. Use Execute Query and pass the unique customer IDs from dt as parameters. Below is the query:

“SELECT column1, column2 FROM tablename where column1 in (@custId)”

  1. Created 2 parameters:

image

  1. Create a separate datatable with only those rows that has a valid customer id ( use the result from the query)

I am stuck on step 3 and the query does not seem to work. How do I pass the customer IDs in the sql statement?

Appreciate your help. Thanks!


#2

You can make use of Argument and local variable to achieve this.


How to Pass Parameter in Database Activity
#3

@Jishnu_Kumar

I need to pass a list of IDs (For example: The query should look like “SELECT column1, column2 FROM table1 where column1 in (‘44895’,‘13424’,‘52454’,‘45345’)”

Hence, I am using DataRow as the data type for the parameter and not string. Getting the below error while executing the query in uipath:

image


#4

@whyyouandi @vvaidya Will you guys be able to help with this? Appreciate your help. Thanks!


#5

Hi @oshinkavdia

You can write code like that

Declare @Query VARCHAR(8000)

Set @Query = 'SELECT * FROM tablename WHERE colum IN(' + @value1 + ', ' + @value2 + ')'

EXECUTE( @Query )

Assign it to a variable as the sql text.
Then in [Execute query] activity, set above variable to the input sql.
Notice: above sql can run correctly in SQLSERVER.


Create SQL query parameters dynamically
#6

Hi oshinkavdia,

Could you share your workflow which takes the data from excel and pass as parameters in SQL Query.