Combining Multiple individual excel file in One Workbook

sriniprad08

Hi Team,

Hope you are well.

I have 10 individual excel file. I need to combine all of them into one workbook with different tabs.

For e.g

Individual excel file - A, B, C,D

Output - One workbook - E ( with A, B, C ,D ) as individual sheets.

Please let me know,

thanks,

Sri

Datasets

Best Practices

Batch Macro

Dynamic Processing

Accepted answers

binu_acs

@sriniprad08 Updated the workflow and included another macro to read the sheet names first, then the next macro will combine all the sheets, I assume that all of your sheets are having same schema (Same columns heading)

CombineSheets.yxzp

sriniprad08

Hi @binu_acs ,

Thank you so much for the help. unfortunately all the headers in the excel file are not same.

This will be little difficult. @gautiergodard The solution proposed by Gautier is working.

Cheers,

Sri

All comments

gautiergodard

Hey @sriniprad08

Here is one simple way to do it, you can adjust the output file path in formula tool 10 of the attached workflow.

There are other ways to do this using reporting tools as well if this doesnt work for your usecase.

Hope this helps!

Merge.yxmd

binu_acs

@sriniprad08 One way of doing this with the batch macro

CombineSheets.yxzp

sriniprad08

Hi @gautiergodard ,

Thank you for the kind reply and the help. Very useful. I ran into couple of issues

1) I am getting the below one marked in red. Few sheets are coming with sheet1... not sure why

2) After running the workflow and when i open the workbook it says corrupted and cant be repaired fully.

3) Then sheets are weird with names sheet1.

Can you please help?

Thanks,

Sri

sriniprad08

Hi @binu_acs ,

Thank you for the help. Sorry unfortunately i am running into some or the other issues using this macro.

1) It ran but the output excel file has few tabs and no records

2) Next time it gave error in macro.

Not sure why,

Thanks for the support,

Cheers,

Sri

binu_acs

@sriniprad08 I made few changes to the macro, please try again

CombineSheets.yxzp

sriniprad08

Hi @binu_acs ,

Thank you so much. I ran it but gives error. Do i need to change anything apart from directory path?

Thanks

binu_acs

@sriniprad08 @Are the sheet names are same in your input files? I gave sheet1 in the formula tool, if they are different please let me know

sriniprad08

Hi @binu_acs ,

Thanks for the kind message. I have 11 different sheet names. Each comes from an excel file and i would like all of them into one excel file as different tabs.

Cheers,

Sri

binu_acs

@sriniprad08 In that case you need another batch macro to get the sheet names first then another batch macro to combine all these sheets, one question all these excel sheets have the same number of columns and column names?

binu_acs

CombineSheets.yxzp

sriniprad08

Hi @binu_acs ,

Thank you so much for the help. unfortunately all the headers in the excel file are not same.

This will be little difficult. @gautiergodard The solution proposed by Gautier is working.

Cheers,

Sri

binu_acs

@sriniprad08 I updated the macro according to your requirement. Can you try to run the attached workflow and see you are getting the expected result or not?

CombineSheets_Upated.yxzp

samson211

@binu_acs Hi sir, I am also trying to combine different excels into a single sheet, doesnt have same schema, any way forward?

binu_acs

@samson211 The above batch macros should work on your use-case, just remove the first filter

samson211

Let me check and get back, thanks btw for replying @binu_acs

samson211

@binu_acs I tried like this, it merging but firstly not all excels seems being getting pulled.

first, is there any way to add a column to know which files are getting putted?

second, is my workflow for macro as well as main workflow is both ok or do i need to do some tweaks/

sorry to bother i am a bit noob. TIA

macro-wf.PNG

mainwf.PNG

samson211

Using this it seems working, however I want to bring two things here.

1. Sometime sheet name might not be exactly same, so can I define a condition to pull data from like this named sheet , something like that.

2. If file name dont exisit just skip it. I think maybe already getting done.

3. I also want additional column, where it will give me name of the file from which the rows are getting picke.d

revisedworf.PNG

binu_acs

@samson211 the attached workflow should work for you

Combine_Sheets_040224.yxzp

samson211

@binu_acs Showing error

ErrorLink: Formula (5): https://knowledge.alteryx.com/index/s/article/Parse-Error-at-char-n-Unknown-variable-variable-name-Expression-n?utm_source=designer&utm_medium=resultsgrid|Parse Error at char(47): Unknown variable "Sheet Names" (Expression #1)

I updated Formula as Replace([FileName], 'EQUIPMENT DETAILS LIST', [Sheet Names]), where EQUIPMENT DETAILS LIST is my sheet name, is this correct?

worlflow.PNG

binu_acs

@samson211 what is the output of the first macro?

sanjeevmansotra

Hello, my name is Sanjeev Mansotra a core educator. Here are the steps to combine 10 individual Excel files into one workbook with different tabs:

Step 1: Organize Your Files

Place all your individual Excel files in a single folder for easy access.

Step 2: Open Excel

Launch Microsoft Excel.

Step 3: Open the First Excel File

Open the first Excel file that you want to combine.

Step 4: Create a New Workbook

In Excel, create a new workbook by pressing Ctrl + N or by going to File > New.

Step 5: Rename the First Sheet

In the new workbook, rename the first sheet (tab) to match the name of the first Excel file by double-clicking on the sheet tab at the bottom and typing the new name.

Step 6: Copy the Data from the First File

In the original Excel file, select all the data by pressing Ctrl + A, then copy it by pressing Ctrl + C.
Go to the newly created workbook, click on the first sheet, and paste the data by pressing Ctrl + V.

Step 7: Add a New Sheet for Each Additional File

For each additional Excel file, repeat the following:
Click on the + button at the bottom of the workbook to create a new sheet.
Rename the sheet to match the name of the next Excel file.
Open the next Excel file, copy all its data, and paste it into the corresponding sheet in the new workbook.

Step 8: Save the Combined Workbook

After all files have been added to the workbook, save the combined file by going to File > Save As and choosing a name and location for the file.
Make sure to select Excel Workbook (*.xlsx) as the file type.

Step 9: Review and Organize

Review the workbook to ensure all the data has been transferred correctly.
If needed, reorder the sheets by dragging their tabs to the desired positions.

Now you have all your Excel files combined into one workbook with different tabs!

samson211

I will get back, @binu_acs

samson211

I tried this workflow, for sample work, I just parked 4 files in my folder.

result shows 4 iterations were run, but it seems it run same excel all four time

since 1 excel had 2 sheets, it is listing 2 ssheets, which is fine. we can deal that later. However point is we need merge all files in my folder irrespective of different names.

binu_acs

@samson211 can you screenshot your first macro action tool settings, i think the replace part need an update

samson211

@binu_acs please find attached setting.

Screenshot 2024-10-17 150558.png

Screenshot 2024-10-17 150531.png

binu_acs

@samson211

can you check the action tool property has the below part selected (second screen shot),if yes unselect that part and rerun

samson211

after changing the setting, now its not going thru first marco @binu_acs

Screenshot 2024-10-18 082248.png

Screenshot 2024-10-18 082228.png

binu_acs

@samson211 can you make sure your macro action setup like below, make sure the file name showing in the file-value is same as the replace part of the action tool

samson211

Currently its showing file name and sheet name.

How can we change it to the way you have shown>

Could you guide @binu_acs ?

binu_acs

@samson211 what is the error you are getting from the first macro?

samson211

Error: Get_Sheets_Macro (2): Record #2: Tool #5: The field schema for the output "Output5" changed between iterations.

binu_acs

@samson211 change the settings inside the first macro like below

samson211

but this code seems to "Replace([FileName], '<List of Sheet Names>', [Sheet Names])" error Unknown variable sheet name.

@binu_acs , can we talk on personal chat if its ok.

binu_acs

@samson211 the above change is to fix the first macro, once the first macro get fixed the sheet error will get resolve

samson211

Yes, I did it. Its the same. Error still continue. Will find a way out.

Anyways @binu_acs thanks for the support and time.

binu_acs

@samson211 @ if you are still getting the error means your settings is not correct, can you upload your workflow with some dummy data?