Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

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

SD9126
7 - Meteor

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.

6 REPLIES 6
CarliE
Alteryx
Alteryx

@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!

Carli
matt_dix
9 - Comet

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.

 

Capture.PNG

allwynthomas24
11 - Bolide

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
Macroallwynthomas24_0-1654865522989.png


Workflow 

allwynthomas24_1-1654865564038.png

 

SD9126
7 - Meteor

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

SD9126
7 - Meteor

worked perfectly. thank you

allwynthomas24
11 - Bolide

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.

Labels