Power Automate FIle Name Extract

[rrahat93]

Hello, 

I am a new PAD user. I am trying to build a flow that will extract first 5 character of the file name from a folder and then match the 5 char with an excel file column a and if finds match it will rename it using column c. But I am having trouble make it to work. 

I also tried this

for some reason %TextList[0]% is not doing anything. Can someone please help

[Deenuji]

@rrahat93 

You have created your desktop flow with power fx enabled features since it's in-preview stage and it have lot of limitations as well.

Please find the below known limitations for power fx enabled flow.

Please replicate your desktop flow by deactivating that option as shown below, and implement similar logic, it should work properly

.

In case if you want to continue with power fx enabled flow with known limitations then use the below syntax:

Index starts from 1 in power fx 

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 🚀

[rrahat93]

@Deenuji  if i change I disable power FX, do you think my flow will work?

[Deenuji]

@rrahat93 

In case if you want to continue with power fx enabled flow with known limitations then use the below syntax:

Index starts from 1 in power fx 

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 🚀

[rrahat93]

I rebuilt it and disabled power fx. but file name are not changing. 

my file name is in this format 00023_2024-MAR-U002K3Q4

[VishnuReddy1997]

Hi @rrahat93 ,

Please find the solution. Please Disable the Power FX option before creating the flow.

Code:

System.TerminateProcess.TerminateProcessByName ProcessName: $'''EXCEL'''
Folder.GetFiles Folder: $'''C:\\Users\\OneDrive\\Desktop\\Power Automate Desktop\\Practice\\Excel\\Excel Input''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
LOOP FOREACH CurrentItem IN Files
    Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: CurrentItem.FullName Visible: True ReadOnly: False Instance=> ExcelInstance
    Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: $'''A''' StartRow: 1 ReadAsText: False CellValue=> A_ColumValue
    Excel.ReadFromExcel.ReadCell Instance: ExcelInstance StartColumn: $'''C''' StartRow: 1 ReadAsText: False CellValue=> C_ColumnValue
    Excel.CloseExcel.Close Instance: ExcelInstance
    SET FileName TO CurrentItem.NameWithoutExtension
    Text.SplitText.SplitAtNumberOfCharacters Text: FileName SplitInterval: 5 Result=> Five_FileName
    SET First_Five_FileName TO Five_FileName[0]
    Text.Trim Text: First_Five_FileName TrimOption: Text.TrimOption.Both TrimmedText=> First_Five_FileName
    IF First_Five_FileName = A_ColumValue THEN
        File.RenameFiles.Rename Files: CurrentItem.FullName NewName: C_ColumnValue KeepExtension: True IfFileExists: File.IfExists.DoNothing RenamedFiles=> RenamedFiles
    END
END

(Note:- if you got your solution you can mark as solution and gives kudos)

Thanks & Regards

Vishnu Reddy

[Deenuji]

@rrahat93 

How is your folder files and excel files looks like?

Share some screenshot on the same.

[rrahat93]

NewVar Variable is giving this 

[rrahat93]

@Deenuji 

[VishnuReddy1997]

Hi @rrahat93 ,

Try My solution and let me know.

Regards,

Vishnu Reddy

[Deenuji]

@rrahat93 

Do you intend to truncate the filename obtained from the folder, verify if the truncated filename exists in column A, and if there's a match, extract the corresponding name from column C to rename your file accordingly? Is this the workflow you wish to create?

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 🚀

[rrahat93]

@Deenuji That is correct.

[Deenuji]

@rrahat93 

Try the below proposed flow:

Code:

Folder.GetFiles Folder: $'''C:\\Deenu\\pdf''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Boot\\checkfilename.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
LOOP FOREACH CurrentItem IN Files
    Text.SplitText.SplitWithDelimiter Text: CurrentItem.Name CustomDelimiter: $'''_''' IsRegEx: False Result=> TextList
    LOOP FOREACH ExcelFileItem IN ExcelData
        IF TextList[0] = ExcelFileItem[0] THEN
            Display.ShowMessageDialog.ShowMessage Title: $'''test''' Message: $'''Matching

FIle Name:
%CurrentItem.Directory%\\%ExcelFileItem[2]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
            File.RenameFiles.Rename Files: CurrentItem.FullName NewName: $'''%CurrentItem.Directory%\\%ExcelFileItem[2]%''' KeepExtension: True IfFileExists: File.IfExists.DoNothing RenamedFiles=> RenamedFiles
            EXIT LOOP
        END
    END

How to copy/paste the above code into your desktop flows? Refer below image.

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 🚀

[rrahat93]

@Deenuji getting this error on first for each action

[rrahat93]

@Deenuji never mind sir. your solution worked. This is crazy.

[Deenuji]

@rrahat93 

I am glad to hear that 🙂 Kindly mark my response as the solution ✔ and give it a thumbs up!👍 Your feedback supports future seekers 🚀

[rrahat93]

@Deenuji I last question. What if I have a file that 10 digit at the beginning i.e., 0002312345_2024-MAR-U002K3Q4 instead of 00023_2024-MAR-U002K3Q4. and I want to split only first five of that file name, is it possible?

[Deenuji]

@rrahat93 

Use another split action like below to trim first 5 characters

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 🚀

[rrahat93]

@Deenuji do i need to use any if statement if both type of files exist in the folder?

[Deenuji · Accepted solution]

@rrahat93 

Slightly modified to incorporate the above logic

Code:

Folder.GetFiles Folder: $'''C:\\Deenu\\pdf''' FileFilter: $'''*''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> Files
Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\Boot\\checkfilename.xlsx''' Visible: True ReadOnly: False Instance=> ExcelInstance
Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData
LOOP FOREACH CurrentItem IN Files
    Text.SplitText.SplitWithDelimiter Text: CurrentItem.Name CustomDelimiter: $'''_''' IsRegEx: False Result=> TextList
    SET TrimmedFilename TO TextList[0]
    IF TrimmedFilename.Length = 10 THEN
        Text.SplitText.SplitAtNumberOfCharacters Text: TrimmedFilename SplitInterval: 5 Result=> TrimmedFilename
    END
    LOOP FOREACH ExcelFileItem IN ExcelData
        IF TrimmedFilename = ExcelFileItem[0] THEN
            Display.ShowMessageDialog.ShowMessage Title: $'''test''' Message: $'''Matching

FIle Name:
%CurrentItem.Directory%\\%ExcelFileItem[2]%''' Icon: Display.Icon.None Buttons: Display.Buttons.OK DefaultButton: Display.DefaultButton.Button1 IsTopMost: False ButtonPressed=> ButtonPressed
            File.RenameFiles.Rename Files: CurrentItem.FullName NewName: $'''%CurrentItem.Directory%\\%ExcelFileItem[2]%''' KeepExtension: True IfFileExists: File.IfExists.DoNothing RenamedFiles=> RenamedFiles
            EXIT LOOP
        END
    END
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 🚀