Alteryx not Reading Excel Files Converted from PDF

[alteryxuser_1184]

I'm facing an issue where input tool is not reading all the files. I have 99 excel files. Now, these excel files were originally in PDF. So I standardized each excel file after converting. However, when I input all of them, Alteryx is only able to read 18 files. The reason Alteryx gives on not importing the other 81 files is this: 'Table 1' has a different schema than the 1st file in the set and will be skipped. I then separated 81 and 18 files in different directories and imported them separately. However, again Alteryx is unable to read all 81 files. Attached "ADDRESS-CUSTOM" is one of these 99 files that Alteryx is not reading. Since these excel files were originally in PDF, it could be that after conversion to excel, Alteryx is unable to read. Or it could be some configuration in Excel security settings? Attached is the "reading error" screenshot. 

[Garabujo7]

I tried to read the file and got no errors.

How are you reading the files?

With an input tool and the * wildcard? if that so, the layout of the files has to be the same to read them all at once.

Gabriel

Gabriel

[alteryxuser_1184]

The question should have been phrased this way: Alteryx can only read 18 out of 99 excel files. Yes individually, Alteryx is able to read file by file, however when importing in bulk by using "*" this in the end then it is unable to read majority of files.

[danilang · Accepted solution]

Hi @alteryxuser_1184 

When Alteryx is telling you that "Table 1' has a different schema than the 1st file in the set and will be skipped", it's referring to the column names and data types not matching across all the files.   This error only occurs when you try to load multiple files using wildcards.  Possible causes error include

• Leading and trailing spaces in the column headers
• extra spaces inside the field names
• different dash types "–"(en-dash)  vs. "—"(em-dash) 
• character substitutions "I" (upper case "I") vs "l" (lower case "L")
• etc.

Unfortunately, there no easy way to find the differences without loading the files individually and carefully comparing the field names and types. 

There are macros that can help with the process such as the Wildcard XLSX Input tool from the Crew Macro pack. What these do is iterate across all the input files and union the results.  Data in fields that match will line up in a single column.  Fields that don't match will be separated into multiple columns.  You can use this output to go back and correct the fields in the original files or develop a workflow to merge the data into a single group of columns

Dan