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

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

Input

Output

Preparation

Accepted answers

Emmanuel_G

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.

Read excel files.yxzp

All comments

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

Multiple_files_diff_schema.yxmc

New Workflow3.yxmd

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

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.

You will find the items attached.

Let me know if there's a problem.

Read excel files.yxzp

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.