Comparing string to a values in huge excel sheet

Help Studio
1

Hi.

I have a large excel with thousands of rows.

I have a string that i want to compare to with each row in column J in that excel and see how many matches are returned. if there is a match i want to record the data from column K & L from that row.

If the string i have is contained in the value in row J, that will suffice.

Thanks for the help

2

Hi @Automater999,

If you use “Filter Data Table” Action, you will get out a second DataTable with the same structure. If you count the rows of the new Datatable you will have the information on how many matches.

Then taking the columns or going For Each Row, you get the other informations

3

Hi,

this works but is quite slow as the size of the excel is so big! is there another way to do it using linq for example?

4

You may try to use SQL, if you prefer.

You can use some Oledb tool with this connection string:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myBinaryExcel2007file.xlsb;Extended Properties=“Excel 12.0;HDR=YES”

5

Hi,

Sorry i am unsure as to how to go about this. could you explain further?

6

Hi @Automater999

You can create a datatable variable. then in a assign activity intialize it using the below Linq:

( From row In your_datatable.Select()
Where row(9).ToString = “matching_value”
Select row).CopyToDataTable

This will filter-out all the matching datarows into your matching-datatable. Hope this filter technique will improve your processing speed :slight_smile:

7

You can combine the COUNTIF and INDEX/MATCH functions to achieve your desired result.