Read Excel Range into variable with proper format

Help
, ,
1

Hi. I’ve got a set i values that I’d like as a variable (string). Let’s say I have the values A, B and C in A2, A3 and A4 in an Excel sheet. I’d like to read these in as var1, and the values in B2, B3 and B4 (let’s say 1,2,3) into var2.

I’ve done this using Read Range, and then Output Data Table. So far, so good. However they are now outputted as A B etc. I need them in the format of {“A”,“B”,“C”}. Any input on how to achieve this?

2

Why don’t you use read column activity which will give you the enumerable of values in that particular column so that you can use them with a simple loop further?

1 Like
3

I’m sure I can use that instead, but it still doesn’t bring me any closer to getting the data in the format I need.

4

You can explore the DataTable functions. Each row in the DataTable is a DataRow. You can then string concat functions to concatenate the columns of the DataRow the way you want them. Something like:

for each row in DT
String s1 = "{"+ "\""+row("A").ToString+"\""+ "\""+row("B").ToString+"\"+ "\""+row("C").ToString+"\" +"}"

Hope this helps.

1 Like
5

I’ll try that - do I put that in a for each row activity or in an assign activity? Sorry about the stupid qustion but this is a bit new to me.

6

Sorry, I wrote that code as an example.
Yes, you will have to use the For each row activity and within that container, use the assign activity to assign your concatenations to a variable s1.

7

I’ve entered the code String s1 = “{”+ “"”+row(“A”).ToString+“"”+ “"”+row(“B”).ToString+“"+ “"”+row(“C”).ToString+”" +“}”, s1 = “{”+ “"”+row(“A”).ToString+“"”+ “"”+row(“B”).ToString+“"+ “"”+row(“C”).ToString+”" +“}” and “{”+ “"”+row(“A”).ToString+“"”+ “"”+row(“B”).ToString+“"+ “"”+row(“C”).ToString+”" +“}” and they all result in various errors from “Expected end of expression” to Expected “.”. I’m doing something wrong :thinking:

8

@mickeymack if your alright with using a list this should work for you

image
image903×765 28.2 KB

sauce:
readRange.xaml (5.6 KB)

9

The double quotes inside of the double quotes may be causing issues.
You might want to do something like this:

""""+row("A")+""""+""""+row("B")+""""

10

I’m okay with using anything that gets me where I need to be, but I’m afraid I’m at a loss what to do with the list. I need a string with the format mentioned in OP.

11

I’ve modified it so it reads

“{”+ “”“+row(“A”).ToString+”“”+ “”“+row(“B”).ToString+”“”+ “”“+row(“C”).ToString+”“” +“}”

but it’s still throwing the same error

12

I’m not sure if you’re copy-pasting the expressions from the forum . But your post shows incorrect symbols for double quotes. The ones that are the beginning of the line are incorrect. They should look like the ones in the second red circle.

image
image1063×108 8.3 KB

13

@mickeymack can do!

Add one last assign activity at the end.

create a new string variable (lets call it myString ) and assign it to myString.Join(“,”,StrArray)

image

You got this!

Edit - Here is an updated workflow that would output your string with the brackets and quotes you mentioned in OP

readRange.xaml (6.5 KB)

1 Like
14

That works like a charm! Thank you so much!

15

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