Help needed with non structural XLSX file

Hello friends,

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).

Is there any way that I get all the information together because I have one large Excel file and I don’t know much rows will column J have.

Should I ask the client to try and export the table in only one row?

Example of Table.xlsx (9.6 KB)

A similar issue was resolved here:

https://forum.uipath.com/t/excel-reorder-merged-rows-inside-column

Cheers

1 Like

Thanks I could not find the topic. I’ll look into it!

Did not help that much :confused:

There is a method retrieving size of the merged range => you could use it to read all rows in “J” column

Best to read the range A:I and J separately.

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.

I can provide an example in time.

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

store the flattened J Values for a segment into j Column with the row index of the corresponding non empty Col A

Once you have done this for all segements, then filter the DataTable and remove all empty Col A Rows

result should be

in each Row the segement values from J is flattened in the Column J
No empty Column A rows are now longer in the dataTable

Let me know your open questions

1 Like

I will try this, since I don’t know LINQ it will take time (I asked if I can get exported Excel in different way) but if I cannot, I will learn this :slight_smile:

Thank you for detailed explanation.

Hi @srdjan.suc

May I ask you on following:

  • please provide an excel with 3 sample rows (Col A, jumping Col J rows)
  • your feedback on general for the segementing, flattening Col J approach

then we work out a sample xaml for this in a little time

Example of Table.xlsx (10.6 KB)

Here is an example.

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

@srdjan.suc Perfect give me some time I will come backwith a sample.

Dont be worried about LINQ. You will learn it with us supporting you by this

1 Like

@srdjan.suc Can you please check the result Excel. If its ok I will pass you the XamlExample of Table_Changed.xlsx (9.2 KB)

Seems great!

Btw I don’t know if you kept in mid that column J won’t have fixed Rows but they will change.

I started a course with Linq so I can understand your sample better :slight_smile:

Main.xaml (15.2 KB)
JCol should get supported by variable length - Just play with this and let me know the result

1 Like

Absolute mad lad, perfect!
Thank you so much!

Anyway, where did you learn Linq with Functions, seems like I cannot find it directly.

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

1 Like

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.