Splitting by delimiter in Datatable for each row

Help Studio
, ,
1

Hi, I was wondering how to split for each row in a datatable. For example, a column of my datatable looks as follows: image. I would like to know how to split them by space without having to read the excel range (if that is possible).

The process I took was as follows:

  1. I made a datatable using regex to find information in a PDF file.
  2. The second column consists of the regex information (which happens to look like the image above)
  3. I read that onto an excel sheet.

So my question is in a sense, is like how to do text to column in excel, but the datatable version.

Thank you!

2

Hello @ktanli
have you tried Generate DataTable activity…?

3

I have, it did not work out so well. I cannot send my workflow, but my data table looks something like this: image There are also null values in the second column in some rows. Generate Datatable does not seem to like the null values.

4

can you give some sample text or attach text file here

If I understood your requirement correctly, you need datatable something like this
image

5

Yeah! Something like that! It’s like if it is on Excel you would do text to column with the space as delimiter. So in the end it should look like: image

6

I am not sure about attaching a text file because the way I did it was I had to use RegEx in order to find the second column values (hence why they are null values because if the bot cannot find a value in Column 1, it would leave the corresponding Column 2 cell blank).

7

actually I dont know the exact use case of Regex here,
but when I tried with sample data like this (there is no space after “B”)
image

Generate DataTable activity gave me result like this
image

issue could be related the regex (I am not sure though)

8

could you try to dump dummy PDF text here. Text value before using any regex

9

I cannot attach the PDF because it is for work. But let me give you a rough idea what I had to do, when I read the pdf to a text file it looked something like this: image. The idea around it was that I needed to separate the numbers from the "first column (A, C, Sample A, etc. The problem was that when it says Sample A it would split that into 2 columns. So I did regex that looks something like this: image. This was an example from a video I followed along. Now what ended up happening was the image I attached above. The first column was one thing, and the numbers are part of another column.

Now, aside from the PDF extracted, there is actually a set list of variables in column 1. Some may not be in the PDF file itself so for example:

A
B
C
Sample A
Sample B
Sample C
Sample D
E
F

Regex could not find “Sample B” and “E”, so hence it leaves it blank on the data table.

10

So in short, the use of Regex was to separate the first column, and the values that were put in the second column. I just need to figure out a way how to delimit the second column by space.
I was thinking something along the lines of: For every row in Column2, split the string… or something like that.

11

can you confirm one thing.
in column0 i.e. A,B,C Sample A etc.
all the values will be alphabets or they could be alpha-numeric

12

Yes. When you mean alpha numeric do you mean: 1A?

13

yes…

14

Yes, then some values in Column0 are alpha numeric but it always ends with a letter.

15

ok then give me some time… :nerd_face:

16

Got it! Thank you!

17

Also, I managed to get some of it, but as soon as it hits a blank row it does not want to continue.
This was what I did:

image
image453×696 14.9 KB
. So, it quite works but not when the row is blank, is there a way to go about this?

18

try with this xaml
Main.xaml (6.8 KB)

OfcUrlCategory.txt (118 Bytes) [sample data that I used]
Note: this will replace " " [space] with “_” [underscore] in first column

19

It worked out! Thank you!

Also just to clarify a few things, I am not very good a RegEx:

image → what does {0,10} mean?.

And basically, find any values in the text file that has a letter in the front (and not all numbers), then put an underscore.

20

that pattern is finding the part of string which starts alphanumeric and ends with alphabets only…
example:

image

you can refer below link for steps by step explaination