Use of Dynamic Input
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello,
I have the following problem, and since I'm new to Alteryx I was hoping to get some advice.
- In a directory, we have some comma separated files (extensions .dat and .csv).
- Files have different layouts, so not all files have same number of columns / column names.
- Some files are Unix type files (record delimiter = CR), some files are DOS type (record delimiter = CR / LF).
We also have an Excel file that contains some basic metadata on the files:
Filename | Header |
File_A.dat | col1,col2,col3 |
File_B.csv | colA,colB,colC,colD |
The idea is to follow these steps:
- Use Input to read filename + expected header from Excel
- Feed filename to Dynamic Input and read only 1 row (containing the header data)
- Compare the actual header record with the expected header
However, the Dynamic input requires an Input Data Source Template. How do i configure this to cope with both Unix and DOS type files?
Basically, from each file, we only want the first record. Record length is variable.
Any guidance, thoughts, or examples would be greatly appreciated.
Hans
Solved! Go to Solution.
- Labels:
- Dynamic Processing
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
It might be easier to use a macro like the one here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-Batch-Macro-AKA-the-Extra-Dynami...
By using a macro like this, you don't need a template. It leverages a control parameter to pull a list of files and stacks based on common headers. Given that you want to read in the header data, you will need to set the input data tool inside of the macro so the box is checked for "First Row Contains Data". Then you can use a sample tool where you pick the first row and check the box to group by file path. This will give you the header rows for each file that has been read in.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi Brandon,
Thanks for your input. I installed the macro and made the proposed modification ( "First Row Contains Data").
The output of the Input Batch is indeed showing the header for each selected file. However, it's in this format:
Filename | Field_1 | Field_2 | Field_3 | Field_4 | Field_n |
File_A.csv | col1 | col2 | col3 | null | null |
File_B.csv | col1 | col2 | col3 | col4 | null |
File_C.csv | col1 | col2 | null | null | null |
I'm looking for something like this:
Filename | header |
File_A.csv | col1,col2,col3 |
File_B.csv | col1,col2,col3,col4 |
File_C.csv | col1,col2 |
Is there a way to concatenate a (variable) number of columns into 1 comma delimited string?
Hans
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @Hans020
In your batch macro, update the Input tool's configuration with the delimiter set to "\0" which signifies no delimter. You'll also want to update the field length to something larger than the default 254. I usually just add some 0's, but adjust to whatever works for your use case.
Hope this helps!
Jimmy
Teknion Data Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
While experimenting, I discovered basically what you are suggesting Brandon: see screenshot.
I will look into the other suggestion, might be bit easier.
Thanks,
Hans
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
