Facing Error: Input Data /XML parse failure

Hi All,

I am trying to merge all input files with a wild card (" * ") which has same set of names as "Default123" but unfortunately although all of them are ".xlsx" format and not corrupted/do not have any sort of errors, I am getting an error as "Error: Input Data (5): Corrupt File: XML parse failure - cell has no column definition" every time even if I select other files with the same names.

Can anyone please help me related to the issue as it seems to be not working even if I feedle with the configuration pane. I have attached all screenshots for reference.

Thanks,

Alt Inp error.jpg

Alt Inp error-ss1.jpg

Input

Connectors

Data Investigation

Database Connection

Accepted answers

All comments

davidskaife

Hi @db89

Have a look through this thread, there are a few suggestions on it that you could try - https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Parse-Error-When-Connecting-to-XLSX-File/td-p/45133

BS_THE_ANALYST

Could it be that the sheet names aren't the same on the files that you're trying to bring in? @db89

db89

@BS_THE_ANALYST no they are all consistent in every worksheets.

db89

@davidskaife yeah saw this but not helpful enough.

davidskaife

@db89

Worth a shot! Are you able to share a file with us so we can have a look?

Incidently, can you import a file without the wildcard? i.e just one file by itself?

db89

@davidskaife Unfortunately I wont be able to share as it has sensitive data. But what i figured out is when I'm reducing the number of texts in each columns of the extract it is working! Like say one column has 2022:v1.2:123 and i reduce it to 1.2 across and make similar changes in the rest of the columns it is doing its job. Is it limiting itself due to characters present in the columns?

davidskaife

@db89

Just had a thought after looking at your screenshots again, try turning the AMP engine off in Workflow Configuration and see what it does?

db89

@davidskaife oh thats selected! Recent developments i have seen is, i deleted extra blank columns from extreme right and bottom all of them are getting merged with wildcard barring one which states "Warning: Input Data (1): The file "Default123.xlsx|||`ReturnInfo$`" has a different schema than the 1st file in the set and will be skipped"

So now out of 9 remaining 8 are getting combined absolutely fine. Now I am not sure what else I am missing as i have done the same for that file as well.

davidskaife

@db89 so that warning is simply the columns don't match in the file that is erroring.

Besides checking they're in the same order etc you will also need to check if the datatypes are consistent. I've had it before where one file was picking up a columns as numeric and the other as text so check that as well.

db89

@davidskaife yeah! i selected the entire set of columns and converted to text. But still getting that error! Weird.

davidskaife

@db89 must be something else in that file that is causing the error, at least 8 of the 9 files now work! Sorry i couldn't help get to the bottom of the overall issue

apathetichell

bring the file that won't go in via wildcard via a separate input data tool. compare columns. you will have one that is different. even if it is an extra column without data.

It's much more stable to bring in multiple files via a batch macro. the wildcard method really needs perfect schema to work.

MaciejG

Try opening and saving all of the files that are giving you this error.
Here is a quick python loop to do that for you (you can also do it manually if there are just a few files):

import os

from openpyxl import load_workbook

from openpyxl.workbook.workbook import Workbook

folder_path = "your folder path"

for filename in os.listdir(folder_path):

if filename.endswith(".xlsx"):

file_path = os.path.join(folder_path,filename)

wb = load_workbook(file_path)

wb.save(file_path)

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703