Load multiple CSV files into single table

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.

Input

Macros

Transformation

Preparation

Accepted answers

jay_chang

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

All comments

JoshKushner

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:

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-different-Field/ta-p/13370

Directory Being Read:

jay_chang

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703