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

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.

Form 2.xlsx

Form 1.xlsx

Form 3.xlsx

Data Format.xlsx

Input

Output

Preparation

Accepted answers

Matt_D

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.

ExtractDataStructuredOutput.yxzp

All comments

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!

ExtractDataStructuredOutput.yxzp

Matt_D

ExtractDataStructuredOutput.yxzp

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

Data Output Alteryx.yxzp

SD9126

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

SD9126

worked perfectly. thank you

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335