Unanswered

Create Folder based on a particular column having unique values.

(0) Share

Report

Posted on by Sumitra_Sai

Hi, I want to create a folder based on a particular column having unique values.

I have an Excel File having column names Entity, Code, Year, Number, Admin, Draft No, Date. I want to create folder from the column Year which has 2022 as its values. Let me show the order,

[Year]\[Code]\[Number]\[Draft No]

2022 \ ABC \ N1 \ Draft1

2022 \ DEF \ N1 \ Draft1

.....

Similarly, we need to create folders and subfolders.

Kindly assist me in achieving this.

Thanks in advance

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (0)

Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report
Well, first of all, you will need to read the data from the Excel file. Assuming you haven't done that already, you will need to use Launch Excel to open the file, Read from Excel worksheet to get the data, and then Close Excel to close the file.

When you have read data from the Excel worksheet, you will have a data table called %ExcelData% that you can use. You should then use a For each loop on the table, to iterate through the rows and create all of those folders by using Create folder. It makes sense to check if the folder doesn't exist before creating it using the If folder exists action.

The idea of the loop is somewhat like this with several conditions and Create folder actions for each sub-folder:

Here's a snippet of the entire flow that you can copy and paste into your PAD flow designer to have all of the actions created for you:

SET RootDirectory TO $'''C:\\RPA''' Excel.LaunchExcel.LaunchAndOpenUnderExistingProcess Path: $'''C:\\RPA\\Test.xlsx''' Visible: False ReadOnly: False Instance=> ExcelInstance Excel.ReadFromExcel.ReadAllCells Instance: ExcelInstance ReadAsText: False FirstLineIsHeader: True RangeValue=> ExcelData Excel.CloseExcel.Close Instance: ExcelInstance LOOP FOREACH CurrentItem IN ExcelData IF (Folder.IfFolderExists.DoesNotExist Path: $'''%RootDirectory%\\%CurrentItem['Year']%''') THEN Folder.Create FolderPath: RootDirectory FolderName: CurrentItem['Year'] Folder=> NewFolder END IF (Folder.IfFolderExists.DoesNotExist Path: $'''%RootDirectory%\\%CurrentItem['Year']%\\%CurrentItem['Code']%''') THEN Folder.Create FolderPath: $'''%RootDirectory%\\%CurrentItem['Year']%\\''' FolderName: CurrentItem['Code'] Folder=> NewFolder END IF (Folder.IfFolderExists.DoesNotExist Path: $'''%RootDirectory%\\%CurrentItem['Year']%\\%CurrentItem['Code']%\\%CurrentItem['Number']%''') THEN Folder.Create FolderPath: $'''%RootDirectory%\\%CurrentItem['Year']%\\%CurrentItem['Code']%''' FolderName: CurrentItem['Number'] Folder=> NewFolder END IF (Folder.IfFolderExists.DoesNotExist Path: $'''%RootDirectory%\\%CurrentItem['Year']%\\%CurrentItem['Code']%\\%CurrentItem['Number']%\\%CurrentItem['Draft No']%''') THEN Folder.Create FolderPath: $'''%RootDirectory%\\%CurrentItem['Year']%\\%CurrentItem['Code']%\\%CurrentItem['Number']%''' FolderName: CurrentItem['Draft No'] Folder=> NewFolder END END

Note: You need to change the value of %RootDirectory% in the Set variable action, as well as the path to your Excel file in the Launch Excel file. I used sample directories.

-------------------------------------------------------------------------
If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
Sumitra_Sai 65 on at

Like (0)

Report

Hi @Agnius , thanks for your reply.

But when I try to proceed further for creating sub folders inside the "Code" , it's throwing error

Kindly help me to resolve this issue.
Thanks in advance.

Was this reply helpful? Yes No
UshaJyothiKasibhotla 203 Moderator on at

Like (0)

Report

Please check
As @Agnius agnius said it should work
i think you did not give the parameter doesn't exist
you gave folder exist
please check the below image

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (1)

Report

Instead of using %NewFolder%, I would recommend using the full path the way I defined it. This is because %NewFolder% will not be initialized if the folder does exist and the previous Create folder action is skipped. So, you might end up creating a sub-folder into a folder created by the previous iteration of the loop.

-------------------------------------------------------------------------

If I have answered your question, please mark it as the preferred solution. If you like my response, please give it a Thumbs Up.

I also provide paid consultancy and development services using Power Automate. If you're interested, DM me and we can discuss it.

Was this reply helpful? Yes No
Sumitra_Sai 65 on at

Like (0)

Report

Thanks @Agnius , it worked!!

And I have one more doubt.
I have an Excel File which has 2 WorkSheets, one is Input File and the other is the Input Template. Now, I need to get all the Work Sheet Names and check whether there's input template in the worksheets based on an image present inside the template. After finding it out, I need to extract the Input Template Worksheet and convert it to a separate Excel File which acts as a Input Template.

Kindly help me to resolve this issue.
Thanks in advance for your help.

Was this reply helpful? Yes No
Agnius Bartninkas Most Valuable Professional on at

Like (0)

Report

This is a separate question, unrelated to the original topic. Please mark my reply as a solution to this one and create a new topic for the new question. This makes it easier for others possibly having similar issues to find solutions in the future, when they're organized properly.

Was this reply helpful? Yes No