Need to input multiple files with unstructured and inconsistent data

I have multiple files that will all be located in the same directory with similar naming conventions. They are all formatted in similar ways but are unstructured when they come into Alteryx. I need help with being able to quickly combine the data from all the files into a structured output I can use for another workflow I previously built. Not all the fields needed For the regular hours in the example, I just need to show dates where regular hours were not blank. I have attached three example files and the desired output.

Any assistance is appreciated.

Thank you.

John Doe sick call.xls

Jane Doe Sick Call.xls

Divya Sick Call 2.xls

Time Sheet Output.xlsx

Workflow

Input

Output

Preparation

Accepted answers

gabrielvilella

Hey @Dsmithhisler, you will need a macro to read those XLS files as they have different schemas. Then you just need to parse the data out. Here is a solution:

Sick Calls.yxzp

All comments

gabrielvilella

Hey @Dsmithhisler, you will need a macro to read those XLS files as they have different schemas. Then you just need to parse the data out. Here is a solution:

Sick Calls.yxzp

Dsmithhisler

@gabrielvilella Thank you very much. This resolved my issue. I am not very good at macros at all.

gabrielvilella

Happy to help @Dsmithhisler 🙂

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167