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.

[RaulFinol]

Good afternoon 1 have 4 spread sheets i tried to group them and convert to text but still getting the same errror.

If i bring in the sheets individually i am able to do a union no problem.

Not sure why i am getting this error

[sarah_burnett]

That might work for some people, but if you have dates in your Excel file it will reformat them as Excel's text string for numbers. So if you use this solution you need to be careful. 

[nicoletta]

This is how I resolved it, for those like me who still were struggling:

1- do this macro in Excel VBA: 

Sub CheckRegex()

Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Global = True
RegEx.Pattern = "^[\x20-\x7F]+$"
For Each objCell In ActiveSheet.UsedRange.Cells
If RegEx.Replace(objCell.Value, "") = objCell.Value Then
objCell.Interior.ColorIndex = 3
Else
RegExCheck = 1
End If
Next
End Sub

2- run the macro on every sheet which was "rejected" by the Alteryx dinamyc tool

3- in Excel, filter by colour red, and identify all the cells that are giving wrong results and correct them to proper ASCII

4- format all sheets to "Text", using the Home-General-> Text (in my case i also had issues with IDs perhaps sometimes read like numbers)

5- save the excel and close

6- rerun in Alteryx. Now the schema is properly recognized across every tab

I hope this helps other people to save the 3 hours i wasted today. Nicoletta

[Jhar2401]

Hi,

I appreciating the fact that you have had the same problem as me and explained it in language that I understand...none of the macro's I've found make any sense. I'm going to see if I can work out which value might be causing the issue.

[PeterAP]

@DaveJ  This was really helpful, thanks. 

I fixed the datatype issue in the source, but also had to fix the length of a cell in one of my fields too. Once I did that, the dynamic tool worked.

[jenv20032000x]

Greetings @DaveJ ,

I'm having a similar problem to your data issue.  I'm trying to use dynamic input to input multiple files.  Out of 39 excel files there is only 1 with the error "different schema than the 1st file in the set".  I compared the errant file with a file that did properly import and find one field that has a different data type and size.  I went to the errant excel file and changed the data type but when I input the file again into Alteryx, it continues to have the old data type (unsure how to change the size).  Can you help me fix the data type and size in my errant excel file?  

[PeterAP]

Hi @jenv20032000x 

Is the excel file with a different schema showing a DATETIME instead of just the DATE? If so, you could try using this function in excel to remove the time portion of the file and then cut and paste values over the original column.

Alternatively you could try adding it as a separate input file in Alteryx and use the select and union tools to join it on to the remaining 38 files?

I hope this helps!

[jenv20032000x]

@PeterAP 

Yes, my errant file is showing datetime instead of just date.  To what excel function are you referring?

[PeterAP]

Apologies, I forgot to type it in!

=DATEVALUE(TEXT(A2,"dd-mm-yyyy"))

[jenv20032000x]

@PeterAP  I'm sorry, that didn't work.  It returned #VALUE!.