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
Meteoroid

So, 

In workflow 1, from single data source, I do 17 splits into smaller files.

In workflow 2, I need to run specific analyses per each "cut".

Workflow 2 fails to read all 17 files, just 2.

Files are xlsx.

They come from single and same data source.

All of them are created the same way by the same workflow.

All sheets are named Sheet1.

 

Why this great business tool cannot read all the files that obviously has the similar content and same schema?

 

Thanks for answers,

Just please dont reply with "you need to build 5 custom macros...", I will be better off with pure sql at the moment.

Highlighted
Meteoroid

btw,

did a test run on single row extracts,

similar outcome

Highlighted
Alteryx Partner

Hi MK,

 

I've run into this problem a lot when dynamically reading in .xlsx's. Oftentimes I found that one of my suppliers would have opened an excel, filled in a column that wasn't used and deleted the contents of that column later on. On other occassions some excels would have a messed up date somewhere (e.g. DD.MM.YYYY instead of DD-MM-YYYY), also causing read-in issues in the type of field. Somehow, this messes up the dynamic read-in for me.

 

From your description it seems you might run into something similar. You say you split up your data source in 17 smaller files in workflow 1. Do you do this with 17 different output data tools or just 1 with an additional "Take File/Table name from field?" If you do the first, I would recommend trying the latter.

 

Secondly, have you tried writing to .csv's and reading those in dynamically in your second workflow? This usually solved the problem for me.

 

Let me know if this helps, or if I can do anything else to help you.

 

Regards,

Wesley

 

 

Highlighted
Meteoroid

Hi Wesley,

 

The workflow 1 uses the same data tools for all files.

Lets say, the cutting column is market. So you end up with 17 files were contents of last column is different, while the name, order, type of all columns is same.

No one opens/edits the files. After running workflow 1, I immediately go workflow 2 for correlations etc. (main reason why no group by's)

 

I also did try with csv files, but it is also not fetching all the files.

 

I think I have to read alteryx xmls in order to understand what developers had in mind when they tried to code it, and why alteryx cant handle it.

 

 

Highlighted
Alteryx Partner

Is it possible to share 2 or 3 of those files?

 

Highlighted
Meteoroid

Unfortunately not, confidentiality and security policies. 

Highlighted
Alteryx Certified Partner

If files have to be excel, check out the batch macro import: https://www.concentra.co.uk/blog/how-import-multiple-excel-sheets-and-files-different-schemas-altery...

 

Otherwise, yxdb outputs should be okay? 

 

Cell values will change the schema sometimes. Single source doesn't guarantee same schema when import from excel to alteryx, which sometimes can be quite annoying.. 

Highlighted
Meteoroid
Thanks for answer,
however schema is the same, not different.
Same datatypes with same column names even in the same order.
Quite irritating to write timeconsuming batch macros dynamic renames etc
when alteryx suppose to be easy and fast.



Virus-free.
www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
Highlighted
Alteryx Certified Partner

I agree. 

As a workaround though the batch macros could be simple as it might be re-used so you only need to build once (or just download the one I built from the bottom of the blog).

I say might though as it will depend on how many sheets your workbooks have etc..

But yes it would indeed be easier if Alteryx has a default tool or configuration for that.

Labels