Data table lookup Value more than one option

Help Activities
, ,
1

can someone help me with this please?

i am curenty reading through a lot of csv files trying to extract a vlaue from them.
i am using a for each row in datable process to do this

what i found is that the name of the field that i need to extract is not the same all the time.

this a a very slimmed down version of what i mean
The image shows a side-by-side comparison of data from two CSV files, with the first CSV listing an employee named John, number 6, in the IT department, and the second CSV listing an employee named Jane, ID 50, in the Sales department. (Captioned by AI)

i am trying to extract the employee number.
so i use lookup datatable like this
The image shows a configuration panel for a data table (DT1) lookup setup, specifying the column to search by name, column number, data table, and lookup value. (Captioned by AI)

this work fine for Csv 1, but will return nothing for Csv 2

is there any way of setting the lookup to look for (“Employee Number” or “Staff ID”) or some other way of doing this

thanks

2

Hi @adrian_sullivan ,

If the column name is dynamic and the column index is fixed You can always opt to give Column Number

1 Like
3

Hello Adrian

If you know that it will always be one of the two - “Employee Number” or “Staff ID”, you could surround the first “Lookup datatable” with a “Try/Catch”, and create another “Lookup datatable” (with the other column name) in the “Exception”-part of the Try/Catch-Activity.

Take a look at this example:
Lookup Datatable.xaml (10.3 KB)

Regards,
Soren

1 Like
4

unfortunately the column index is not fixed, there could be 5 reords in the csv file, or 55 records in the csv file.

5

The number of records is rows, not columns.

6

this was working until i found that there was a possiblitly of a thrid option.

so i can have Empolyee Number, Staff ID or Employee ID,

if i keep digging i may even find something like Staff ID Number.

just proving to be a bit of a nightmare

7

Sounds like you need to improve the process and the data sources instead of struggling to automate a bad process.

8

if only i could, i dont have any control over the csv files. tempted to just say that this cannot be automated.

1 Like
9

One way I’ve handled situations where the source changes is to put the necessary info into a config file. For example you could have something in the config file like this:

Name: ID Column Names
Value: Employee Number,Staff ID,Employee ID

Then you use that list to check if there is a column with one of those names, and rename the column to what you want it to be so you can then code the lookup based on the known column name you change it to.

And if it doesn’t find one of those column names you throw an exception which indicates you have another value to add to the config file list.

10

In that case there is an alternate Approch using Linq.

  1. Declare a String variable employeeIDColumn : used to store the correct column in Dt2 where the “Employee ID” might be stored, whether it’s named “Employee Number,” “Staff ID,” or “Staff Number.”
  2. Assign activity to Get the column Name : Get the employee ID column in the particular Datatable. Mention All possible entries for the same.
    employeeIDColumn  = Dt2.Columns.Cast(Of DataColumn)().
    Where(Function(c) c.ColumnName = "Employee Number" OrElse c.ColumnName = "Staff ID" OrElse c.ColumnName = "Staff Number").
    Select(Function(c) c.ColumnName).FirstOrDefault()
  1. Assign activity to Clone DT1 :
DT1Clone =DT1.Clone
  1. Assign Activity to perform VLookup using Linq: The vlookup results are loaded to DT1Clone Datatable
 DT1Clone= (From row1 In Dt1.AsEnumerable()
                           Join row2 In Dt2.AsEnumerable()
                           On row1.Field(Of String)("name") Equals row2.Field(Of String("name")
                           Select Dt1.LoadDataRow(New Object() {
                               row1("name"),
                               row2(employeeIDColumn),
                               row1("Dept")
                           }, False)).CopyToDataTable()
11

Hello Adrian

In theory you can add another Try/Catch inside the first Try/Catch and continue like this, untill you cover the known column names.
However its not a “pretty” way to do it… but might work…
Let me know if you need an example.

Regards,
Soren

12

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