Dynamic Input Macro - Handling Different Delimiters, Header Rows, File Types, Etc.

[KieranRidge]

Hi all,

I need to create a master data input macro that can automatically pull in all zip and csv files: those that are already delimited, those that need to be delimited, and files with header rows where the data doesn’t start until somewhere like row 12 (requiring a dynamic Import Line). Needs to be in a macro as it would involve pulling multiple files at once.

Let’s assume I’m doing all these files separately for now, so I don’t have to stack up these five different files into one output – I just need a macro that I could drop in to take care of each different file.

I have five different containers listed in the attached workflow, and they’re all plugged into the same macro with 5 different outputs. Output A was built for the example in Container A, B for B, and so forth. I’m also attaching a zip folder of the files I’m using so if you want to export into your own folder and test, please change the C:\temp\ path in the text inputs to wherever you have the data saved.

When I test some containers on some differently-lettered outputs, some work, some kind of work (the data is there, but still in non-delimited form, or all the data is there but the non-header rows are still in the file) and some don’t work at all. I need a master output from the macro that would be able to take on the different file types and cleanly export as a delimited and properly header-rowed file. None of the 5 Outputs work for all files well enough that I could currently finagle the final pieces on my own.

I’m listing the specific examples of the types of file I want to import and what the constraints are for all of them.

Container A – File is standard csv; Output A is able to take in standardized files.

Column B – Zip file with underlying csv. Syntax is already organized into the code you need to feed into the macro (both zip and csv text): C:\temp\Outputs\Report.zip|||Report.csv. This file automatically delimits the columns without me really having to do anything to the underlying Input tool.

Container C- Zip file with an underlying csv BUT this one necessitates that I write in a tab delimiter to the underlying Input tool as \t (Output C). I have no idea why one csv file in a zip would need to be delimited and the one in Container B does not, but I do need a code that’ll be able to treat them both. Might be safer to input it non-delimited and then use a delimiter tool in the next step for files like Container C that still require it?

Container D – I previously had a macro where I’d overwrite the XML code for the Import Line portion, saying that if the file name contains DMA Conversions then Import Line 12 because that’s whether the header is, BUT in this example, I want the macro to be able to detect that itself. Let’s assume I have other files not included that start on row 6 or something so I’d need a code that will realize the header row is not line 1 and will find it automatically, instead of me manually writing the Line Imports for certain files into the code.

Might be safest for the input portion to just import the data as below, and then I could write some classic Alteryx code to delimit it in the next tool:

I mean, if I had an input tool that would import regular csv and zip files as is that could also detect if the file needs to be delimited like in Container C and import files where files that begin with header rows on line 12 like above, I could probably automate the rest on my own. But none of my current output options pull in all the files like that.

Container E is like Container D except it’s a direct csv file instead of within a zip but right now, Output D doesn’t work for container E, and vice versa.

Ultimately, it’s a conceptually easy outcome that I’m seeking: for all files - regardless of file type and files that have awkward header rows - to be cleanly parsed into tables. But the input tool isn’t variable enough standalone that it can account for all these different file types. And while a macro can help to overrule some variability, mine still aren’t sophisticated enough to ingest and output all these files.

I’m providing the workflow export with the underlying macros, and the folder that contains all the files I want to dynamically input (please change the file path in the text inputs to whatever folder you load those files into so you can play around with the different file types firsthand).

Please let me know if anything is unclear. Looking forward to hearing from some brilliant minds,

Kieran

[KieranRidge]

Thanks, Joe – what I’m actually seeing is that for standalone (non-zip) csvs, the delimiter \0 and deselecting First Row Contains Field Names still works when I send the path through the macro from the master workflow with the Update Input Data Tool action (if file is standalone csv in Input Data tool in macro), but doesn’t work for zip files through a standalone csv macro (similarly, if I have a zip file in the master workflow and send it as Update Input Data Tool action through a macro with a zip path Input Data tool, the non-delimiting and data coming through as Field_1 will still work as well). Either way, the Update Value (Default) option is clearly the way to go if I want it to work for multiple file types through one input/output so this is one of those examples of two methods that appear to be kind of the same thing, but the specificity of my output only works via Update Value (Default) option, thus necessitating that I understand the difference of that vs. Update Input Data Tool. Thanks again for engaging – hopefully these threads can help other people with similar issues down the line.