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.

[PeterAP]

@jenv20032000x Could you share a screenshot of the format your date is in?

[jenv20032000x]

@PeterAP  it is custom [$-en-US,1]m/d/yyyy.  This wasn't a problem with the other 38 files (all are formatted the same).  However, this one file has the datatype datetime instead of date (sizes are different too).  I really appreciate you trying to help me.

[jstavares21]

Ok y'all, I hope this helps.

You can try this: 

In your workbook (the one with multiple sheets, all sheets have the same column headers) - go to the active sheet and right-click "select all sheets".  Then, in the active sheet insert a row between your headers and first row of data (creating a blank row). Then, in the empty row, the one you just created, select the range of cells for all columns where you have a header, i.e. headers are in A1:F1 - select B1:F1. Go to the HOME tab of your excel ribbon, "Editing" group, and click "Find & Select" - and select  "Replace" - replace all blanks in your selected range with an "X". 

NOW >>> import to Alteryx using the dynamic input tool - and ta-da!!! it tricks Alteryx into thinking all your columns are text. Now use your handy dandy select tool to set up the field types for your data and presto. Don't forget to use the filter tool to pull out those "X's" that you added in row 2 of each sheet. 

My goodness ---- I know this was driving me insane. And I have too big of a heart to allow anyone else to suffer as I did. 

Y'all have a wonderful data day!

if you like this answer, please give me "like" haha - I guess that's a badge too. 🙂

[GTilbury]

Just adding to this answer as I have also been frustrated by using the Dynamic Input tool and my inability to resolve the 'has a different schema than the 1st file in the set' error in the past, and because this is the thread I always find first when searching for answers

My problem turned out not to be the data types, but that one of the columns contained text that was wider than a V_STRING (in all the files) and so Alteryx was setting the type on that column to a VW_WSTRING at which point the string SIZE apparently became a part of the schema check too, and is set differently according to the maximum string length in the data of each file (e.g. 450 and 498 in my first two files).

I can work around this only by putting a uniformly wider string into the underlying files (a 600 character string just below the headers) which then passes the schema check and the tool then works

I would have hoped that in this situation Alteryx would ignore the size in the schema test or else look at all of the files in the set & increase the SIZE for all the VW_STRING to be the maximum size across the set.  Certainly if I join those tables with the union tool it copes and doesn't complain about the SIZE - it just sets it to 498 as part of the union.

Maybe someone from Alteryx might look into this, as it would potentially reduce a lot of noise on the use of Dynamic Input - thanks