I have two data tables that each have a column “X”.
I want to find the values that are in column X of data table 2 but not in column X of data table 1.
I’ve thought of a couple ways to do this but they both feel very verbose and I’m wondering what the best way would be. Is there a single activity for this task? Or a single “assign” command I could run?
You will have to read the two source Excel files into respective DataTables using two Read Range activities.
You can then perform the comparison based on your specific criteria and output the results to new Excel files using Write activities.
As to the specifics, there is plenty of information on the forum about filtering the results. Some hints:
you can use DataTable.Select("[columnName]='columnValue'").CopyToDataTable() to create a new DataTable with specific filter applied (this can be used to generate a table for the first output Excel)
use For Each Row activity to iterate through the rows of DataTables to compare the records and generate your desired output for the second output Excel;
to access specific items in your DataTable, you can use this syntax DataTable.Rows(rowIndexAsInt).Item("columnName").ToString
or shorter if within a For Each Row activity: row.Item("columnName").ToString
Thanks for the reply. I don’t need to do anything with Excel though. I already have two DataTable objects and am just trying to make a third DataTable object with the values that the other two DataTables don’t have in common.
Using a For Each Row inside of another For Each Row (so two for loops) is my current solution for finding the rows that are in one DataTable but not another… but I asked the question in hope that there might be a better way.
Is there not something like… DT1.Column(0).doesNotContain(DT2.Column(0).Items)?.. I mean I know that that doesn’t work but hopefully you see what I’m trying to get at.