Error: Dynamic Input - The file "xxx" has a different schema than the 1st file

[DaveJ]

Hello,

I am trying to load 2 excel spreadsheets using dynamic input. Each has 2 worksheets. My workflow splits into 2 and loads the same worksheet page from each of the files in one branch and the other worksheet page name from each file in the other branch.

Branch 1 works and successfully opens up the contents of worksheet "sheet1" from both files. However branch 2 complains that one of the 2 files has a different schema and only loads one of the worksheets. 

1) I have scrutinised both files to look for differences and couldn't see any. Nonetheless I checked the column types for format differences, data shape differences, column header name differences etc but there was no difference.

2) I switched the worksheet pages around so that each spreadsheet now contained the others "sheet2". Same error on the same file.

3) I recreated both files from scratch and copy\pasted values from each into brand new spreadsheet files. No difference.

4) I started to think that what is was telling me was that both files are ok but their data is different so the load order wasn't a critical factor but the 2nd file was different than the first therefore the error would always come on the file loaded 2nd. As a test I deleted the worksheet page from the 2nd file and copied the worksheet from the first file so that they would literally try to load the exact same data. Now there couldn't be a different schema because it's the exact same file. However I did indeed hit the same problem. 

So I have zero confidence in the reliability of the error message as stated. It can't be a schema difference!!  What is going on? Can anyone help please? This is getting really frustrating.

Regards

David.

[DataBlender]

Hi David

I can't tell you what's causing the two files to be seen as different schemas, but I can tell you how to fix it.

If you take a look at the third section of this link, it will explain about creating a batch macro input (with an attachment you can use) for reading in files of different schemas.

[DaveJ]

Thanks for the reply. The batch macro looks like a more robust, fault tolerant approach so I will switch to that. Many thanks.

If anyone else out there has any idea why I was getting the error even when using the same file twice to ensure they were identical please do let me know as it is a mystery.

Cheers

Dave

[bsharbo]

Can you post a copy of your code and some examples of the files? YOU can make up the data in the files frankly as long as you still get the error.

I do think that a batch macro is always a better option if you think your file metadata WILL change, as a dynamic input will fail as you noted.

However in your instance I would like to see why it is failing.  Maybe there is a small bug in your dynamic input, or maybe there is something different in the files that you are missing, or maybe it is a real bug (I would love to see that!)

Let me know if this is something you can provide and i'll try to do some digging.

[DaveJ · Accepted solution]

I've finally got to the bottom of this issue. I switched to a batch macro but it still wouldn't union the data from the two files. So I went back to basics. I created a test workflow with 2 input tools (one for each file) and then a select tool for each input tool. I wanted to see whether the select tools would report a different number of columns or different data types for what should be the same data types.

I spotted that one field was being identified as a "V_String" in one file and a "V_Wstring" in the other file. I wanted to prove whether this was the root cause of my problem. It was!! The next challenge was finding the "bad data" that was causing this effect. By searching for non standard ascii codes I found that some rows in one of the files had a hyphen character that wasn't actually a hyphen. Once I replaced this character both files treated the field as a "V_String" and the import of the union of the files worked correctly.

So it turns out it was a data issue after all and not a problem with the Alteryx tools. I think it was exacerbated by the fact that Excel seems to retain data type formats for columns even if you switch the data. Weird but seemingly true. 

Thanks to the two people who responded to this post. Hopefully my comments will provide ideas for anyone else having a similar problem.

Cheers

David

[alexbao]

hi there, I got same issue with you. But I got 100 files to combine so how could I have a solution to configure this one time? thanks!

[DaveJ]

I'm not sure what advice to offer you. Are you certain that the column structure is identical and that it's only multibyte characters creeping into one or more fields in some of the files?

My solution was manageable because it was just 2 files. It has happened a few times with customer supplied data so I now search the files looking for rows with an ASCII number that is usually higher than 200. I filter to just these rows and replace the "troublesome" character(s) and then re-try my Alteryx workflow.  When I say I search the files looking for high ASCII values this is done outside of Alteryx. 

Someone more knowledgeable may be able to offer an Alteryx based approach. If they do I'm also interested in the answer because I find it time consuming and manual to resolve my issues. 

You could add a filter to direct the problematic files into a different path and open the two sets separately. However, if the "problems" are variable, i.e. different fields in different files are introducing different issues then this wouldn't work.  To identify the "problematic" files I would keep re-running the workflow and filtering each problem file into a different path based on whatever criteria you can.

Hope these ideas help. I'm no expert. I'm still learning Alteryx myself.

[alexbao]

Thank you anyway. I found one solution using Macro, I will share the link that has the material here and hope it could also help you.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-T...

[Pasty]

Thank you so much Dave, I was frustrated by the same problem all day long and your answer saved my life!  Thanks haha 

[eyalgrn]

Another way to solve it is by (in excel) selecting all the data in the top left corner (even a few sheets together as group), then Home->Number-> data type as "Text", and all done!