How can we extract data from Unstructured excel

Help Forum
1

How can we extract data from Unstructured excel.

2

hey

can you be more elaborate please? also provide an example of your requirement so we can help you better

regards!

3

Yeah Sure , Thanks for reply.

Actually I have an excel having unstructured data in it from where i need to extract PO Number and Vendor Name and there is also a table from where I need to extract only column 0(O = iuk) and Column 2(M Market) . How can I achieve that

Capture
Capture999×449 10.7 KB

. Please suggest.

4

so you are not using the first table?

you can delete those rows and the use a read range to get that table as normal excel

5

From first Table PO Number and Vendor Name needs to be extracted also.

6

By offsetting the range we can extract the two tables separately

first table comes in a key value format and we can e.g. apply filters for extractions

7

@Mansi3 ,

After you have the Excel read as a Datatable, You can do the below Operation :

DT.AsEnumerable.Where(Function(x)x(0).ToString.Equals("Vendor Name")).First.Item(1).ToString

The above should give you the Vendor Name value.

Similarly for PO Number, you can use the same expression by replacing Vendor Name with PO Number.

Let us know if it doesn’t work.

8

Thanks for reply , but in assign activity its showing sequence contains no element.

9

@Mansi3 ,

Try the Updated Expression Below :

DT.AsEnumerable.Where(Function(x)x(0).ToString.Trim.Equals("Vendor Name")).First.Item(1).ToString

If the above doesn’t work, then we would require you to Share a Sample Excel file.

10

Thanks for help , that’s work.
There is also a table from where I need to extract only column 0 i.e. (O = iuk) and Column 2i.e.(M Market) all rows one by one. How can I achieve that.

11

@Mansi3 , Does the Table always start at the Same row in the Excel, say from the Screeenshot it is from 12th row, So is it always 12th row ?

If so, You could try using Read Range Activity with Range as "A12" and Add Headers Checked :

image
image865×252 12.4 KB

We can then use Filter Datatable Activity , to keep only the Required Columns :
image

12

No, Table does not always start at the Same row in the Excel, it varies .

13

Hi @Mansi3 ,

Apologies for the Late Reply.

In that case, after we have the Excel Read as a Datatable, we can use the Below Expression in an Assign Activity to get the Rows from the Marker value O = iuk or we can use the Marker Description of the Second Column as it is more accurate :

DT = DT.AsEnumerable.SkipWhile(Function(x)Not(x(1).ToString.Trim.ToLower.Equals("description"))).CopyToDatatable

You can then Write the updated DT to another Sheet/Excel without Add Headers enabled to Check the contents.

Let us know if there are any difficulties and would also help better if you were to provide a Sample Input file to work on i.e if the issue still persists.

14

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