Extract data from google sheets

Help
1

Hi,

I am looking to extract data from cells in google sheets, when enter the data i need to split up the data for example
image
all the above data would be in one cell however when i am typing the data back into another application ‘Linnel’ is the first name
‘R’ is the surname
‘WR2020707’ is a ref
‘BGC’ is a ref

How can i use that variable to input the data in specific fields rather then entering the whole data thats stored in the variable.
Each row will have different amounts of data, the bot will need to recognise the spaces in the data to separate the data.

can anyone help me please?

2

If your variable is a string MyStr = "LINNEL R WR2020707 BGC", you can assign each separately by creating an array of strings MyArr, where MyArr = MyStr.Split(" "c).

Now you can get “LINNEL” with MyArr(0), “R” with MyArr(1), etc.

3

@Anthony_Humphries
Thank you Anthony, how would that work for the next row of data, would it be the same logic that is looped until there is no more rows to extract data from?

Also if there are rows with data missing i need the bot to write in a separate cell ‘Can’t process’, would that be a different activity?

4

The next row of data would be read by checking the queue item or file (whichever you choose to use), and resuming on the row after that.

If there is data missing, this is something you will need to have the bot set to check. This would be a separate check from determining the last row processed.

5

@Anthony_Humphries

Ok cool i get you, when entering the data back in say my variable is called ‘Test’, ive assigned the variable and and created an array of string when typing the variable back into a document would i be tying it in like this ‘Test.MyArr(0)’ ?

6

If Test is your original string, you need to create MyArr with an Assign activity, where MyArr = Test.Split(" "c). Then you use MyArr(0) to enter the first name “LINNEL”.

7

i am still getting errors, where am i going wrong?
test.xaml (6.2 KB)

@Anthony_Humphries

8

Remove the space in MyArr (0). This needs to be MyArr(0). Also, you don’t have MyArr declared in your variables. This needs to be set to an array of strings.

9

@Anthony_Humphries
Thank you, sorry i dont understand how do i declare the it in the variable?

10

See the screenshot below. You’ll need to create a new variable, and when you set the datatype, go to Array of . At the popup that appears, choose String.

image
image1430×349 9.72 KB

11

@Anthony_Humphries you the man!! thank you

12

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