Create one row from multiple row (startswith-endswith)

Hi all!
I am trying to get multiple rows into one row. The row should start with “NL” and should end with the “%”. Then this should be the first row. Then it should start again at “NL” and stop at “%”, which should result in the second row. And so on.
Could you please help me out with this?


@esther.hesseling Can you maybe provide a sample of that data?, and also it would be better if you could show us the output as to How you would want it.

@supermanPunch good point. Refer to the following (input and desired output )Book5.xlsx (10.3 KB)

Is it possible that there is garbage data in rows you DON’T want included? So would there ever be any rows between the row ending with % and the next row starting with NL?

It’s possible either way, but if there isn’t garbage data, then the easiest way would be as follows:

  1. read excel file to datatable i’ll call dt1
  2. Initialize OutputList (of String) assign OutputList = new list(of string)
  3. Create an integer variable called MatchCounter assign MatchCounter = -1
  4. For each row in dt1
    4a. If row.item("Column1").ToString.Trim.ToUpper.StartsWith("NL")
    4b1. Then (true side) assign MatchCounter = MatchCounter + 1
    4b2. Then (true side) assign OutputList(MatchCounter) = row.item("Column1").ToString
    4c. Else (false side) assign OutputList(MatchCounter) = OutputList(MatchCounter) + row.item("Column1").ToString

I haven’t tested it, but that should work to give you a list of string for all the data. If you want it back into a datatable format, then just build a datatable and use a for each loop to add the string to the datatable using the ‘add data row’ activity.

If there is garbage data included, then you will have to do some additional legwork with an If-ElseIf type of statement to further filter out the data you don’t want. This can still be done easily enough using similar logic and a flag that you flip to capture the data or not

EDIT: I have included a tested .xaml now showing how to do it when there is the possibility of garbage data. I didn’t annotate very much so please let me know if you have any questions
deleete.xaml (16.4 KB)

@esther.hesseling I think I have got the workflow to perform the operations for the Expected Output that you needed.
But the Conditions that I have used is For Every value that starts with “NL” there would be a corresponding “%” symbol at the end of value. Meaning that the number of occurrences of “NL” and “%” symbol would be equal. If that’s not the case always, you can give more examples and I could modify the workflow for the same.

Check this workflow :
Split Values Add (9.7 KB)

Thanks a lot @Dave and @supermanPunch.

Indeed there is some (a lot of garbage data).
Now I have got the following output when I use the deleete.xaml flow:

Is is possible to store the output in several variables (Accounts, Name, Currency, Amount 1, Amount 2, Percentage) so that I can write it to an Excel? Furthermore, is it possible to keep the spaces between the rows that are added together?
(upload://onZ18UnyJ4k9jLJB4hh4J7svKA.xlsx) (15.4 KB) Actually, the accounts always have the same amount of characters and they also all have a name.
Book6.xlsx (15.4 KB)

@supermanPunch @Dave Sorry for my haziness when providing a sample of the input data and describing the problem. I am building my first robot and I really appreciate your help

@esther.hesseling I updated the .xaml so it stores each individual string in a column in a datarow instead of as a list of strings.

However, I don’t think this will work, or there will be further processing/error handling required. Right now, the input data is not consistent in how it is structured. For example, you want 6 columns, which means there should be 6 rows of input data between the ones starting with “NL” and ending with “%”. However, the 1st one found has 5 rows, the 2nd one has 4 rows, and the last has only 2 rows.

If possible, you should fix this during the creation of the input data so it is consistent. If that is not possible, then you will have to devise a way to consistently transform this input data into your output data (likely with string manipulation & column counts).

deleete - updated.xaml (18.2 KB)

Thanks for your reply. I check the data, but there is no possibility that this will get more organized.
Together with the two workflos (deletee and deletee updated) I tried to create the solution, but I don’t get the result. Could you help me a last time?

I was trying to get the lines in one column (as in deletee) and then output it in a datatable (as in deletee updated). I don’t know how to do this.

Hello Esther, Try the attached XAML file and let me know. This just prints the info. You can add it to a data table and output it to an excel sheet.

Excel_Esther.xaml (7.7 KB)

@esther.hesseling sure thing - I just took the original deletee.xaml and instead of using a write line activity at the very end, i switched it to a build datatable, add datarow, and write range activity. See attached:
deleete - write to excel all one column.xaml (19.0 KB)

Thank you all for the support, much appreciated! I got the desired output : )

1 Like

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