Read Multiple Excel Files as input

Question

I have a huge set of input files in a specific folder which I need to combine into a single file & then take it further. I have  already tried using asterick(*) and directory tool but both didn't help as Alteryx throws an error stating files have different schema.

All the files in directory are of same format only change will be the number of rows. Can someone please help me out on why this error comes up even tough the format of files remain same. I would prefer not using Batch Macro as I am not an expert in creating the same.

Regards,

Abhinandan

DavidP · Answer

Even if you column names are exactly the same, you can get a different schema error if the datatype of one or more fields is different in one or more files. Have a look at the examples attached.

There are 2 files: testfile1.xlsx and testfile2.xlsx

Both have 2 columns: Col1 and Col2 and if you open them they look pretty similar, but Col2 in testfile2 is formatted as text while the rest are all numbers.

Example workflow1 tries to load both files with a directory tool and dynamic input, but gives the different schema error.

Example workflow2 uses a simple batch macro to read the files and works.

Don't be afraid of batch macro's - they're not as scary as you think.

load multiple files example 1.yxmd

load multiple files example 2.yxzp

testfile1.xlsx

testfile2.xlsx

JordanB · Answer

Hi @abhikuma

Unfortunately within the wildcard and the dynamic input there isn't the option to avoid it skipping the fields which do not match. Although it appears these files match, there maybe some additional characters which are throwing of the read of these files.

I would suggest following this guide on the batch macro which will walk you through the process.

https://community.alteryx.com/t5/Alteryx-Knowledge-Base/CS-Macro-Dev-Reading-in-Multiple-Files-with-Different-Field/ta-p/13370

Best,

Jordan