Extract data from multiple excel files' specific cells and make a structured output file

[SD9126]

Hi, I have 100s of .xlsx files where data is not in tabular format. Instead data are in a form structure. There are some merged cells as well. All files' format are same. I would like to fetch those data from specific cells of excel and create a tabular structure. 'Input data' with selected range didn't work because there is no particular range of data I need to select. Instead there are 7 to 8 different cells from where I have to fetch the data. I have attached 3 sample Form data and the Data Format file for your reference. I'd appreciate any help on this. My Alteryx Version is 2020.

[CarliE]

@SD9126,

Its a bit of a wonky solution but it definitely works! 

Attached is a solution, if you have any questions please feel free to ask. I used a wild card to bring all the files in at the same time

Please mark as a solution if this works for you to help other members on the community out.

Thanks!

[Matt_D · Accepted solution]

Hi @SD9126 I'd just bring in the ranges and join them together. Assuming the files are in the same directory and file specifications do not change. If this does happen you could change the path and search for subdirectories or turn this into a batch macro.

[allwynthomas24]

Hey @SD9126,

Check the attached Alteryx Packaged File which consists of Workflow + Macros and the Final Output Excel Fill. This Macro will take all your Input Files 100s or 1000s and convert them into a Tabular Format as given your Data Format Excel File. Please Confirm whether it fulfills your current requirements. 

Note: Kindly change the Directory Path as per your Folder Location. And also ensure that all your Input Files in that Folder start with the word Form

Regards.

Spoiler

Macro

Workflow 

[SD9126]

Thanks a lot. this worked perfectly with some extra tweaks as per the actual file's requirement.

[SD9126]

worked perfectly. thank you

[allwynthomas24]

Hey @SD9126,

Welcome buddy.

Happy to know that the uploaded solutions worked out for you.

Kindly mark the above Reply as Solution whichever worked out for you or else your Query would be Open in the Discussion Forum. Thanks

Regards.