Consolidate data from many excel to 1 final excel without changing the Formatting of data

[ansh09]

I need some help on how to consolidate data from many excel (in my case currently its 3 but can be more as well), to one final excel file.

I want that all the sheets from those all individual Excel's should get copied to 1 Final Excel without the Formatting of Data gets hampered. Basically a direct copy and paste sheet by sheet.

I tried using python, but my openpyxl doesnt work as my organization IT has maybe blocked it to get package installed.

Please help me to get the data cosolidation without loosing any formatting of data , how the way it is in individual files it should same come to sheet by sheet to 1 final file.

my all files will have different column structure, there is no standard that the number of columns will be same , i just want to make them consolidated in 1 final file.

my data contains numeric values, there were '0' which i already converted to '-' and those values which were not present for the particular row column combination those cells are been greyed out.

below i am attaching screenshots for reference of my data which i generated using batch macros , currently only 1 sheet per file is there but i need that if i can get help to get a solution for input having more than 1 file and even the files are containing more then 1 sheet.

please help to get the sheets exact to the combines excel without loosing formatting and data.

[jrlindem]

A Batch-Macro is a really nice way to accomplish this.  Another option, if all the files are of the same schema, would be to bring them in using the Directory Tool, filtering down to the ones that are relevant, and then using the Dynamic Import tool taking the full file path.

Now, granted, this method doesn't help preserve formatting, but you could always write the output to a preformatted template (BLOB tool or Run-Command for template handling).  This, for some is a bit simpler to conceptualize than getting into Batch-Macros.  But macro would be a more scalable and stable solution (or required if schemas differ per file).

Hope that helps validate your approach and give you some alternative ideas, -Jay

[ansh09]

i used the batch macro to generate the formatted templates already and there is no issue in it.
just those templates (as visible in screenshots) I require to consolidate them like all files all sheets one by one in final single Excel File.
For that i am not able to work through, any help would be great as i need to combine them without affecting the formatting.

if you can give any sample workflow it would be great, i am using Alteryx Designer 2024.1 version and i need to work by preserving the formatting of data which is already generated in excel file 

the structure of my files cannot be standard as they get generated based on some other logics already built in, i just need the logic how to consolidate by just copy pasting the exact proper sheet from all the file to the one single file. .. please help!!

[jrlindem]

Can you provide a couple of the spreadsheets with fake data and then another one that shows the expected output with the formatting?  From there, I'm sure I or someone else can build out an illustrative solution for you.  -Jay

[ansh09]

Yes, surely I can, I am here uploading 2 spreadsheets(Book1 and Book2) which have the same type of dummy formatted data that i already got out via Alteryx using batch macros. They are all individual files with 1 sheet each; possibly, there can be multiple sheets too sometimes in the data.

I am also uploading the 3rd Spreadsheet (Consolidated.xlsx), which has the data from both the files and all the sheets one after another without affecting the formatting of the data. The whole sheet is copied and pasted as it is in the final file; that's the output I am looking forward to, that it takes all my files, how many of them are, and just starts reading and copying and pasting all the sheets from all the files one after another, without affecting anything. Just the whole data as it is.

Please help me get the solution for this!!