Trying to take a list of rows in one column and separate them out into columns based on criteria

Help Robot
1

I have a txt document that I am trying to put into a datatable. The file looks like this:
Name=JaneDoe
Email=Jane@gmail.com
Last Date=20210514
Name=JimWalker
Email=jim@gmail.com
Last Date=20211515
Name=Lisa Loeb
Email=Lisa@gmail.com
Last Date=20210430

I need to put the info for each person in a row with a column for name, a column for email, and a column for Last Date.
There may be times that one of these fields could be missing or blank.

I am pretty new to UiPath so any help would be appreciated.

2

Hi @karen.c.stewart4.civ,

Welcome to the community :smiley:

Now this whole task can be done by following the steps below:

  1. Read the text and store it in a string. Create 3 variables namely name, email and LastDate.
  2. Build a DataTable on the basis of the columns required (here you will have 3, on basis of name, email and LastDate)
  3. Split the whole text on the basis of next line ( can be “/n” or “environment.New Line”)

Article: Split(environment.new line)

  1. Now Store the line and split again on the basis of “=”
    (This is to get the value and and the key pair you want to store it in the variable as per the split before “=” with value after “=”)

  2. Once you have any repeated value you will do add Data row to the DT we have build in 2nd step. Once done, reset all the variables.

Keep on repeating till you reach end line.

Hope this helps

Enjoy :beers:

Thanks,
Shubham

1 Like
3

@karen.c.stewart4.civ
If you have resolved the issue. Please mark the answer as solution and this will close the topic :slight_smile:

4

So my issue is that the three lines need to stay together and the next row always needs to start with the Name field. If, say, the email is missing it would just skip that one and the Last Used would be put on that row and a new row would start for the next set of three (or less). So I would need three columns (name, email, last used) and then a row for each set of these. Does that make sense?

5

This looks like one of those situations where the first question should be “how can we improve this process?” A good place to start would be getting a properly formatted (ie csv) data file instead of this bad format you’ve posted.

6

And I would agree with you, but this is the data that we have, so it is what I am forced to work with. I have gotten the data formatted to the point where I have a list, all in Column A, that goes down the rows. There are three pieces of information…but one of those pieces, the email, could possibly be missing. Does anyone have an ideas before I start my trial and error down this rabbit hole???

I also just realized that there is a blank line at the end of each “set” of data. Could I use that as a separator somehow?

7

In that case all you really need to do is split on carriage return as mentioned above. Then you’ll get an array with each line a different index in the array. Then loop through the array, and if the value starts with Name= or Email= or Last Date= then process accordingly.