Save values in a string to different columns of a data table

Help
,
1

Hi,
I need to save values present in string to different columns of a data table. Please help

For example I have this as a string in a cell of a excel

image.png699×21 1.21 KB

SEARAY 30 ULSD PRIMORSK UKC 01/05 WS 135 ST SUBS

In the end I need it like this

SEARAY 30 ULSD PRIMORSK UKC 5-Jan WS 135 ST SUBS

2

This looks like you have values separated by “Space” character.

You can use Split to get all the values as one String Array.

YourStringVariable.Split({" "},StringSplitOptions.RemoveEmptyEntries)

Once you have the string array you can use Add Data Row Activity and pass the array in the ArrayRow property.

[details=Important Note]
If any Value itself contains “space”, then it will be considered as values to be in different columns.[/details]

1 Like
3

Hi @sudhanshu31,
You have the input in one string. Thats fine. You have to split the whole string based on some feasible separator, may be space as @akhi_s27 has suggested.
Next thing, after splitting you will get the output in one Array.

  1. Implement one for loop to iterate through the array values.
  2. By fetching one by one values and storing in different variables.
  3. Use Add Data Row activity and pass the multiple variables like this: {Value1,Value2}.

Hope it will help.
Regards,
Jiban

1 Like
4

What if we have separater as TAB, as it is not accepting ‘tab’. Please suggest

5

Does vbTab work?
like .Split({vbTab},SystemStringSplitOptions.RemoveEmptyEntries) or if you do just the character .Split(vbTab(0))

2 Likes
6

Thanks! That’s work partially, as now I am able to get separated items in my object variable, but I am getting output in excel as system.string when I trying to add data table. Please help,

7

Have you tried the steps suggestions from @jibanjyoti?

8

I tried this using For each, but don’t know how to assign values to different variables or array variable?IMg

9

@rk.sati
The For each activity has a Type Argument parameter that is defaulted to Object, I believe. You will want to change that to “String” since each “item” will be a string from your array.

Then you can put a string Variable in the left side of the Assign activity to store the item into or do other activities with that value.

Let us know if you need further help.

Thanks.

10

Thanks ClaytonM, but after splitting I have 35 items. My actual ask is how can I store that in 35 different variable?

11

Typically, I wouldn’t store them in individual variables (since there are so many) cause you can access each item from the array.

For example,
DTarr(5) would give you the 6th item using index of 5

It also depends on what you need to do with the values. If you need to store them into a spreadsheet then there are few ways to do that also. Like, if you create a datatable either by making a new one or Read Range, you could do “Add Data Row” and put DTarr array into ArrayRow parameter.

Then, at some point you use Write Range to output the datatable to the spreadsheet.

12

Thanks!
Initially I tried it using my DTarr which of array type into add data row(array row as {DTarr}) but that is giving output in excel in a single cell.
So I split that using tab. But after splitting when I am using in my array row, it is giving output in excel in a cell as System.String.
That is why I am thinking of storing each split item in different variable or array variable, but don’t know how.

Appreciated your help!

13

Do you have a screenshot or example so we can see the activities you are using and the parameters? You should have a datatable that stores the items into each column (with Add Data Row) so you can look at values before you output it to Excel. Then, Write Range will output the datatable to the sheet.

The ArrayRow parameter should be just DTarr with no brackets. When you do the split it is already an Array so no need for brackets.

Thanks.

14

Ooops! removing single {} brackets makes all difference. It is working perfectly fine now after removing those bracket. Thanks a ton!
I tried many ways and spent 1 day… Thanks a ton!