I need help with unstructured XLSX file (or at least I call it that).
Please note Column J, it is exported to have multiple rows.
When I use normal Read Range activity inside Excel Application Scope it only reads the first Row of that Column (so I only get the value “a” not the rest).
A:J is structured so that is simple enough (dt1). You need to then split the values in J column, using the split method and space as the delimiter. Add these strings to their own datatable (dt2). Then you can append dt2 these values to dt1 as an additional column.
The only requirement is that the number of values in col J is equal to the number of rows in dt1 for the two datasets to line up.
That was what I was thinking, but I have this as repeated value (sometimes J has 1 row, sometimes 15) how can i know when should I difference it if I read it separately form rest of the Excel file that difference it?
Is your goal to have col I split into columns and appended to col A:J?
Well, once you have split col I in dt2, you can use a foreach loop to cycle through those values. Before adding them to dt1 however, you need figure out how many additional columns need added correct?
In an IF statement place the condition dt1.columns.count <> dt1.columns.count + dt2.rows.count
This should ascertain if there are sufficient columns to put the data, if not, use the Invoke Method to add a new column and write the value to it.
You see from the picture that I actually have 10 rows, I want everything to be in 1 row, so I want to add everything that is in Column J until there is a change in Column A and so on and so on.
@srdjan.suc
doing a quick adoption to the read datatable should be possible, but it may need multiple activities.
Detecting the segements - find out when J is on End for a particular A:
make benefit of LINQ and index
e.g. dt.AsEnumerable.Select(Function (e, index) New KeyValuePair(Of int32, String) (index, e.Item(0).toString).Where(Function (d) Not String.IsNullOrEmpty(d.Value)).toList
it will return a KVPList with rowindex and Col A Values from the non empty Column A rows (lets call it markers)
These markers you can be used to identify the J Segments and J values can be flattended
I kinda don’t waste your time since I don’t understand Linq at all, especially in UiPath. Maybe I will go and google it a bit before I can answer you the second question
I learned LINQ with C#, much more understandable as with VB.Net. But with this experience I am driven in a way that I have an idea and try it till i do suceed. Let us finalize this post and summarized whet we achieved and how it was done. I will do my part in a few hours.
About LINQ lets check together what we can do for learning help for you and the community
The problem was that sometimes we get exported XLSX files that are not structured in a way that we can work with them easily in UiPath Studio.
In my example I got multiple rows in a single Column where it should be 1:1 it was 5:1 or that number 5 was dynamical.
The solution was to add in those 5 rows into a single Row and filter table based on that.
With the solution provided above, where there was use of Linq expressions, I got exactly that, so now I can Query and search my DataTable much more easier than before.