Load all the sheets from an Excel File.

Question

Hi all, I am trying to figure out a condition where I am facing an issue, I have a excel file which has several sheets with similar data similar columns in all the sheets, just the name of the sheets is different. Ex - Sheet 1 - Report 1, Sheet 2 - Report 2, Sheet 3 - Report 3, Sheet 4 - Report 4.

I have already used dynamic input for this condition and It was working fine till now but when I received a file from my database the data type of Report 4 was different from Report  1 which was my standard template in dynamic input file and then the workflow didn't load the Report 4 data because of error and only consisted the first 3 sheet data, how can I perform a function where when I load my file in the input tool all the sheets will have the data consolidated just because all the column headers are same and all the sheets have similar data. Also to note that every week I have to load a new file which can have 1 sheet or multiple sheets as mentioned above. I want to consolidate all the data. Thanks Kindly help

KGT · Answer

Same as @binu_acs, I would go with a batch macro.

I mention this as it's handy at times to just get past an issue, in the template input, you can select "data starts on first row" or whatever that option is for your filetype and then the headers will be in the data and everything will be a string (providing you don't have pure number headers). Beware that you will be essentially unioning on position rather than headers here though. You can then cleanse it afterwards.

Batch Macro is a much better solution, this is a "hack" that's handy to know.

binu_acs · Answer

@Kaish Try batch macro

https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309