Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Weekly Challenges

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

Want to get involved? We're always looking for ideas and content for Weekly Challenges.

SUBMIT YOUR IDEA

Challenge #180: Thousand File Challenge

AYXAcademy
Alteryx
Alteryx

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
17 - Castor
17 - Castor
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

Annotation 2019-09-09 124603.jpg
David-Carnes
12 - Quasar

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

180_file_reader.png

...then the workflow.

180.png

JORGE4900
8 - Asteroid

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
2019-09-09_10-34-00.jpg
LordNeilLord
15 - Aurora

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

 

Spoiler
Capture2.PNGCapture.PNG
kat
12 - Quasar
Spoiler
Challenge #180 macro.PNGChallenge #180.PNG
JosephSerpis
17 - Castor
17 - Castor

Challenge Completed

RolandSchubert
16 - Nebula
16 - Nebula
Spoiler
Created a macro first:

10-09-_2019_07-59-11.png


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.
10-09-_2019_08-00-25.png
Result

10-09-_2019_08-00-47.png


PhilipMannering
16 - Nebula
16 - Nebula

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
MacroMacroWorkflowWorkflow

 

 

ggruccio
ACE Emeritus
ACE Emeritus

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.