Append sheets from 2 or more excel files

[Stoneage8988]

Hello,

I am quite new to Alteryx and I have one quick question about how to merge different sheets from different excel files.

Basically now I have 2 excel files, file A contains sheet 1 and 2, file B contains sheet 3 and 4.  Expectation is to move just sheet 3 to file A, so in the end file A has sheets 1 2 and 3,  and file B has only sheet 4 left.    This step in excel would be to move sheet 3 to file A without creating a copy. 

Can anyone kindly let me know what tools should be used to perform this step ?  Thanks a lot. 

[DavidP]

Alteryx can read data from specific sheets in Excel files via the Input data tool and write data to existing sheets or create new sheets via the Output Data tool, but it doesn't have functionality to delete sheets from a file.

You could therefore read sheet 3 from file A specifying the full path and sheetname, such as "C:\Users\davidp\Downloads\file B.xlsx|||Sheet3$" in the Input Data tool, and write it to file A with the path "C:\Users\davidp\Downloads\file A.xlsx|||Sheet3$"

[Stoneage8988]

Thanks for your reply David. 

If I understand correctly,  below you meant to split 2 sheets in 1 file right?  Is there a way to to  append sheet 3 from file B together with sheet 1 and 2 in file A?   like can be seen below, output file in the end has sheet 1 2 3,  and the other file has sheet 4 only. 

 

[DavidP · Accepted solution]

Look at below:  Sheet 3 is loaded from file B and written to file A as a new sheet in addition to Sheet 1 and 2 that already exist.

In the output options you can choose to:

create a new sheet, but if the sheet already exists you'll get an error  OR

overwrite a sheet so all existing info in that sheet will be lost OR

append data to existing sheet

[Stoneage8988]

Hi David

thanks for you reply from last time. 

Basically what i am doing now is i have a file A, it has two  sheets 1, 2 .  File B has 1 sheet called 3,  now i m using input file to read file B and trying to write sheet 3 over to file A by selecting create a new sheet in output option,  it would work  and file A would have sheets 1 2 and 3.   But it is still called file A because it is just overwritten the original file A.  Do you know if there is a way to rename this output?  Basically i still want those 3 sheets in one file, but i would like to use alteryx to give it another file name , for example call it  file C.   Now i need to name it manually after alteryx side is done, Not sure if this is something possible?  I tried to google around but couldn't find the answer.  Thank you very much. 

[DavidP · Accepted solution]

I would read all 3 tabs from file a and b with input data tools, create a filename for file c with a text input tool, add that field to each of the datastreams with the append tool, update the new filename with each individual sheet name and write the 3 tabs to the new file c. Like this:

[dsevilimedu]

I have 50 files with a sheet name "summary" in Folder A and 40 files with two sheets "employee" and "hr" in folder B.

The file names are same in both the folders and can these be combined?. meaning if folder A has file name "output - Payroll" and Folder B has file name "output -Payroll" , i would want the "summary" sheet inside the file "output - Payroll" in folder A to be moved to "output -Payroll" in folder B. At the end "Output-Payroll" in folder B will have 3 sheets -summary, hr, employee.

Move summary sheet only if the file name matches in both the folder.

The number of files are subject to change every month

Is it possible to do? Thank you