Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

has a different schema than the 1st file in the set and will be skipped...... WAIT WHAT?

Highlighted
Bolide

Hi @MK 

 

I've had this happen and it could be as something as small as the field type is slightly different. What I mean column C in one file is a V_string and column c in the next file is a V_Wstring. Now, that being said their are a few ways to bring in data into Alteryx. Some make assumptions that the schema will be the same and some allow you to ignore the schema.

 

More specifically, a batch macro will allow you to import by field name or even field position. I use this way all the time and it works well.Alteryx is a great tool once you get some of the idiosyncrasies down.

 

Thanks,

Seth

Highlighted
Atom

Hi  

thanks for your help. 

Cannot share original data as they are confidential. But the appended Files Book1 & Book2 show the simple concept. I also attached "TargetFile" which is the result I am trying to achieve. 

Thanks again

Alteryx Certified Partner

Here you go: https://concentra.box.com/s/3ixdzlma95t75990n0p6q1h85qhuedei

 

You can point the Directory tool to your data folder, and change the sheet name if necessary. I've set all dependency as relative so the batch macro should automatically appear if you download the whole folder. The result should have all your data from the sheet "Client Data" from all the xlsx files in the folder. It also has 2 additional columns indicating which file and sheet the records are from. You can easily deselect these if you don't need them.

 

The macro is actually a generalised batch macro so you can use it in another scenarios as well. Let me know if you have further questions.

 

Bingqian

Highlighted
Atom

I ran into this same issue but with a dynamic input query and what I learned was that if the column for one record was returned as a string numeral Alteryx would make the output column numeral even though the schema specifies it is a character column. When the next row was executed and the result for the column was a character value, I'd get this error. Casting the column to character solved the issue. 

Highlighted
Meteor

 

Not a solution, but a potential trouble-shooting tip for anyone with the same issue:

 

I have had the same problem today.  Multiple sheets in one xlsx file, all are populated by the same process (extract from central DB).

 

I confirmed schema was identical.

 

The actual problem (for me) was that for one of the sheets I had a single field with all nulls.  In the schema (and all other sheets) this was a date field. While other sheets had some null dates, this was no problem.  But for a sheet with 100% null dates Alteryx seems to infer a different field type, and hence reports a schema break.

 

I would describe this as a bug.  If we have a field that is full of nulls then Alteryx should not try to infer the field type as different from the prescribed schema.  In other words, it should not be treated in the same was as if the schema was (e.g.) Double and then it encounters a field full of text - that is a true break. nulls should not overide the schema field type.

Highlighted

@Cal_A,

 

I've been searching for about an hour for an explanation for why my schema was breaking.  This is the first description that makes sense for my particular case.  Thank you.

 

Of course, I'm still left with the problem of how to get around the issue.  In some cases, my data *should* be all nulls in one column of one file.  I think you're right about this being best described as a bug.

 

I wonder if there is is a workaround for this that doesn't involve some custom macro gobbledegook...

Labels