Skip data on Excel column from Power Automate desktop flow

[AdyG]

I have Excel data on same column from 2 different dates like below:

However to read the data, I have to ignore only the value that says "2-Jun". So in all, I need to read the 6 rows.

It could be any date, so I can't make it to filter out just "2-Jun".

Also I can't get rid of this date. It has to be there on the original file, after I do the manipulations based on the 6 rows.

Things I tried but were not successful:

1. Reading the Excel data using SQL queries, couldn't get rid of this cell temporarily.

2. Set a list variable to have all dates from 1st to 31st and put for each loop to read the number.

Please guide me on how I can do this.

Thanks.

[UshaJyothi20]

Hi @AdyG 

Will the date be same format????

If so 

Fst read the excel using launch excel and read Excel sheet then use find and replace datatable activity then give regular expression from for the date formats 

Then you can delete the date value from the index you get from find and replace activity and you can write it into new sheet or new excel sheet.

Hope this helps,

Usha 

[NathanAlvares24]

Hi @AdyG !

Follow this flow.

My excel file what it looks like:

Configuration of some actions:

The regex used in action 3:

^\d{1,2}-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[a-z]*$

Code (just copy and paste into your flow):

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Users\\UserName\\Desktop\\Book1.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> Column_Data
Variables.FindOrReplaceInDataTable.ReplaceItemInDataTableWithRegexEverywhere DataTable: Column_Data AllMatches: True ValueToFind: $'''^\\d{1,2}-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)[a-z]*$''' TextToReplaceWith: $'''%''%''' DataTableMatches=> DataTableMatches
LOOP LoopIndex FROM 0 TO Column_Data.RowsCount - 1 STEP 1
    SET varColumn_Data TO Column_Data[LoopIndex][0]
    IF IsEmpty(varColumn_Data) THEN
        Variables.DeleteRowFromDataTable DataTable: Column_Data RowIndex: LoopIndex
    END
END
Excel.CloseExcel.Close Instance: ExcelInstance

Output:-

Before (when reading data):

After(1. replace dates using the regex and then 2. delete the empty rows obtain a filtered data table):

1.

2.

I hope this helps.

[Deenuji · Accepted solution]

@AdyG 

You can follow the below approach:

Code:

Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Boot\\Q.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: False RangeValue=> Column_Data
SET Counter TO 0
LOOP FOREACH CurrentItem IN Column_Data
    Text.ParseText.RegexParseForFirstOccurrence Text: CurrentItem[0] TextToFind: $'''\\b\\d{1,2}-[A-Za-z]{3}\\b''' StartingPosition: 0 IgnoreCase: False OccurrencePosition=> Position Match=> Match
    IF Position = 0 THEN
        Display.ShowMessageDialog.ShowMessage Title: $'''Info''' Message: $'''Its date value: %Match%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
        Variables.DeleteRowFromDataTable DataTable: Column_Data RowIndex: Counter
        SET Counter TO Counter - 1
    ELSE
        Display.ShowMessageDialog.ShowMessage Title: $'''Info''' Message: $'''Not matched with Date format: %CurrentItem[0]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
    END
    SET Counter TO Counter + 1
END

Thanks,
Deenuji Loganathan 👩‍💻
Automation Evangelist 🤖
Follow me on LinkedIn 👥

-------------------------------------------------------------------------------------------------------------
If I've helped solve your query, kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[AdyG]

Thank you so much everyone!