Challenge #118: Think Like a CSE - A not-so-wild-wildcard input!

[kat]

Spoiler

Multiple solutions here, I suppose, including just asking them to edit the sheet name.

I went with using the crew macros.

[jackie_liu]

My solution files are attached. This was definitely a challenge for me!

Spoiler

In order to import and union data from multiple .xlsx files with different sheet names and field names, the co-worker should do the following:

1. Create a batch macro that reads in a template file. The Output Mode in Interface Designer properties needs to be configured to "Auto Configure by Name" to avoid the different schema error.

2. Create a standard workflow that reads in the list of the .xlsx filenames.

3. Insert the batch macro from Step 1 into the standard workflow and add an output tool.

The result of the output tool is the data from all the .xlsx files unioned together.

Batch macro


Workflow

[estherb47]

So this was an exercise I did with my colleagues about a year ago. Fun challenge! Instead of using the macro I already had, tried a new approach with a filter before the dynamic input, as many others (and this community) suggested. Pretty cool!

[LordNeilLord]

I kept my nice and simple

A lot of other solutions were over complicating their macros (imho) it doesn't need to be like that

Spoiler

[dominiklz]

Spoiler

this was an interesting one, I first tried solving it from scratch using the directory input and some standard alteryx logic, but kept thinking that wasn't very elegant, in that pursuit I discovered that the (generally amazing) CReW macro pack already has this solved quite nicely. feels a little bit like aiming a cannon at a bird to use it for just 4 excel sheets, but will certainly start using it in organizations that overuse separate unorganized spreadsheets

Today I learned a thing. That was nice :-) 

[KOBoyle]

Fixes attached. I guess I viewed this as a troubleshooting problem as opposed to an automation problem. In looking at a couple of the macro solutions, neither fixed the formatting issue in File3. The metadata in these solutions showed that Column23 was incorrectly set to string instead of numeric. They also appeared to assume each Excel file would have a single sheet and that the columns present in each file were positionally correct. I think fixing the data sources is the better approach. It was good to see that was the solution in the video. It was interesting to see the use of the Field Info tool and its value in the troubleshooting. I resorted to examining the files directly.

[DawnR]

Good use of some new tools I hadn't used before.

[cplewis90]

This was definitely a tough one. I had to do some research on the different file names. Great learning on that. Will be very useful in the future!

Spoiler

[vkarthik21]

It was definitely a challenge for me. It was straightforward coming to think of it but kept getting into "schema error" for the fourth file but finally was  able to crack it just by reading into the finer details.

Here is my solution -

Spoiler

Anyway my approach is simple. Have a directory tool spitting out all the required input files. Connect it to a batch macro. 

At the macro level -
1. Drag an 'Input Tool' and connect to one of the excel file inputs with option for 'List Sheet Names'
2. Have an Dynamic Input Tool that takes the input form the 'Input Tool' above. Here have the tool configured with option 'Modify SQL Query' and set it for 'Replace a specific string' option. 
3. Have a 'Control Parameter' connecting to steps (1) and (2).
4. Connect this back to the 'Macro Output'. You are done. It is at this point you need to go to 'View' -> Interface Designer -> Tools and set the option - 'Auto configure by Name' as shown in image 3 

 

Special thanks to @mceleavey for his valuable input regarding 'Interface Designer' within the macro page, never knew such option existed.

[jasperlch]

Since the schema of the files given are different, we need to use a batch macro tool to complete this task.