Challenge #180: Thousand File Challenge

[AYXAcademy]

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

[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.

[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.

[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

[LordNeilLord]

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

Spoiler

[kat]

Spoiler

[JosephSerpis]

Challenge Completed

[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

[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

MacroWorkflow

[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.