Skip data on Excel column from Power Automate desktop flow

(0) Share

Report

Posted on by 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.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

UshaJyothiKasibhotla 225 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
NathanAlvares24 1,714 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
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.

Was this reply helpful? Yes No
Verified answer

CU16071609-1 6,255 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@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 🚀

Was this reply helpful? Yes No
AdyG 50 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you so much everyone!

Was this reply helpful? Yes No