Input data .xlsx files with different workbook and sheet names?

[horth007]

Hello All

I tried to consider as input data a folder including all *xlsx files each files contains one sheet

The columns is always equal but the file name is different and also the sheet name is always different? e.g.

File   ABC 4564.xlsx /// d34

         ABC 3476d.xlsx.  /// a32 

        RGT 456.xlsx.  /// Xyz  etc....

The content and columns are always the same....

any idea how this works? I tried to use the dynamic Input toll, but this requires a "fix" file name ?

Thanks for your thoughts!

[ShankerV]

Hi @horth007 

Please use the directory tool, then use the dynamic input tool.

Directory tool helps to read all the file name and then dynamic input tool helps to read all the files.

Many thanks

Shanker V

[ShankerV]

Hi @horth007 

The below setting in Dynamic input tool will help to source the data from different excel workbooks with different sheet names.

Many thanks

Shanker V

[mceleavey]

Hi @horth007 ,

this is a very commonly asked question on this forum.

I have written two tools to do carry out this task which will negate the error you will undoubtedly encounter when using the Dynamic Input tool method mentioned above.

You can download the tools at the following links:

https://community.alteryx.com/t5/Community-Gallery/Import-Multiple-Schema-Excel-Files/ta-p/919226

https://community.alteryx.com/t5/Community-Gallery/Dynamic-Multi-File-Sheet-Names/ta-p/919218

You can use these tools in the following configuration:

The first tool loads in all sheet names, making this dynamic, then appends these to the full path of each file. You can use filters to narrow down the files and sheets you want.

The second tool loads in the sheets and gets around the problem with multiple schemas.

I have attached the example workflow.

I hope this helps,

M.

[horth007]

@ShankerV  Thanks for this but this solution was not working. I have always sheet with a different file name and changing number of files. This would require I have a fix "Input Data Source Template 

@mceleavey Thanks I will verify the links you sent! The workflow you shared can be not used as it seems the function included not matching my Alteryx version and I cannot update this.

In any case many thanks for your help!

[ShankerV]

@horth007 

As per the question, I understood that the file name is different and the sheet name is different.

sheet name is always different? e.g.

File   ABC 4564.xlsx /// d34

         ABC 3476d.xlsx.  /// a32 

        RGT 456.xlsx.  /// Xyz  etc....

Was not aware that, one file has many sheets. I created and shared the workflow to read only 1 sheet from the Workbook.

If you need the support to read more than 1 sheets, let me know. Happy to help on that!!!!!

Many thanks

Shanker V

[horth007]

@ShankerV 

This would be a monthly recurrent task,  I could have up to ten files *.xlsx and each file contains only one sheet one sheet.

But the file name will change each month and also the sheet name is changing. I only know that the columns and headers are always equal.

The solution should be also ensure that the workflow can run by "normal" without any adjustments in the workflow.