Assign DataTable individual cells to variables?

datatable
excel
uiautomation
text

#1

Hello,

I have an Excel file from which I want to read individual cells in a loop and combine content into a text file.

For example, I’d like to assign cells to variables: A1=var1, B1=var2, C1=var3 and write a text file that combines the variable in this manner: var1 + “and” var2 + “or” + var3.
I want this process to be repeated in a loop for any number of rows in the Excel spreadsheet (A2=var1, B2=var2, C2=var3 etc.), appending another line in the .text file for each row.

I am currently reading the full range from Excel but I can’t assign individual cells to different variables to combine them in the text file in the correct order.

Could anyone provide suggestions on how to tackle this? Please be as descriptive as you can as I am quite new to UI Path.

Thanks for your help.


#2

@bellucci.l, if you are intended to combine all column values from a row then u can try row.ItemArray() method
Follow these steps,

  1. Run a For each row row in datatable dt
  2. Inside the body, assign and keep on append the string
    String value=value+String.Join(“and”,row.ItemArray())
  3. Finally write this string variable value to a text file.
    IMPORTANT NOTE
    Output will be like text1 and text2 and text3
    and not as expected like text1 and text2 or text3
    Regards,
    Dominic :slight_smile:

#3

Dominic, thanks for your response!
I’m not sure if your suggested approach would work. I need to have different content in between variables (AND, OR, “;” etc.)

Is there a way to read a row and assign each cell to a different variable on a loop? Ideally my workflow would look like this:

  1. For each Row in excel file, read col1 and assign to var1, read col2 assign to var2, read col3 assign to var3.
    1a. (Within the same forEach) Write Text File = var1 + “and” + var2 + “or” + var3

Any suggestions?


#4

You don’t have to assign values to variable each time. If you are referring to only 3 columns, you can just pass this into your Write Text File activity along with file name without looping.

You can modify the query if you have more columns.

dt is datatable variable

String.Join(Environment.NewLine, from myRow in dt.AsEnumerable() select String.Format("{0} AND {1} OR {2}", myRow(0), myRow(1),myRow(2)))


#5

@vvaidya where would I apply the code to? I’m unsure on which object to append it to or maybe I’m completely on the wrong track.


#6

you can create a string variable and assign the above code or directly pass as shown below.

image


#7

That worked! Thanks a lot @vvaidya you rock!