Need more robust bulk/dynamic input

Question

I'm trying to understand if problem is a real limitation of Alteryx or if there is a feature/configuration/workaround that addresses it.

My problem is when loading many excel or csv files and unioning all in one process, files are skipped because either a) data type in the columns is not aligned or b) different number of columns. I have used 2 ways to bulk load files: 1) normal input tool, but changing filename to *.xlsx, and 2) the dynamic input tool (for example when loading and merging many tabs in a single file).  These methods work great when you have nice and clean conforming data (which is rare). The problem is that Alteryx uses the first file it reads to decide the data type of each column and total number of columns, and if any subsequent files do not match then they are not loaded. For the data type issue, my workaround has been to select "first row contains data" which forces Alteryx to read everything as a string, but then I have the additional steps of setting the field names and removing the extra column header rows. It would be great to configure this tool to read data as text, ensuring all data is loaded, but I don't think that is possible. The second problem of having additional columns is the most frustrating, because I don't have a workaround and the normal union tool already handles this issue. Why can't the bulk load or dynamic input be similarly robust as the normal union tool when unioning the data?  I haven't found a workaround to this problem yet, so I am literally looking in the warning logs for where there data is not loaded, and then manually deleting the additional columns in my input files.

Looking forward to hearing from others that have run into these problems and have better or more elegant solutions.

rbeale98 · Answer

Re-upping this since I just ran into the same problem on a new project.

To answer the above questions: @markcurry - the schema is not different, but alteryx interprets it to be different for edge cases.

Example 1: a field may be a string across all files and include alphanumeric characters, but in a small subset of files this string field is interpreted as numeric and alteryx excludes it.

Example 2: as a workaround for example 1, I have set the input to "first row contains data" in an attempt to force everything as string (field names are all strings). However Alteryx still excludes some edge cases for reasons I cannot determine. When I load an edge case into Alteryx using the same input configuration, and union it to the output of the bulk input tool, it unions just fine with no warnings or errors on the union tool.

@ChrisTX - I will look into submitting this as an idea. However I think this is a bug report not a new feature request. Is there an alternate forum for that type of issue?

Thanks for the input!

ChrisTX · Answer

If you're manually deleting extra columns, do you have a known number of columns with specific column names you want to import, and you want to ignore any other columns?

I agree about Excel and macro challenges.

1) Excel input should be easier, even when formats/layouts/schemas are different.

Consider adding a Like to an existing Idea, or creating a new Idea

For now, see The Ultimate Input Data Flowchart
Community > Designer > Designer Knowledge > The Ultimate Input Data Flowchart
https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

2) Excel output should be easier, even when formats/layouts/schemas are different.

Considering adding a Like to this Idea: https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Ideas/Enhance-options-for-Excel-output/idi-p/843260

3) Macros are not intuitive, and detailed training or examples for macros is not easy to find.  Suggestions:

Try Community > Learn > Academy > Interactive Lessons > Macros

Under Community > Learn > Academy > Videos, search for "macros" or use the Video Training Index

If the Interactive Lessons aren't enough, create an Idea and mention specific macro topics/examples that are missing from available training options

Chris

markcurry · Answer

Hi @rbeale98

Where the schema differs between files you're best to use a batch macro.  See the following Knowledge Article which should help : CS Macro Dev: Reading in Multiple Files with Different Field Schema - Batch Macro (alteryx.com)