Challenge #180: Thousand File Challenge

A solution to last week's challenge can be found here.

In celebration of the Data Connections window and many other cool new features in the 2019.3 release, this challenge requires you to read in thousands of files across 15 different file formats: yxdb, sz, avro, csv, shp, gml, sav, json, mif, tab, xlsx, xlsm, qvx, sas7bdat, geo. Each file contains a single value. Report back the sum of all values.

Hint:
suffix FileFormat
yxdb 19
sz 16
avro 48
csv 0
shp 12
gml 31
sav 46
json 54
mif 10
tab 14
xlsx 25
xlsm 56
qvx 47
sas7bdat 45
geo 15

challenge_180_start_file.yxmd

data.zip

challenge_180_solution.yxzp

Macros

Join

Preparation

Advanced

Transform

Interface

Difficult

Accepted answers

All comments

patrick_digan

Spoiler

I unzipped the file manually. Then I used a batch macro and edited the xml so that the fileformat = -1 and thus it will read all the files with default settings. Props to @jarrod I believe who showed me that a few years back. It produces an error when trying to read some of the helper files, but it doesn't affect the answer.

Annotation 2019-09-09 124223.jpg

challenge_180_start_file.yxzp

David-Carnes

This was fun lunchtime spent.

Spoiler

Tip: Creating a Text Input with the numeric FileFormats then joining on the file types from the directory will eliminate all the helper files that some formats require.

First, the macro...

...then the workflow.

David-Carnes_180.yxzp

JORGE4900

I used the directory tool to bring in the 15 different formats and used the dynamic input to load the specific template for those formats. 1000 files were added but did not match the results. I also tried filtering the records counts and no match.

Spoiler

challenge_180_Jorge_Solution.yxmd

LordNeilLord

A nice quick and i got to use my favourite formula, FileGetExt()

Spoiler

challenge_180_LNL.yxzp

kat

Spoiler

Challenge #180.yxzp

JosephSerpis

Challenge Completed

challenge_180_Joe_Serpis.yxzp

RolandSchubert

Spoiler

Created a macro first:

I copied the list of extension and file type numbers to a Text Input tool, created a list of files using Directory tool and used Text to Columns tool to separate the file extensions. The macro return 3 fields to include in the sum of values, so I used the formula tool to convert/merge them.

Result

challenge_180_solution_rsc.yxzp

philipmannering

You are always so disappointing Dynamic Input Tool.

Here's a thought, replace the (not so) dynamic input tool with the same simple batch macro everybody has to create in order to input multiple files. Solution attached...

Spoiler

Macro

Workflow

Challenge 180 - Thousand File Challenge.yxzp

ggruccio

This one was lots of fun! I don't have the newest version of Alteryx but still able to complete in reasonable fashion with older tools.

challenge_180_gg_finish.yxzp

Adam_Dooley

My solution might be too simple, no macros, but it works and was pretty easy. The caching feature helped a lot here.

Spoiler

challenge_180_AD.yxmd

andresleonm

Spoiler

My solution 🙂

challenge_180_start_file.yxzp

JORGE4900

Hey there @Adam_Dooley

Your solution helped me identify the error in my solution. I was using a directory tool followed by the dynamic input, but when it pulls the .sz extension--(Alteryx Spatial Zip File), it populates the same incorrect value for the num field for all records.

Spoiler

I was surprised that the tools pull different values. Does anyone know why? Wait a minute; I found out why; I changed the configuration of the dynamic input to read and change Entire File Path, and it pulled the correct values as seen below.

Spoiler

challenge_180_Jorge_Solution.yxmd

Ritu_Gowlikar

Spoiler

SPOILER

STEPS:

Unzipped all the files manually and stored them in a folder.
I created a text input file with all the file formats.

Created a macro to read all the files from the directory tool and file formats from the text input.

FILE WORKFLOW:

Challenge_108.yxmd

FileReader.yxmc

pasccout

Here are 2 potential solutions (see spoiler for details)

Spoiler

I almost add it done without extracting the files in advance from the ZIP but there are some file types that cannot be opened from a ZIP in Alteryx

So I finally add to extract all data and then loop (at least my extraction macro was already done

).

One other issue I had to learn the hard way... if you force a macro to return all data even if the outputs are different... you need to put a browse after... if not... only displays the first row (was starting to drive me nuts).

Overall, lots of fun using tools I don't use everyday!!!

challenge_180_my_solution.yxzp

challenge_180_solution_from_zip_file.yxzp

TonyA

Had a few fits and starts with this one. It would help if I read the instructions more closely...

Spoiler

challenge_180_TonyA.yxzp

Sophia

I can't download files in any of weekly challenges. Any idea why? Thank you, Sophie

T_Willins

Like several other people I spent time trying to come up with a solution to read from the .zip file and got the same error on files that cannot be read directly from a .zip file.

Spoiler

challenge_180_solution.yxzp

Reesetrain2

Wow,

This one took me awhile, as I could figure out why my total was way off until I peeked at other solutions to see that the FID files were throwing things off.

-Cheers!

Matt

Spoiler

Challenge_180_Reesetrain_Submission_MACRO.yxzp

Challenge_180_Reesetrain_Submission_workflow.yxzp

RWvanLeeuwen

Another great challenge and another learning moment 😄

My results:

Spoiler

Sum_num Count Distinct Non Null_FileName
2.94538714461834 1000

My workflow:

Spoiler

My macro:

Spoiler

challenge_180_start_file - RWvL.yxzp

DanielMS

Dan_Salway_challenge_180.yxzp

Garrett

I think I missed why I would choose to use two Actions in my macro (one to update File Path and another to Update File Type).

Isn't that what the "Update Input Data Tool" action type is supposed to do for me? Seemed to work...

Spoiler

challenge_180_glodewyck.yxzp

NicholasM

Didn't fully read the challenge prompt,

missed the hint,

rage quit,

finally wised up and read the prompt on my second attempt,

now we're here...

Attached is my solution.

challenge_180_NM.yxzp

smoskowitz

Not elegant, but certainly fun. I learned a little about brining in files as well. Is there a list of all these codes somewhere?

challenge_180_Completed.yxmd

kelly_gilbert

At first, I didn't think this one would be too bad, but it took a few tries to get the correct fields returned!

@LordNeilLord, I had no idea that the FileGetExt function existed!

Spoiler

First, I unzipped the files using a RunCommand tool.

File input macro:

Outer workflow:

Result:

challenge_180_solution_klg.yxzp

LordNeilLord

@kelly_gilbert

There's also FileGetFileName() & FileGetDir()!

danilang

Fun Challenge

Spoiler

Main

File Reader Macro

Macro

Macro

Dan

challenge_180_solution_DL.yxzp

Niklas

Spoiler

Keeping an eye on the XML really helped and saved me from having to use the data in the hint, although yxdb = 19 did probably help me realise what I was looking at.

Main worflow.PNG

challenge_180_NE.yxzp

cadyb

That was a fun one! This is awesome practice for those getting started with Batch Macros!!

challenge_180_solution_CBrooks.yxzp

JordyMicheal

Really interesting. Might have to demo this one day

Spoiler

challenge_180_start_file.yxzp

garthn555

So now I know to use the Interface Designer to allow different macro output formats. Had me stumped for a while - I resorted to using LNL's solution but it still didn't work although the macro looked identical. Should this option really be in the Interface Designer properties?