Update Formatting of Multiple excels Multiple Tab

[snigdha25]

Hi Everyone, I want to edit the formatting across all tabs of all excel files present at a folder location . Currently all columns are stored as text type but I want to update number and Date formatting wherever possible. Issue is that data that needs to be edited starts from different row numbers in each of the tabs of an excel.
Note- schema of tabs in one excel workbook matches the other excel workbook file.

Example

Tab-1 (data starts from row 16) and the date in column C needs to be edited to date format

Tab-2 (data starts from row 11) and the numbers in column D,E,F needs to be edited to number format

Attaching excels to be as Input and expected output for working/reference purpose.

[mceleavey]

Hi @snigdha25 ,

I've attached a workflow that will load in all of your worksheets with like for like columns and union together based on common sheets.

The first tool loads in the tab names, creates a full path including the tab name, then the second loads them all in and unions together.  I have then done the cleansing you require.

I have split this for the two required tabs in the workbooks:

This gives the following results:

Hope this helps,

M.

ps - if you are running a later version of Alteryx and can't get an error opening the attached file, please refer to @atcodedog05 's blog which you can find HERE on how to fix this error.

M.

[snigdha25]

Thanks a lot @mceleavey.It works like a charm. The issue however now I am facing is writing this in an excel file. I do not want to discard the initial rows of each excel and update the format too. Example- after editing the format of rows 16 onwards in first tab I want to write them back in the same excel from row 16 onwards without removing 15 and above rows

[mceleavey]

@snigdha25 ,

no problem, my first recommendation would be to stop using Excel.

The second would be to output to ranges in the output tool:

The third, if you need to maintain colours etc in the Excel sheet is to stop using Excel, but if that's not feasible, then use the reporting tools to build the output to match current format.

Alternatively, you can write the data to a hidden sheet which then populates the required cells.

M.

[snigdha25]

@mceleavey Unfortunately I cannot omit out excel usage here. I am facing issue in dynamically allocating both tab and sheet name to be written at the same time. Any suggestion's here?