Get Column Name from Excel with partial match

Help Studio
, ,
1

I have a excel table with a column name starts with “Which Business…”. The end of the column name might change every month. I need to get the value under this column.

2

Hi @rvivek, instead of using column name, you can use index number, It may work

3

What change will be there can you share some sample ?

Also if your column will be in same position you try like @rohin_Kumar suggested

Regards
Sudharsan

4

For example, the column name might be “Which Business ?” or simply “Which Business”. At the end, Question mark might be there in the column Name few times.

And No, the position will also change.

5

Hi @rvivek ,

Use the below code to retrieve the full column name and assign to a variable. Then you can use the same variable to fetch the value.

dtData.Columns.Cast(Of DataColumn)().FirstOrDefault(function(h) h.ColumnName.Contains(“Which Business”)).ColumnName

1 Like
6

Hi @rvivek, You can use the following approach.

Step 1: First of all, you need to get all the column names into a list. You can do this by using -

headers = dt.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToList()

where ‘headers’ is the list of strings.

Step 2: Since we are not sure about the column name, we can pass it from the ‘headers’ list, by using a sub query inside the main query. This can be done by:

rowData = dt.AsEnumerable.Select(Function(row) row(headers.Where(function(x) x.ToString.Contains(“which”))(0).ToString).ToString).ToArray

Where ‘rowData’ is an array which contains all data that comes under the column “which…”. Position of this column or the name doesn’t matter as long as the keyword ‘which’ is present in it.
Also note that this query gets the data from the first column whose name contains ‘which’ keyword.

Hope this helps. Please let us know any further clarification is required.

Best Regards.

7

I tried this - but it shows error as
Compiler error(s) encountered processing expression “(DT1.Columns.Cast(Of DataColumn)().FirstOrDefault(function(h) h.ColumnName.Contains(“Which Business”)).ColumnName)”.(2) : error BC30561: ‘DataColumn’ is ambiguous, imported from the namespaces or types ‘System.Data, UiPath.UIAutomationNext.Models.ExtractData’.(2) : error BC30456: ‘FirstOrDefault’ is not a member of ‘IEnumerable(Of DataColumn)’.
Please help me with this.

8

Can you post the screenshot of your code?

9

I’m sorry I am having trouble uploading the screenshot. But this is the code I entered in the Assign activity
DT1.Columns.Cast(Of DataColumn)().FirstOrDefault(function(h) h.ColumnName.Contains(“Which Business”)).ColumnName

DT1 is the DataTable

10

Hi @rvivek ,

The error states that the DataColumn type is available in two namespaces and hence it is not able to identify the correct namespace and thus it gives the error.

To Rectify this, you could add the whole namespace of the DataColumn that we require that is System.Data.DataColumn.

Resulting Expression :

DT1.Columns.Cast(Of System.Data.DataColumn)().FirstOrDefault(function(h) h.ColumnName.Contains("Which Business")).ColumnName
11

This is one of those situations where I would look at the overall process, find out why this inconsistency is happening with the source file, and fix that part of the process. Don’t automate bad processes. Fix your processes, then automate them.

12

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