IEnumerable <Regex Matches> to Datatable (best way to meaningfully extract data from multiple IEnumerables)?

[CodeForgePro]

I have a sequence where I am using ReadPDF and then the Matches activity with Regex to great success.

The output is coming out as ItemNamesRaw an IEnumberable variable for the first Matches activity and as QtyRaw which is an IEnumrable variable for the second Matches activity on the same PDF.

Currently I just have: For Each Item in the Variable converted to string and writing a line to be sure that they are all being read correctly.

I would like to have them append columns in an excel spreadsheet for inventory purposes, (all items to column A and all Qtys to Column B for example) but I’m having trouble figuring out how to convert the IEnumerable into a DataTable as that’s what is required to append a range.

1. Should I be aiming towards using Append Range to do this?

2. Is there a more meaningful and maybe flexible way to extract and collect the information within the IEnumerable so that I can access this information in the best way possible?

I don’t recall learning something specifically already that I can think of using, so maybe this is further ahead in the Academy than I am, (I’m about 25%) but even being pointed in the right direction would be great.

As always thanks for all the help on these forums, it’s always quick and concise to get an answer and I’m loving the community here.

[vvaidya (Vinay Vaidya)]

Did you try ItemNamesRaw.CopyToDataTable? (you won’t get intellisense, but you should import System.Linq)

if you need to bifurcate your Item and Quantity, build a datatable and probably you can bifurcate by length (eg >5) or contains hyphen (-) then add to different A or Add to column B.

Will think of more options…

[CodeForgePro]

Where would I put “ItemNamesRaw.CopyToDataTable” ? …In an assign activity?

Also, what is System.Linq? and where would I use it?

[vvaidya (Vinay Vaidya)]

Yes create a database variable and assign ItemNamesRaw.CopyToDataTable to that variable.

To Import System.Linq, after Variable,Arguements tabs you see Import Tab, in the search bar type System.Linq and add it to your project.

[CodeForgePro]

Getting this error when trying to use the .CopyToDataTable

It’s not popping up to autopopulate like commands usually do.

[CodeForgePro]

As I understand then, .Linq imported will give me more commands to use? Including .CopyToDataTable?

I imported the System.Linq and System.Linq.Expressions and I’m still getting that same error though.

Do I need to do anything else to be able to use those expressions?

[vvaidya (Vinay Vaidya)]

Sorry I did not notice that your Inumerable is Regex Match type, it works only with DataRow type.I will give you another solution.

[CodeForgePro]

Thank you!
*Waits patiently

[vvaidya (Vinay Vaidya)]

Your item and quantity are mapped? Or Random?

[CodeForgePro]

They are Mapped, corresponding to each other… Is that what you mean?

[vvaidya (Vinay Vaidya)]

Yes sir.just a thought on what basis are you getting regex match? Can you concatenate item quantity at regex level itself?

[CodeForgePro]

Item: (?<=Article Number: ).{13}([^\n\r]*)
Qty: \d+(?= piece ).{1}

… Even if I get them both from regex and concatenate them, I’m still looking at how I’m going to get the data out of an IEnumerable of Regex though right?

If I got them into just one, concatenated… how would I get to that data? Isn’t it the same problem I’m having or could I do something else with that?

[vvaidya (Vinay Vaidya)]

This should give an idea. Good luck.

matches.xaml (9.5 KB)

[CodeForgePro]

That was exactly what was needed.

Thank you very much, I’ve got everything running correctly with my data.

It was very helpful for you to make a template for me, and I can understand the processes used now. Really great job and thanks again and kudos!!

[AnniekJ (Anniek)]

Hey, I saw your solution and I am looking for something similar I think, but I don’t understand the file you send.

I use a matches activity with the following regex pattern: “\s[0-9]{23}\s”, to find all strings on a page that are 23 digits long. Now I want to filter the results so there is only unique results. I thought I’d do that by putting them in a data table or excel or something and then further filter, but I can’t figure out how to get the collection of matches into a data table/excel

Help would be much appreciated!
Thanks

[harishk5 (Harish)]

Hi vvaidya,

Your solution is what i was looking for. But still i couldnt convert IEnumerable to Datatable.

At ‘Add Data Row’ step, I am getting the below error message. Kindly help

An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.ArgumentException: Unable to cast object of type '<CastIterator>d__97`1[System.Text.RegularExpressions.Match]' to type 'System.IConvertible'.Couldn't store <System.Linq.Enumerable+<CastIterator>d__97`1[System.Text.RegularExpressions.Match]> in Email Ids Column.  Expected type is Int32. ----> System.InvalidCastException: Unable to cast object of type '<CastIterator>d__97`1[System.Text.RegularExpressions.Match]' to type 'System.IConvertible'.
   at System.Data.Common.Int32Storage.Set(Int32 record, Object value)
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   --- End of inner ExceptionDetail stack trace ---
   at System.Data.DataColumn.set_Item(Int32 record, Object value)
   at System.Data.DataTable.NewRecordFromArray(Object[] value)
   at System.Data.DataRowCollection.Add(Object[] values)
   at UiPath.Core.Activities.AddDataRow.Execute(CodeActivityContext context)
   at System.Activities.CodeActivity.InternalExecute(ActivityInstance instance, ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.ActivityInstance.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
   at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)

[CodeForgePro]

Did you ever get this working? I hope so!! but if not lemme know and I can show you how to use Matches to find what you want, and you don’t need to dump the data into an excel first or anything.

[CMBeck]

Hi Vvaidya,

Thank you very much…
Your solution saved my day.

[May_Guerrero]

Thanks for this, almost what I needed but now I am now going mad! How do I create the table if the item and quantity are not mapped and there are items where the quantity is empty