Dynamic Input - Multiple Sheets On One Excel File - Null Fields

Joseph_Mediant

Hi Everyone,

I'm pretty new to Alteryx and very new to Dynamic Input.  I have one Excel spreadsheet with multiple tabs all using the same layout.  When I try to run it through the dynamic input, if there are any empty (null) fields, I get the schema error.  Is this the correct behavior? 

Sometimes certain fields will not have any data.  I have double checked all of the fields in Excel to make sure they are set to the same data format.  If I go in the data and replace all of the null fields in Excel with "0" it works fine.  

Is the only option to use a Macro to fix the issue?  

DataNath

Hey @Joseph_Mediant, I just quickly mocked something up to investigate this. Here I made 3 Excel sheets with the exact same schema. However, in one of the sheets I removed all records for one field so they became null, much like your scenario. When I went to investigate the metadata of this problem sheet, the 100%-nulled field appears to default to a double (numeric) data type, whereas its populated counterparts in the other sheets are a string.

Therefore, the schema is actually different due to varying datatypes. Because of this, we can't use the dynamic input tool and we have to move to the usual workaround for this - a batch macro. Here I can force the [Code] field into a string to match the others. Obviously you'll have to change the field etc here to suit your own needs:

When I run this, the datatype is changed before being output from the macro, and the 3 sheets can therefore be appended to one another as we wish:

Please let me know if you need any further help or explanation. Hope this helps!

Get around null fields changing schema.yxzp