Alteryx crashes when loading XLSX file

[craigja]

I have a file that a Power Automate process is downloading from a website, I can open the file with no errors in Excel but if I try to open it with Alteryx it crashes the application.  If I open it in Excel and resave as a different name but same filetype it opens fine!

[IraWatt]

Hey @craigja, 

Is there any strange characters in the name of the xlxs file? 

[flying008]

Hi,@craigja 

Maybe there is like 2 point to strike you:

1- the file name include special characters out of [ 0-9 | a-z | _ ] range .

2- Your OS/Software security settings prevent direct opening of downloaded files.

Could you please show the crash file name?

[fharper]

@craigja , It would be very helpful to share the downloaded file before modification, but if there are protected data we may not be able to explore for you. 

That said we are somewhat blind but I will share some thoughts based on experiences.

1) you should verify the source file and how the Power Automate (PA) process works, you can do a lot with it and one thing is you can rename, if the process was built with the expectation everything is going to be xlsx it may be changing the extension but the data adheres to an older or other version's data standard like xls or xlsb... check the exact version/type of the excel file "Crashing" the system.  See if you can download or manually access it instead of using power automate to see the actual file and format and version to verify PA or that process written in PA is not causing an issue.

Keep in mind there are 14 different file extension types for excel I am aware of https://support.microsoft.com/en-us/office/file-formats-that-are-supported-in-excel-0943ff2c-6014-4e...  and 19 by number code

Name Description Value Extension
xlExcel12 Excel Binary Workbook 50 *.xlsb
xlExcel2 Excel version 2.0 (1987) 16 *.xls
xlExcel2FarEast Excel version 2.0 far east (1987) 27 *.xls
xlExcel3 Excel version 3.0 (1990) 29 *.xls
xlExcel4 Excel version 4.0 (1992) 33 *.xls
xlExcel4Workbook Excel version 4.0. Workbook format (1992) 35 *.xlw
xlExcel5 Excel version 5.0 (1994) 39 *.xls
xlExcel7 Excel 95 (version 7.0) 39 *.xls
xlExcel8 Excel 97-2003 Workbook 56 *.xls
xlExcel9795 Excel version 95 and 97 43 *.xls
xlOpenXMLStrictWorkbook Strict Open XML file 61 (&H3D) *.xlsx
xlOpenXMLTemplate Open XML Template 54 *.xltx
xlOpenXMLTemplateMacroEnabled Open XML Template Macro Enabled 53 *.xltm
xlOpenXMLWorkbook Open XML Workbook 51 *.xlsx
xlOpenXMLWorkbookMacroEnabled Open XML Workbook Macro Enabled 52 *.xlsm
xlTemplate Excel Template format 17 *.xlt
xlTemplate8 Template 8 17 *.xlt
xlWorkbookDefault Workbook default 51 *.xlsx
xlWorkbookNormal Workbook normal -4143 *.xls

Your mention of opening in excel manually and saving to another name may be doing more than renaming but also writing it out under the modern version/form of excel versus the source version.  New versions of excel are typically set to "Compatibility Mode" or are backward compatible but will write/save in the newer data standard.

2) A few years ago we had to add "AccessDatabaseEngine.exe" to help Alteryx run with Excel on our servers because the servers did not have office installed and a driver/component that used to be directly available and part of drivers commonly used was shifted to be delivered within the larger office Suite install or Access Install and not available as a standalone.  So if your issue is that it works on laptop, where you may have Office or ACCESS but not on server then explore that possibility.

The critical thing in solving is finding root cause...so try to get the file direct, outside of PA, and analyze it, version, extension..

Consider writing vba code to open and ask for the XLFileFormat value to know exactly what you are dealing with.  a macro that uses ActiveWorkbook.FileFormat to capture the number value should tell you if you are getting something other than xlsx, assuming a direct look at the file did not show a different extension than xlsx.

If nothing shows there then consider if Access driver needs updating, but this is more likely an issue for server as most laptops have office suite which would have all the components you might need...most likely.

But without the source file and details on the PA process being used I am not sure I can help more than these guesses here.

[craigja]

This is the file name: Holdings_Custom_Report_-_Cross_Holdings_-_EmmaL_2022_04_25_162329_GMT.xlsx  If I open it in Excel and save it, the file works fine though. 

[IraWatt]

Yeah to be fair the file name seems fine. Perhaps it is coming in as a strange file type then when your save in excel it reverts to a proper xlsx. Before you've saved it what does it say when you right click on it and select properties like so:

Do you drag and drop the file onto the Alteryx canvas?

[craigja]

if I open the file direct from the website in question, (so removing PowerAutomate) it still crashes Alteryx.  Yes, Im just dragging the file to the desktop but I just tried using the input data tool and browsing and its also crashed

[fharper]

@craigja a thing to try, reverting to my earlier verbose discourse, is to download direct then write a vba macro to open the file via vba and capture the enumeration code value of XLFileFormat.  this should tell you what excel perceives the file type to be where the extension can be incorrect.  you can rename a file with any extension you want and in doing so may make it unreadable by a system that has certain expectations on file format based on extension.  so directly downloading and then opening in Alteryx only helps reduce the possibilities, i.e. PA did not cause this.

Another thing to try, if the above does not show a discrepancy on enumeration value vs extension, is on a version directly downloaded do a rename in file explorer, do not open in excel and save as.  in the renamed file call it the same thing but trim off the leading part of the string "Holdings_Custom_Repor".  some times systems get hung up of file name length, can be OS version issue or driver or app.  this will test just ability to open in Alteryx a shorter file name.  if still not working then rename it to Testxxx or something very simple, again do so from File Explorer. and then try dragging on to a canvas and see if it opens.

QQ...

1. what is the OS of the machine, windows 7, 10 etc, version...
   1. on a laptop/desktop or server or VDI
2. do you have office suite or just excel? and what versions
   1. is ACCESS db installed, newer versions I think get by default
   2. you might try installing latest ACCESS db and thereby get latest MS drivers that include some relevant to Excel
   3. What version of Alteryx are you using?

if none of this gets you closer or resolved I suggest opening a ticket to Support@Alteryx.com and see if they can help.  because you probably don't want to share your data with the general community you may be able to share more fully with Alteryx Support and they may also have seen this before.  work both the Community and Support in parallel and let us know if you get a resolution before/without us.

[craigja]

Unfortunately I cant even share the data with the Alteryx support team - if I leave it in Alteryx it does eventually come back and gives me this:

Error: Input Data (1): Error parsing xml file '<?xml version="1.0" encoding="UTF-8" standalone="yes" ?><sst xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main 

Along with the full content of the document, so I assume what has went wrong is the company creating the document is not fully complying with the standard,  but Excel will open it, clean it and allow it to be saved.  This is my work around, after downloading, I open it in Excel and re-save - all within PowerAutomate and it works fine

[flying008]

Hi,@craigja 

in fact like you said, maybe the document is not fully complying with the xml stream standard, so you can convert the format xlsx to xlsb use script in even before input file.