I need Search Column name in a data table like Fuzzy selectors if match column name 80% like this

Help Activities
, ,
1

Hi Experts,

i have a Hundred plus excel files. its contains customer data. here in each file in different formats, we don’t know how headers coming and index of that column. so i need to search column names.

in my scenario if i need to find Customer Name , but in excels it might be Cust Name/CustomerName/Cust. Name like this. so, is there any possibility to search like Fuzzy Selectors Selectors/RegEx. if possible string(Column name) Match at least 80%, its would be TRUE.
i need solution for this.

Thanks in Advance…

2

@Vijay_0815

If you know the possibilitie of th names that you can get then you can use below to get which column name is present and then can use it

{"CustomerName","Cust. Name","CustomerName","Cust Name"}.Intersect((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray())(0)

If ypu dont know one more way is to try regex

(From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray().Where(function(x) NotString.IsNullOrEmpty(System.Text.RegulrExpressions.Regex.Match(x,"cust.*name",RegexOptions.IgnoreCase).Value))(0)

One more way is to use levinstein algorithm…

Hope this helps

Cheers

3

Thanks for Quick responding. if we know possibilities of input, it would be easy. but my scenario not possible. but i am trying with your answers…

here,
{“CustomerName”,“Cust. Name”,“CustomerName”,“Cust Name”}.Except((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray())(0) its always shows first index, that means CustomerName. so why should use list of possibilities in array. can you explain or modify this syntax?

4

@Vijay_0815

Is customername present in the columns of your datatable? If yes then it will give that value…

one small change is I used Intersect instead of Except

{"CustomerName","Cust. Name","CustomerName","Cust Name"}.Intersect((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray())(0)

image

this is working as expected

Cheers

1 Like
5

its working, thank you so much. while working on this i found one issue, Some files column had not in a first row, started 3/4/5th rows, so can i find same way that key word(Column name) in sheet?, if not i can move to next sheet. actually i have more sheets(they hide, robot reading those sheets also) so i need to read one by one sheet. please let me know…
thanks for quick respond.

6

@Vijay_0815

So if row changes do we need to read from that specific row?

ideally in read range if we dont give any rnage then it will read from the first row where data is present…can use that

cheers

7

is it possible to search in entire sheet(data table) like as you mentioned ({“CustomerName”,“Cust. Name”,“CustomerName”,“Cust Name”}.Intersect((From dc In dt.Columns.Cast(Of DataColumn) Select dc.ColumnName).ToArray())(0)) like this? (not row by row, need entire sheet at a time)

8

@Vijay_0815

You need to use find/Replace activity and search each value separately in whole sheet

if found it returns a value or cell if not it does not return a value

cheers

1 Like
9

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