Create Folder based on a particular column having unique values.

[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

[Agnius]

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.

[Sumitra_Sai]

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.

[UshaJyothi20]

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

[Agnius]

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.

[Sumitra_Sai]

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.

[Agnius]

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.