Need help with dynamically changing column names in Excel

[slaurel]

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! 

[ChrisTX]

To change column names:  under the Developer tool group, look at the example for the Dynamic Rename tool.

To remove blank columns and remove zeros:  under the Preparation tool group, use the Data Cleansing tool which has an option to Remove Null Columns.

Chris

[grazitti_sapna]

@slaurel , You can read all excel files at once and then rename the fields using select tool and use data cleansing tool to remove unwanted columns. If your column has 0 value only then you can also de select those columns in select tool itself else you can use multi-field tool and then replace 0 with null and then use data cleansing to remove that column.

Here is sample workflow

[DanFlint]

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!

[slaurel]

@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]

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