Data table and Excel comparison for If activity

Help Studio
1

Hi,

I am running a SQL statement to create a report. This report will be scheduled to run every 15 mins. I want to include an “If, then, else” activity within the sequence. The sequence runs the sql statement then inserts the data into an excel sheet which is stored on my pc. If the data is the same as before or blank I would like for the database to disconnect. If the data is different than the last report I would like for the report to be emailed to users. I have the “Then” and “Else” portion correct. However, I am struggling with the Condition piece in the “if” activity. Can somoene please help i’ve been working on this for days.

2

The “If” portion is going to be very specific to your own data and your own use case. What can you use to identify if the current report matches a previously generated report? Perhaps if all items in a unique identifier match the previous report? What all have you tried so far?

2 Likes
3

I would recommend using the datatable row count for the excel file and the datatable you retrieve from your sql at a first glance.

But it sounds like you want to do a like for like comparison between your excel report and sql datatable

Take a look at this link: c# - How to compare 2 dataTables - Stack Overflow

There are several ways to achieve this by looping through each row and column to compare data or using linq.

2 Likes
4

Are you suggesting that I just I create a report from the data and compare it to the already existing excel file? As of now I only have the excel being created.SQL > Excel application scope > write range > if, then , else

5

You stated you’ve been trying for days to determine what to put in the “if” statement. What all have you tried? How are you comparing the current report to see if it’s the same as the previous report?

If this were me, this is how I would begin trying to do this: First I’d open the previous report which I saved before emailing out and use read range to save as a datatable called OldReport. If the report didn’t exist (meaning this is the first time the process was run) then I’d stop doing any of the following checks and just move straight into processing the NewReport. Then I’d run SQL to save as NewReport and I’d sum up a column in there that contains numbers but is not necessarily meant to be summed, such as ID column and save it to a variable called NewSum. Then do the same on the OldReport, but save it to a variable called OldSum. Then I’d check if NewSum = OldSum. If they are equal, then stop the process. If they are not equal, then save the report somewhere for the robot to check next time and email the excel file to the report users.

1 Like
6

Hi Dave,

I was able to create a new datatable by using the sql query. I also have an read range activity for the report that already exist. I want to compare these two Excel files - if they have the same data I want the db to “disconnect” if they are different I want it to be emailed out to users. However, I am now having trouble with my “condition” within the “If” activity. Anyone have any suggestions. Right now I have “row.ItemArray(0).ToString like line.ItemArray(0).ToString”. I read that 0= column A

7

I"m not quite sure I follow your last sentence. row.itemArray(0).ToString - I am assuming this is in a for each row activity and you named the variable row, is that correct? If so, this should give you the first column of that row and convert it to string (although usually I’d recommend using row.item(0).ToString instead). What is the line variable on the other side of the like operator? I’m also not sure what you mean by “I read that 0= column A”.

8

HI,

Have you tried to work with CSV? If not, try.

I think your file comparison will be easier if the file extension is “text”.

Converting CSV to excel later is relatively simple.

Think about it.

1 Like
9

Yes, it is for an each row activity.Also, I did try row.item(0) I got the same error. The line variable is a data table. I put the variable in the For each row and for each line activity so I didn’t think I would need to set the variable in the “if” activity. I also tried datatable.rows.Indexof = datatable.rows.Indexof that did not help.
I read in another message board that instead of using column “A” you would use numbers. Ex: column A = 0, Column B = 1 etc.

10

@mojisola.ayanwale In the ‘for each row’ activity, there is an option in the properties to output the Index. Do that and save it to a variable (i’ll name the variable CurrentRowIndex, but you can name it anything).

Then in your if statement from above change it to be row.item(0).ToString which gets the current row, column A and converts the value to a string. The same thing in your “line” datatable would be line.rows(CurrentRowIndex).item(0).ToString. If you want to compare the two strings you can just use the ‘=’ or ‘like’ operator, but my preference is to use Strings.Equal method. So your if statement would be: Strings.Equal(row.item(0).ToString,line.rows(CurrentRowIndex).item(0).ToString)

11

I have two For each activities, should I create a variable in the output for both our just for the
first one?

image
image747×364 8.87 KB

12

I would not nest the for each as that would significantly increase processing time. I would actually do it a couple ways.

First compare if dt1.rows.count = dt2.rows.count. That is a super fast way to check if they’re different. If they’re different you don’t need to check anything else.

If the count is the same, then you can do your line-by-line comparison. To do this without nested for each you should sort both datatables by your sorting column (i’ll assume the first column), then do a for each loop on one and check the other using the same row index like i mentioned above. You can use the ‘sort data table’ activity to do the sorting.