macros to combine sheet from multiple excel files not working

[simasarry123]

Hello ,

i created this macro to combine sheets from different excel files into one excel workbook, however it is not reading files but repeating the same file whose path is given in the macros. If i configure differently, it gives error message- record 1 does not exist , please check file path and so on for record 2, 3.....

PL help with config of macros . there is only one sheet in multiple excel files with same schema which needs to be combined in single excel book.

[dalurie]

Hello,

If the Excel files only contain one sheet each with a matching schema, a macro may not be required. I recommend trying a Directory tool (assuming all the Excel files are stored in the same file location), and then passing that metadata through to the Dynamic Input tool, which can then loop through each file, ensure it matches the structure of a template file you provide, and combine them all into one dataset. I've attached an example of how to configure this for your reference.

Thank you

David

[simasarry123]

when i configure the dynamic input tool, it gives error . I have chosen the sheet name which i want to combine from each of the files .

[dalurie]

Try adding a Formula tool in between the two tools to append the sheet name to the end of the FullPath variable, as seen in the screenshot below. Just make sure the sheet name is proceeded by 3 pipe symbols (|||) for Alteryx to recognize it as a sheet. Let me know if this works!

[simasarry123]

unfortunately, even after doing this I am getting the error that schema of file 1 does not match with file 2. I am not sure why because I remember combining similar files together a month ago.

[dalurie]

@simasarry123 This method will only work if the schemas match between the two files. You'll need to confirm that the files you're combining have the same number of columns in the same position (i.e. if an ID field is the first column in data set 1, it needs to be the first column in data set 2), and that the matching columns contain the same data types as well.

Feel free to send me a sample of the two data sets if you need any help validating the two schemas!

[simasarry123]

pl find attached. I want to combine the first sheet of both the files together starting from header row 14. the second tab may contain data but that is not to be combined.

[dalurie]

Thanks @simasarry123. I've attached a working solution based on the sample data files you provided. I used the technique covered above with the Directory Tool, Appending the Sheet name to the File Path, and then using the Dynamic Input tool to union the files together. I then added some extra steps to clean up the dataset by removing records read in from the header sections in the two files and any Null rows.

You'll just need to update the file path in the Directory tool to point to where your data files are located! Please let me know if this solution works for your business case.