Need help with dynamically changing column names in Excel

Question

I need to dynamically change column names in an Excel file (columns a-e, let's say). I need to also remove blank columns (some of them have 0 in them and I also need to remove the zero) and rename every column as 1, 2, 3, 4,5. Can someone please show an example workflow that achieves this. I have around 30 files in one folder (all xlsm) and need this done to each excel file in that folder. Thank you so much!

DanFlint · Answer

@slaurelI've attached a workflow which uses a directory input as a control parameter to allow the process to batch through all excel files in the specified directory

It will overwrite the existing data sheet - if you want to retain the original data you can tweak the output tool within the macro to write to a different sheet.

Hope this helps!

CleanAndRenameBatch.yxzp

slaurel · Answer

@DanFlint This is very helpful! Could you please show me to incorporate a batch macro into this, and how would I achieve this using the directory tool?

DanFlint · Answer

Hi @slaurel

My workflow makes empty and 0 values null and then uses the cleansing tool to remove null fields

It then creates the rename lookup using the field info tool and a recordID tool before using a dynamic rename.

You could put this into a batch macro which updates the file name to affect all the files in the directory.

Hope this is what you were looking for!

CleanAndRename.yxmd