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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Load multiple CSV files into single table

jay_chang
8 - Asteroid

I am an Alteryx noob.  I am trying to solve for a very specific situation.

 

I have approximately 150 input files that I need to join together into a single table.  each file consists of 7 rows of data in CSV format.  The layout of each file is identical and looks something like this:

 

Row 1 = Header row

Row 2 = Data row

Row 3 = Null row (always present)

Row 4 = Header row 2 (completely different set of headers vs. the headers in Row 1)

Row 5 = Data row associated with headers from Row 5

Row 6 = Null row (always present)

 

For each file I need to import Rows 1 and 2, import rows 4 and 5, then append the fields from Rows 4/5 to the right of the fields in Rows 1/2.  At the end of my workflow for a given file, I should have 1 data row and one header.  At the end of the overall process, I should have 150 data rows and one header.

 

I have successfully create the flow to process one file and it works beautifully.  However, I am struggling with how to generalize this into a macro so that I can feed multiple files into the process. How would I go about making a macro out of my single file process so that I can process multiple input files?  Ideally, I'd like to feed a wildcard input into the macro to allow me to process as many files as are needed.

4 REPLIES 4
JoshKushner
12 - Quasar

You can do this with just the directory tool feeding into a dynamic input tool. All of the files will unite into a single table. Then all you have to do is run it through the macro you currently have built.

Flow:

Flow.PNG

 

Directory Tool:

This is reading from the directory referenced below so the file paths should match

Directory Tool.PNG

 

Dynamic Input:

The Dynamic Input will change the full file path given by the directory tool for each file. As long as the schema matches your template (VERY important) all files will unite into one table.

 

CAUTION: If a file doesn't have the same schema it will error silently and not unite with the other files. This can result in incomplete data. It may be prudent to look into handling different schemas. It's more complicated, but more dynamic.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-multiple-files-with-...

Dynamic Input.PNG

 

Directory Being Read:

Directory.PNG

jay_chang
8 - Asteroid

Thanks Josh - the issue wasn't the joining multiple files into a single table - the issue was the processing of the files using the macro I'd set up.  I ended up using a simple input tool with a wildcard, then doing some filtering and re-joining back to itself to get to my end result.  The final version works but it's highly fragile in the sense that there is no way for me to verify that my records are logically consistent, and I'm purely relying on (a) the files never changing structure and (b) the manipulations done by Alteryx never alter row order (short of me actually sorting it).  Under those two assumptions, my flow works.  But is a bit nerve-wracking!  :-)

 

Thank you for the suggestion.

JoshKushner
12 - Quasar
Would you be able to send a sample file and your desired results? I could take a crack at it. 😊
jay_chang
8 - Asteroid

Thank you for the offer - I was able to figure it out.

Labels