Problem reading files

[rmelchiotti]

Hi, I am having a strange issues with reading files and was hoping some of you might help.

I have a series of analytics apps (all duplicates of each other, the only thing different between them are the files used to fill up dropdown fields). All the apps contain a field called Comments which is mainly left empty. Each app generate an Excel file with the same schema.

I now need to analyse the results of the app and decided to put an input tool to open all Excel files produced by the app in one go (using the * option) since they should all have the same schema.  Unfortunately when the column Comments is completely empty it gets read as a double while when it contains text it gets read as a string. I therefore don't seem to be able to combine all files in one go using an input tool with a * in the filename because Alteryx thinks they have different schema.

Is there a way I could do this other than opening each file individually and then union them (I have ~100 files so that would be painful)?

Any help would be greatly appreciated

[jamielaird]

Hi @rmelchiotti 

If you add a select tool to force the Comment field to be a string field that should solve the problem. Alternatively, you could add a dummy value e.g. a single space which results in the field being automatically assigned as a string type.

[rmelchiotti]

Unfortunately that does not seem to help because the reading is done before the Select Tool and Alteryx skips reading the files that do not follow the schema of the first Excel file (where comment is empty and therefore read as a double).

[andyuttley · Accepted solution]

hey @rmelchiotti 

Are your tables structured in the same way? Could you try checking the box 'first row contains data'? That way it should recognize everything as a string (so be fine), and from there use a dynamic rename and autofield/select after that, like this:

Obviously the stacking of files now is now dependent on fields being in the same place (as field F1 could differ between files). If this isn't suitable (because the file structures are so different) then I suspect you could use a batch macro to solve it (with Auto Config by Name checked) - let me know if you want any help with that... 

Andy 

[rmelchiotti]

Thanks Andy, that would work. I ended up using a batch macro as explained here https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-...  just because it was slightly cleaner.

Thanks a lot for all your help