Filtering Excel files

Hi all,

I want to make a process with entering data from Excel (mostly some dates) into Salesforce, but there is no need to enter all dates, just certain, so I would like to ask is there possibility to filter Excel report, to highlight only dates that I need, and then to type data from filtered report?

Thanks :slight_smile:

Hi Sanja,

There are at least two ways you can do it:

  • Open the excel file, filter the data and copy it using keystrokes and clicks on window controls.
  • Open the file, filter the data, copy filtered data to a new sheet and save the file using keystrokes and clicks on window controls. Then copy the data using excel actions.

Hope this helps.

Thanks for the feedback. Actually, I wanted to ask you is it possible to delete all row and to leave only rows which in some columns have date equals today or yesterday. I’ll screenshot an example:

I would like to extract only rows where is any highlighted cell, how could I do? Is it possible to make Table variable in workfusion studio with these data and iterate through it, and extract only rows which contain yesterday and today date?

You can use IF condition in this case and compare the value in a particular cell to the current date.
But if you need to compare with yesterday, too, you will need to add actions to calculate that date.

I’ll try to make this process, based on your instructions, and then I’ll post comment here.

Thanks a lot for help!

1 Like

I think this thread will be helpful, too

1 Like

I’ll review this post and try to implement my process. Also, screenshots of process and comments will be posted here.

1 Like

Hi,

I deleted two first rows in Excel file (I don’t need data in them), after that I extracted columns that I need into lists, and those lists I appended to a Table variable, I attached a screenshot. Also, I used Constant Value and Date Format to get the current date and to give that value to the string variable.
My question is, how to delete every row in which any cell doesn’t have the current date? I need only rows which contain the date that’s equal today or yesterday, but let’s focus now only on the current date.

I think I found a solution (filtered by current date), if process succeeds, I’ll put the screenshot of actions here.

1 Like

Hi Alesia,

I worked on this process, there are no errors, but it doesn’t do filtering completely…Here is part of the program, could you give me suggestion how to extract rows with today date?

part3

Here are screenshots of all actions, I had to do date formatting and these actions with text, but focus should be on the IF condition at the end, I think that there is a mistake. I saw an execution log, and variable FILTERED TABLE is empty, so I think that in this condition is a mistake.

Thanks a lot :slight_smile:

Actually, I think that error is in comparing dates, in this process, the ELSE in IF condition is executed
part4

@svilimanovic what are the values of element and today_string variables?

I made some modifications in my process, here is execution log.


I couldn’t upload the file, so here is the screenshot.

There is no element variable in the log.
You can add actions before the For Each loop to print the values of element and today_string variables to a notepad to see if they are the same, and then see which variant in the IF condition is executed.

A variable ELEMENT is each column in a new_table variable when we iterate through it with FOR loop, I don’t know if I explained you well, do you understand me?

Sorry, element[1], element[2], …are columns

excel_filtering_part2.rar (2.7 KB)
@ashapkina here you can see all actions.

I cannot open this file.

To check the values, add actions to type them to a notepad. Then you can see if the the values are the same. Perhaps, the date in the list ${element} is written differently from ${today_string}, that’s why the bot cannot find it.

Yes, I was thinking about that, that problem is with comparing dates. Because process always goes in ELSE, even if I have today date in the spreadsheet. I’ll try this with notepad, but I just wanted to check with you are all other conditions correct.

It is hard to say without seeing the details. Could you zip the folder with your recording and share here?