How to create a column in each new excel file saved in a folder

[danboc555]

Hello,

I receive on daily basis an excel file with same number of columns. The file has the format date:  sales-mm-dd-yyyy.xlsx

How can I create a flow to create a new column for each new excel file save, where the content in all rows of the new column is the mm-dd-yyyy of the specific file?

Thanks,

[Nived_Nambiar]

Hi @danboc555 

Try this approach 

Hope it helps !

[MichaelAnnis]

If you can’t get that to work, I highly recommend VBA for anything that is strictly Excel work.  Happy to help. Let us know if you continue to have issues. It’s definitely doable with PAD, just faster and more functionality with VBA. 

[danboc555]

Hi @Nived_Nambiar , thank you for your reply.

I followed the steps, it runs correctly but the saved excel file remains unaffected.

The first steps should be intended to capture the file's name, which is already given in the format: "filename mm-dd-yyyy.xlsx"

The idea is to put that date in a new column "B" in all the available rows, and save the file with the changes. Any ideas?

[Nived_Nambiar]

Hi @danboc555 

While using close excel action to close excel instance, are you using save document option in that action ?

[Agnius]

You can use the Get files in folder action to retrieve all files, then loop through them and for each file use the %CurrentFile.NameWithoutExtension% value to get its name. You can then use the Parse text action with a regex using the following pattern:

(\d{2}-){2}\d{4} 

This will return the date from the name. You can then use that to write it to Excel.

[danboc555]

Hi @Agnius, I tried to incorporate your steps in the current model as follows but nothing happens. I'm not sure what I'm doing wrong, I'm keeping excel closed. Thank you

[Agnius]

There are plenty of things you are doing wrong there.

First of all, your %CurrentFile% variable contains a list of files. Get files in folder will always return a list. Even if there's only one file to retrieve, it will return a list of one item. So, you need to use a For each loop to iterate through that list in order to get a single file.

Then, you can use Parse text on each of those files (in a loop). You need to parse the %CurrentFile.NameWithoutExtension%, as the %CurrentFile% will in fact contain a lot more properties. It's a variable of type File, which contains various properties, including the name of the file, its path, its modification date, creation date, etc. So, what you need, is to parse the specific property that can be reached via the .NameWithoutExtension method.

Finally, you need to enable the 'Is regular expression' toggle in the Parse text action:

This is what the above would look like:

Then, whatever you want to do next with the date that you have retrieved should also be done within the loop action, so all of the actions are performed for each file.

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

If you are interested in Power Automate, you might want to follow me on LinkedIn at https://www.linkedin.com/in/agnius-bartninkas/

[danboc555]

Hi @Agnius , 

Thanks for your reply, I missed to mention I'm a beginner with Power Automate, so I'm still stuck with the following steps as shown.

The idea is to run the flow on daily basis when I receive the new file, so it doesn't have to be applied to the previous existing files. Any ideas please? 

[Agnius]

If you want the flow to not apply to previous files, I would suggest doing one of the following:

• Use Move file(s) to move the file to a different directory after it has been processed, or
• Use Rename file(s) to add some sort of a keyword to the file name, so that you know it was processed and can skip it later, or
• Apply sorting in the Get files in folder action to sort by creation time in descending order, so that you have the latest files at the top, and then have a condition inside your loop, so that you do Exit loop when the creation time of the current file is less than the current date.

The reason your flow fails in the screenshot below is because you are passing in a folder path into the Launch Excel action. Instead, you should use %CurrentItem% as the file path. 

It will also fail on the Save Excel action, too. You should simply use Save and not Save as. If you do a Save As, you need to provide the full path to the file, and not only the directory.

Also, you are missing a Close Excel action. If you don't close it, the document will stay open and read-only, until you manually terminate the Excel process via task manager.

-------------------------------------------------------------------------
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.