Extract specific data excel files

[sereganator (Sergey)]

Hello everyone!
Please tell me how to extract data from Sheet2, column “VIN”? As you can see, the cells are merged and you will not be able to add a sheet to the database. Help me please.

sample1.xlsx (9.7 KB) sample2.xlsx (9.8 KB)

[vamsiyeluri]

@sereganator

If that excel structure is constant then u can use for each row for VIN Column and u can extract the values…their itself u can provide the conditions to avoid the numeric(4)… then output is A233244, N12445fsdss.

[NIVED_NAMBIAR (NIVED N)]

Hi @sereganator

You can try this

For this excel file

I had done the workflow like this (The red arrow mark indicates that excel should start from this section using read range.

The workflow did for this is

The output of read range is stored in dt1

Now output obtained as

In same way u can do with second excel file as it should start read from the A5 so that u can obtain values accordingly.

Regards

Nived N

Happy Automation

[sereganator (Sergey)]

Thank you, but the structure is not constant, the “VIN” column can be in different parts of the sheet. In this case, what to do?

[NIVED_NAMBIAR (NIVED N)]

But Vin column is under 4?

[sereganator (Sergey)]

Usually yes, but the worksheet may have another column with a 4 below it.

[NIVED_NAMBIAR (NIVED N)]

Then try this

dt1.Rows(0)(“4”). ToString

[vamsiyeluri]

@sereganator

then first convert that dt to string,

then use generate datatable with csv parsing…

so, u will get all individual cells values without merging.

next lookup for VIN then u will get the index where it is located in excel.

next remove all rows up to where vin exactly located.

and next use for each row with column name —> row(“VIN”).ToString

if it is helpful then mark this thread as the solution…
cheers…

[asgRPA]

Hi,

I think you could use the LookUp Range activity using “VIN” as input parameter, this way you will get the cell address of “VIN”, so now you have the column.

By other hand you could use the Get Cell Color activity using as input any cell of the headers, so now you have the color of the headers (~grey)

Get a datatable of just the “VIN” column and use a for each row loop. You just need to check if the current cell has a different color.

I hope this help.

[sereganator (Sergey)]

@asgRPA

LookUp Range

Thanks for the help, but in the end the “VIN” column is not a header and therefore cannot be read or maybe I am doing it wrong, please see the xaml file. Thank you.

Main (3).xaml (12.0 KB) sample1.xlsx (10.4 KB) sample2.xlsx (10.5 KB)

[vamsiyeluri]

for 1st read range check box the headers…

Main (3).xaml (12.3 KB)

[sereganator (Sergey)]

Thank you so much!