Open, change, and save Excel files as the same filename

[Katy_Holder-Vale]

Hi,

I have a folder full of Excel files and want to add a column to each of them but not to change the filename or any other attribute.  I just want to process them in turn add a calculated field and then save the file in the same location and with the same filename thereby overwriting the previous file.

Is there an easy way to do this rather than having to do it one at a time with input, formula, and output tools?

Many thanks,

Katy

[grazitti_sapna]

Hi @Katy_Holder-Vale , You can use a batch macro for this. I am attaching the workflow with the macro. Pleas try it and add the formula in the formula tool inside the macro. Thanks!!

Do accept my solution if it works!!

[Katy_Holder-Vale]

Hi - thanks so much for the response.

It is not quite what I am looking for so please let me try again.

In my folder - "C:\Data" - I have 3 Excel file - "file1.xlsx", "file2.xlsx", "file3.xlsx" and each of these files have numeric numbers in column A only.  What I would like is for the workflow to open each file in turn and add a calculated field in column B which = column A *2, and then for the file to be saved to the original filename.

This is not the answer you provided and easy with 3 as I can setup a sub-workflow for each but what if there are 1000 files?

In reality the files are bigger and have more rows and fields but that is not the bit I am struggling with.  I do not know how to open a list of files, change them and then importantly save as the original filename again.

The last bit is the important bit and do you know how to do this with saving the filename to the file's data and then adding it to the output filed perhaps?

Kind regards,

Katy 

[Emmanuel_G · Accepted solution]

Hi @Katy_Holder-Vale ,

Find attached the way to do that.

All you need to do is change the folder path in the tool contained in the yellow container where your excel files are located.

I created 3 excel data models for this with column A containing numeric data.

The files are all read in the dynamic input, then column B is created and the output path is enriched with the sheet name for the excel so we keep the initial file name and finally the files are exported.

You will find the items attached.

Let me know if there's a problem.

[Katy_Holder-Vale]

Hi,

Many thanks for a great answer and the use of tools I have not explored before.

Kind regards,

Katy

[Emmanuel_G]

@Katy_Holder-Vale  Good to know !

Don't hesitate if there is any questions about these tools.

[Katy_Holder-Vale]

Hi,

I like your solution but it is 'tied' to those files and structure which is a shame.

If Alteryx are listening, it would be good to just have a "Save and Close" tool for files such as Excel because it would allow for changes to be made and files updated without worrying about all the admin.  For example, if I wanted to add a column such as the Month (January, etc...) from a date in the file but nothing else that could be done.

Just a thought,

Katy

[Emmanuel_G]

Hi @Katy_Holder-Vale ,

Everything is possible. Can you share with me a file or many and excepted output to better enlighten you on the case you are talking about ?

[Katy_Holder-Vale]

Hi,

Many thanks for the response but I think that my question is more generic and not as complex as people seem to be taking it.

I understand from all Alteryx's add on LinkedIn that converting Excel users is prime target, so I am surprised that the software is not more supporting of this goal.  I love the software, but it would appear that some aspects are wanting.  

If I have a folder full of Excel files, I cannot believe that there is no open / close process built-in that minimises the workflow requirements for opening the files in turn, performing some tasks, and then closing them with the same name in the same location.  I do not think that this is possible at the moment even though there are some multi-step workflow "solutions" as demonstrated above even though there is some configuration needed in most that mean that this is not idiot-proof and or dynamic in the sense that this is flexible enough to be dropped into another folder and will just work.  I may be wrong but that's my feeling.

So, if I have a simple example as shown below: 

and simply want the file updated to contain the additional column as shown below:

This takes more steps than I believe it ought to.  There ought to be just an Input Data, a Formula with calculation, and an Output Data with a special setting of "update file" which just replaces the file in its location.

It's not a big issue but it is just strange that something this minor but useful isn't addressed.....

Then there is also no "date aggregation" in the Summary tool either which is just mad as I can't be the only one that would use this regularly and am fed up with having to reorder manually dates from the Cross Tab tool.

Do Alteryx do user surveying as I am sure that there is a lot of suggestion out there if they were interested...

Just a thought,

Katy

[Raddad]

Hi Katy,

I'm exactly in the same spot as you and I can't believe how such a basic functionality is only achievable through some very convoluted ways.

Alteryx will never win over "non-coder/light code" excel users like this. 

What solution did you end up using for your process?