Alteryx skipping input files because they don't match the schema...but they do!

Hi. I have a workflow set up and connected to a macro. The macro makes the workflow cycle through every .xlsx file in a directory and pull out data from five different tabs in the Excel files. These tabs have different schema. Each tab's information is deposited into a separate workstream, which is then united later in the workflow.

My problem is this. Currently I have four Excel files in the directory. The first three Excel files have no data in Column X on one tab, and this is perfectly acceptable; however, the fourth file does have data in Column X. When Alteryx reads in the data files, it determines that the fourth file has a different schema than the first three, and it skips that fourth file. From what I've read in other threads, Alteryx is assigning a different data type to the fields with nulls than it is to the fields in the fourth that have actual data, which presents itself as different schema, but I can't figure out a way to verify this or correct it.

How do I get it to stop doing this? I have fairly complicated (to me) setup for reading in multiple files and pulling data from multiple tabs, and I haven't been able to figure out a way to make Alteryx handle this problem correctly.

Input

Accepted answers

SergeantJack

@Luke_C never mind. I found a workaround. I just selected "First Row has Data", and the headers forced Alteryx to look at everything as string data, and then I just eliminated those rows later in the workstream.

All comments

Felipe_Ribeir0

Hi @SergeantJack

Yes, the case probably is that for some files some column is understood as string (because there are just numbers on some rows and strings on others) and for some files this same column is understood as number, because there are just numbers on all rows.

Just use the macro that is attached on this topic instead of using the dynamic input tool, this macro will accept the different schemas https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

binu_acs

@SergeantJack in your use case you need to use batch macro

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/The-Ultimate-Input-Data-Flowchart/ta-p/20480

DataNath

Hey @SergeantJack - I recently saw this exact scenario come up (where null fields default to double data types) and offered a solution here - part of the batch macro forces said fields into the same data type in order to handle this specific situation:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Dynamic-Input-Multiple-Sheets-On-One-Excel-File-Null-Fields/td-p/1000480

SergeantJack

@DataNath I'm not sure how to implement this. I have a separate Analytic App that connects to the macro in my main workflow. So for each data stream, here's how it's set up:

Analytic App --> Control Parameter --> (here's where it splits into different streams) Action --> Input Data tool

I don't seem to have the ability to add tools between the Action and Input Data tools; if I did, I would simply use a Filter to discard any records that had null values in Column X. Any tools I add after the Input Data tool don't work, since at that point, file 4 has already been discarded for mismatched schema.

SergeantJack

@Felipe_Ribeir0 and @binu_acs This isn't quite my situation. I have multiple files with what should be identical schema, but each file has five different tabs, and the tabs have different schema from each other. I already have a batch macro set up to iterate through each data file in the directory, but each tab has to be brought in separately and processed separately downstream. So, to explain:

The macro will look through all files in a directory, and for each file, it will pull in data from Tab A and deposit it in data stream A. Then all data from each file's Tab B will be pulled in and deposited in data stream B, etc.

My problem is that, in this example, Tab D in files 1,2, and 3 have Column X that is all nulls, so Alteryx decides the data type for that column must be double. Then file 4 is processed, and it actually has string data, so Alteryx discards that file since it doesn't match the schema that was already imputed when the first three files were processed.

I need a way for Alteryx to do one of two things:

1. Skip any row from Tab D that has null values in Column X (preferred), or

2. Specify that Column X is, was, and always will be data type V_WString (which is the case in this example).

DataNath

@SergeantJack are you able to share what you've done so far, as well as the expected outcome? Screenshots or a copy of your workflow (with some mocked up data if your live data is too sensitive) would be great - makes it a lot easier to help diagnose the issue. I do think this will be based around a batch macro in order to avoid Alteryx discarding your files with null fields.

SergeantJack

@DataNath Sure thing. Here is how data is ingested:

In the first data stream, marked '1 - Data Items' above, things are going just fine. Files 1,2,and 3 all have legitimate data in them, and so Alteryx selects the correct data type and is able to bring in file 4. In the example below, F6 is the data field in question. The first three files end in ...101, ...102, and ...103, respectively, and the fourth file is named "Jack_Dummy_File". Even though the example shows nulls for the ...103 file, it had non-null data previously.

In this data stream, marked 'Survey Questions' in the pic above, things are not correct:

In that stream, F7 is the target column, and it's all nulls for the first three files. In that data stream, Alteryx decides that the data type must be double, and when it looks at Jack_Dummy_File, it sees actual data there with a V_WString format, and since it doesn't match what it already decided was a double data type, it doesn't include that file in the data import. You can see in the pic above that it stops with file ...103. Here is the error message for that particular tool:

DataNath

Thanks for that @SergeantJack - really useful! Where are you getting this error though? I'd expect your workflow to error at the highest level i.e. Alteryx throws an error from whatever you've called your batch macro, rather than the Input Data tool. Are you using a wildcard or something in the Input Data tool and getting this error when running the workflow that makes up your batch macro?

SergeantJack

@DataNath It's not actually an error, it's a warning, which means the workflow will run, but it will discard any data from my dummy file since its data doesn't match the schema of the first files, which had the null data. You can see from the last pic I posted above that it's happening on the Input Data tool. The wildcard you mentioned is specified in the Action tools that come before each Input Data tool. In the Action tool configuration window, the action specified is "Replace a specific string:" and the wildcard I have in there is *.xlsx.

DataNath

@SergeantJack in that case, I think the warning is due to using a wildcard here. The usual batch macro approach relies on all iterations of the control parameter being able to run, before applying the fix and then unioning all of the results at the point of the macro output. Whereas, using a wildcard will run the iterations and try to union them as they happen, which in this case it can't because of the schema change. I'm not too familiar with your approach but is there any way you could distinguish between your streams and feed something more concrete into your control parameter rather than a wildcard?

SergeantJack

@DataNath The wildcard is necessary. The idea here is that customers will drop files that need to be processed into a folder, and this workflow will run once per night, gather up all the data available from all files in that folder and then output all of them as a single text file. I'm using the wildcard because the customers will use their own naming conventions for the files.

I don't believe the wildcard is causing the problem. If I were to show you the results of the first data stream (marked 'Data Items'), you would see that all four files are picked up by the workflow, including my dummy file. If the wildcard were preventing this from happening, then that first work stream wouldn't run.

I may be misunderstanding what you're saying, though. But if I can't use a wildcard, then I don't know how to get Alteryx to process all the files successfully.

DataNath

Ah I get what you're saying now @SergeantJack. In that case I'm a little stumped and would be curious to see how this ends up getting solved so I'll keep an eye on it. In other scenarios I've never used a wildcard so it may just be something simple I've not come across before. Only final thing I'd say to check is if your dummy file differs in any other way to the other files in that stream, though I'd imagine you haven't overlooked that, just a simple check.

SergeantJack

@DataNath Thanks for your help. I am completely stumped by this, and it's wrecking my workflow. I found it was a problem yesterday, and it was supposed to go into production last night, so the pressure to get this fixed is high. Hopefully this will make sense to somebody else and I can get moving on the solution.

Luke_C

Hi @SergeantJack

To follow-on to what others are saying the way around the schema warning is to use a batch macro. If I understand correctly, the file names are variable but the tab names are not - is that correct? I fail to see why using a wildcard is necessary then. What I think would work is:

Directory tool to read all files, filtering the ones you want
Batch Macro 1: Read all files to get the 'list of sheet names' for each
1. The input tool within the macro should be set to output the full path as well
Update the full path of each record to append the tab names
Pass each type of file to a batch macro to read in the data, since you have the full paths you should not need the wildcard.
Make sure the macro is set to configure by name, which is what allows the schema error to be avoided

Please let me know if I misunderstood your fact pattern.

SergeantJack

Hi @Luke_C

I'm perfectly willing to accept that I don't have my workflow or macros set up to handle this well. I'll break down the file structure to make sure we're on the same page.

- File 1

-- Tab A

-- Tab B

-- Tab C

-- Tab D

-- Tab E

- File 2

-- Tab A

-- Tab B

-- Tab C

-- Tab D

-- Tab E, etc.

Tab A in any file should be named exactly the same and have the same schema as Tab A in any other file, and the same with Tab B, Tab C, etc. Within each file, all tabs are different from each other, with different schema and data types.

My workflow needs to:

- Iterate through every file in the specified directory

- Generate the FileName as a distinct column

- Separate the data and schema from all the Tabs A and send it into workstream A

- Separate the data and schema from all the Tabs B and send it into workstream B, etc.

- Process each workstream differently (I don't have a problem with the workflow from this point on)

- Join all the workstreams together based on the FileName column

- Write the results to a single text file

My problem is that in some of the tabs, the first file processed has nulls in some columns instead of data, so Alteryx sets the data type of those columns to 'double'. If any subsequent files actually have data in those columns and its data type is not double, Alteryx determines that these later files have different schema, and skips over them.

I need Alteryx to do one of two things:

1. After a tab's data has been disseminated into one of the workstreams, skip any rows/records that have nulls in a key column. This would be the ideal solution, but there doesn't seem to be any way to configure the Input Data tools to accomplish this.

2. Stop coercing the data type of null records to be doubles.

Luke_C

Thanks @SergeantJack, I think what I described would achieve that, filtering out records with nulls would need to be done after data is input, though.

SergeantJack

@Luke_C Thanks. I'm trying to figure out how to do that, and how it is different from what I already have. Currently I have my main workflow set up as a Batch Macro, with the Directory tool located as a separate .yxwz file. What you described sounds very different. I set this workflow up over a year ago, and I'm trying to figure out why I set it up this way and what I need to do to fix it.

SergeantJack

@Luke_C can you help me understand what 1. and 2. from your list above should look like? I'm having a tough time trying to figure this out.

SergeantJack

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703