Forum Discussion
Get Data from Multiple Excel Files with multiples Sheet and separate each sheet by table
twister8889 - I think you will need to have a Folder query for each of your sheets that you want as a table. ImkeF or edhans might know better.
You are going to have to do this 4 times. You can get all sheets in all excel files at once by clicking on the "parameters" folder once you click on combine. That will get all 4 sheets. But, your sheets are not in the same format, so any further transformations will have to be custom. Sheet 1 is the same in files 1 and 2, but Sheet 2 is not the same as sheet 1. So each pair needs its own transformation.
To get the sheet name, you need to make sure that the "Remove Other Columns1" step in the Combine operation retains the Source.Name column.
I do not understand your question though "... each sheet be the table and use the excel name as a column for each table." Each sheet as a table is easy in the combine operation. Do Sheet 1 first, then repeat the combine operation for 2, 3, and 4. But what do you mean use the Excel name as a column for each table exactly? If you mean have a column with the file name, then my direction above will do that.
If you mean something else, please clarify.