Rename Headers in All Sheets

[ricoo]

Hi All,

May I ask help on this. I have an excel file with multiple sheets. All of the sheets have the same headers and structure.

In the example below, I have 3 sheets called “Barcode”, ”Description”, and “UOM”.

Notice the cells that I’ve highlighted.

My goal is to rename the 4th column header (“Value”) to be the SheetName or the actual value of the 3rd column (eg. For the Barcode sheet, the header “Value” will be renamed as “Barcode”; For the Description sheet, the header “Value” will be renamed as “Description”, and so  on.) And the column “Column” will then be deleted.

The desired output will look like this.

   

Is this possible in alteryx?

   

[RaphaelSilva · Accepted solution]

I bot of a workaround, but in the end it works.
Isolate the data, the value and column, then after some prep join them together by RecordID.

 

[ricoo]

Hi @RaphaelSilva thanks for your quick response. That was helpful, but I'm still trying to figure out how to update it in an existing spreadsheet.

[RaphaelSilva]

The sample data is from your examples, that should work for your files.
What errors would you face?

[usmanbashir · Accepted solution]

@ricoo - I have attached a workflow that writes to a new file (for testing). Adapt for the workflow for your need. Another note is that you're unable to have two fields having the same header name (ex: Description and Description). It'll rename the 2nd header to Description2

Hope this helps.

**just updated to exported workflow

[Raj]

@ricoo
As per comments above now you know how the dynamic rename is done and want to update this in existing Excel file
so, what is suggest is when you bring in the fill bring the file name as well and using the formula tool you can add the sheet name as well
after this you should use the output tool and use the created column to take the file path to update the existing file.

[flying008]

Hi, @ricoo 

If you want to figure out how to update it in the existing spreadsheet, maybe you can use run command tool to run bat code for get it.

Spoiler

@echo off &&setlocal enabledelayedexpansion &&@echo Change D Header of each sheet From ***' SourceFile '*** ... &&powershell -Command "Add-Type -AssemblyName 'Microsoft.Office.Interop.Excel'; $objExcel = New-Object -ComObject Excel.Application; $objExcel.Visible = $false; $objFile = 'SourceFile'; $wkInput = $objExcel.Workbooks.Open($objFile); foreach ($wkSheet in $wkInput.Worksheets) { if ($wkSheet.Range('E1').Value2 -eq $null) { Write-Host 'E1 cell is empty in sheet ' + $wkSheet.Name + ' Exiting...'; } else { $d1Value = $wkSheet.Range('C2').Text; $wkSheet.Range('D1').Value() = $d1Value; $wkSheet.Range('C:C').Delete(); $wkSheet.Columns.AutoFit() } }; $wkInput.Save(); $wkInput.Close(); $objExcel.Quit();" &&@echo *** All done ! ***

[ricoo]

Hi @RaphaelSilva , I tried your method. It really was helpful. I tried to take it a step further to simplify the process without isolating the fields. I've done it by using an Action Tool pointing to a Text Input tool wherein I've specified the Old and the New Field names that will be used by the Dynamic Replace tool. And by using a Select Tool to deselect the unwanted column. But this is just one portion of the problem. I'm still struggling though on the process that updates all the sheets in Excel. Still Thank you very much for your idea, since it has solved half of my problem, I'll accept this as a solution! Attached is my solution though, if you're interested on checking it.

[ricoo]

Hi @usmanbashir , I tried your workflow. Your solution on renaming the "Value" column and removing the "Column" column also works. However, I wanted the process to be dynamically opening/updating the sheets based on a result data. I really appreciate your help but the sample you've given needs to manually specifying the sheets in 3 different Input Tool.

 

I was wondering if there's a way that opens the Spreadsheet automatically according to the Sheet Names from the result data and do the renaming and removal of column on that specific sheet. But since your suggestion has helped me solve half of my problem, I'll mark this as well as accepted solution

[ricoo]

Hi @flying008 , I've tried using bat files for a few times only. So I'm not that very familiar on using this. I can see from the image you've shared that it has solved the problem I've raised so I'll still try your suggestion. But I'm going to get myself comfortable first with using bat files. But still Thank you very much for your suggestion, it's really appreciated!