How to find a value in data table using c# in blueprism

[SriLakshmi]

Hi All,

I have 8 different excels and each with 7k rows and 1000 columns
I want to find a employee id in all excels and get all the column names where this employee id is available.
Ex: employee id might be available 0 or any number of times in any cell in any excel.

Points to be noted:.
1. In i/p i will be getting 100 employee ids. So i need to find all the column names in 8 different excels where each employee id is available.

2. Each excel will be having different column names.

3. Each excel will he be having 7000 rows and 1000 columns

So if i try with a normal loop going through each row and column

For 1 id
for 1 excel = need to iterate 7000rows *1000cols times
need to iterate 8 excels
need to iterate for not less than 100 employee ids in all 8 diff excels.

this will consume more time.

Any quick solution?

 

[gil.silva]

There's no need to complicate.

If you have a high number of rows in excels, it's never a good solution to put all the data into a collection.
In your situation I would like like this:
1) Get Row with the headers as Collection
2) Find the Index with the target header (ID)
3) Convert Index to Column letter
4) Find the given id in the respective column (VB.net action required)

 

[SriLakshmi]

There's no need to complicate.

If you have a high number of rows in excels, it's never a good solution to put all the data into a collection.
In your situation I would like like this:
1) Get Row with the headers as Collection
2) Find the Index with the target header (ID)
3) Convert Index to Column letter
4) Find the given id in the respective column (VB.net action required)

At run time - i have no idea in which column (under which header) this id is available.
Then to find the index i need to iterate through all 7k rows and 1k columns to get the column name or header.

Correct -If i understood your sol. approach incorrectly.

 

[gil.silva]

I will try to explain better:
1) Read the range where you have the column header, e.g. (A1:T1), then loop to find in which position you have the 'ID' column
2) Knowing that the 'ID' column is, for instance, in position 4, you know that corresponds to Column D
3) Create a custom action to Find a value in a range (D110000) (more information here: https://docs.microsoft.com/en-us/office/vba/api/excel.range.find )

I use a similar approach to find values in excels with 60k rows and it takes just a few seconds.

 

[SriLakshmi]

What is the ID column you are referring to?

How would i know under which column itself that value exists in the excel cell.

Out of 7000 rows and 1000 column my
cell value =" 0013589" might exists in any excel cell( .

How would i know in which column?
Here i want to find exact cell co ordinates ( initially i dont know about which row or which column )

So without going through each row and column wat is the possibility of knowing the column