Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Knowledge Base

Definitive answers from Designer Desktop experts.

Error(s) when opening XLS files after applying Microsoft’s October 2017 updates

CailinS
Alteryx
Alteryx
Created

UPDATE 11/20/2017:

Microsoft released a fix for the XLS issue described below, this fix is included in their November 14, 2017 Monthly rollup.

For your convenience, this is the link to the fix for Windows 10

https://support.microsoft.com/en-us/help/4048954/windows-10-update-kb4048954

And this is for Windows 7 and other OS

https://support.microsoft.com/en-us/help/4048957/windows-7-update-kb4048957

We’ve been made aware of an issue connecting to .xls files with the File Format: Microsoft Excel 97-2003 (*.xls). The behavior manifestsin two ways.

As an error pop-up box:

error box.png

'InvalidArgument=Value of '0' is not valid for 'itemIndex'. Parameter name: itemIndex'

Within the Results - Messages pane of the Designer or on the Annotation associated with the impacted tool:

Error: Input Data (2): Error opening connect string: Microsoft JET Database Engine: Unexpected error from external database driver (1).\3275 = -535434136

The error box appearswhen an Input Data tool is configured to open an .xls file for the first time ever (if there are cached tables, then the error box will not appear). TheAnnotation and Results pane errormessage occurswhen an .xls file is dragged directly onto an Alteryx designer canvas or when a workflow containing an Input or Output tool that connects to an .xls file using the above format is run (within the designer, a server, or on a schedule), respectively.

The error and experience are discussed in this Alteryx Community post, though you can also read about the issue on this external forum and here, documenting this is a widespread experience across many platforms. Towards the bottom of the external forum post, you can see a comment from a Support Engineer on the Microsoft SQL BI team stating that Microsoftrecommends not using the Microsoft JET engine drivers as the current solution to the issue. We detail below how to follow that directive within Alteryx.

Theroot cause of the errorcomes from Microsoft’s October Security Update (links below).

If you have already had the update installed, must remain using the update, or choose to proceed with the update, you will experience this behavior. In those situations, here are a couple options available to you.

  1. For new or existing workflows:
    • Within Excel, manually save the .xls file as a .xlsx file and within Alteryx update tools pointing to the old file to point to the new file or Browse to the new file within an Input Data tool
  2. For existing workflows:
    • Within Alteryx, manually change the File format to ‘Microsoft Excel Legacy (*.xlsx)’ within the affected Input Data or Output Data tool (described below)
  3. For new workflows/new Input Data tools you will not be able to manually Browse out to the .xls file and read it in as you will receive the pop-up error message desribed above. Your options are:
    • Drag and drop the desired .xls file from the Windows explorer box onto the Designer canvas and then manually change the File format to 'Microsoft Excel Legacy (*.xlsx)'
    • Drag an Input Data tool onto the canvas and paste the full path of the desired .xls file into the Connect to a Database box within the Configuration pane and then manually change the File format to'Microsoft Excel Legacy (*.xlsx)'

Option 2 and 3 Step-by-Step

  1. Determine if you have the “Microsoft Excel Legacy (*.xlsx)” option available within your Input Data tool. You can determine this by loading any file into an Input Data tool, and then clicking the “File Format” drop-down. If you have this option, you can skip to Step 7.

    no 1.png
  2. Determine the “bitness” of your Office Installation. The file you download will depend on whether you are running a 32-bit or 64-bit version of Office.

    https://support.office.com/en-us/article/About-Office-What-version-of-Office-am-I-using-932788B8-A3C...

    In this screenshot, I’m using a 32-bit copy of office.

    no 2.png
  3. Browse to the Microsoft Access Database Engine 2010 Redistributable page and click “Download”

    https://www.microsoft.com/en-us/download/details.aspx?id=13255
  4. Select the download that matches your Office “bitness” from before, then click “Next”. The file will begin to download. Save the file to a location that you can run it from.

    no 4.png
  5. Run the previous installer you downloaded. You can simply click “Next” or “Install” through the installation utility, as options do not need to be changed. Once you receive the dialog that the setup has completed successfully, you can click “OK” and continue to the next step.

    no 5.png
  6. If Alteryx Designer was open prior to the installation, you should close the program fully, saving your work, and then re-open Alteryx Designer. Otherwise, if Alteryx Designer was not already open, you can open it now.
  7. Within Alteryx Designer:
    Open the workflow with the issue in Alteryx Designer.
    b. Click on your Input Data or Output Data tool that is experiencing the issue. (Refer to Option 3 above if you are receiving the pop-up error box while connecting to an .xls file for the first time)
    c. Click the “File Format” drop-down and choose “Microsoft Excel Legacy (*.xlsx)”

    no 7c.png

    d. Data should now display in the preview window and the error should no longer appear if you click off the tool or run the workflow.

    no 7d.png

We understand this is likely to be a frustrating experience for our users and we are investigating what can be done from the Alteryx side to produce a fix for the issue. We are also following the Microsoft posts to understand what their response will be and if they will take any action toward resolution. Please contact Customer Support to report your experience with this issue, as that will allow us to contact any impacted users directly if and when there is a fix made available.

Updates associated with the error:

Windows 7, Windows Server 2008 R2 - https://support.microsoft.com/en-us/help/4041681/windows-7-update-kb4041681

Windows Server 2012 - https://support.microsoft.com/en-us/help/4041690/windows-server-2012-update-kb4041690

Windows 8.1, Windows Server 2012 R2 - https://support.microsoft.com/en-us/help/4041693/windows-81-update-kb4041693

Windows 10 Original - https://support.microsoft.com/en-us/help/4042895

Windows 10 1511 - https://support.microsoft.com/en-us/help/4041689

Windows 10 1607, Windows Server 2016 - https://support.microsoft.com/en-us/help/4041691

Comments
CailinS
Alteryx
Alteryx

UPDATE: According to this Microsoft Support page, they are 'working on a resolution and will provide an update in an upcoming release.' We are still investigating a fix for Alteryx and will provide an update when we have one!

ddiesel
13 - Pulsar
13 - Pulsar

Unfortunately this has impacted many of my applications. So frustrating to have an inbox full of app failure notifications --- especially since some of my peers were just starting to have confidence in Alteryx apps!

 

Thanks for this post. Super informative and I'm troubleshooting now.

CailinS
Alteryx
Alteryx

@ddiesel I am sorry to hear about the inbox full of errors! Especially in light of the exciting comment about your peers getting started on their Alteryx journey. Please follow up with the Support team to log this issue and also to investigate all of the options available to you to mitigate this negative experience for you and your colleagues!

Citauma
7 - Meteor

Hi @ddiesel

 

I am using Alteryx 11.5 on a server and Microsoft Excel Legacy (*.xlsx) is not one of the input file options. What am I to do in this case?

xls error.png

CailinS
Alteryx
Alteryx

@Citauma sounds like you do not have the Legacy driver installed. You should be able to follow the step-by-step guide to determine which download to proceed with in order to get the option. Once you've succesfully downloaded and installed the drivers, you may need to restart Alteryx to see it as an option.

drahn
7 - Meteor

I was able to get this working using the "Legacy" File Format. 

The biggest downside of this is that the Output Options does not come with the "Delete Data & Append" option...now I have to overwrite the file, which means the formatting of the Excel sheet gets erased. 

 

This really causes a ton of frustration on the users that are trying to consume the output file. I'm hoping this can be fixed soon.

JosephK
5 - Atom

New user to Alteryx, but hit this error while working through the tutorial. My coworker was unable to recreate the issue. We compared Alteryx version numbers, he has 11.3, and I have 11.5.

LindaT
Alteryx
Alteryx

Great News!  Microsoft has released a fix for this issue.  It is in their November 14, 2017 Monthly rollup.

jakub-mg
6 - Meteoroid
@LindaT - thanks for the info, any idea if/when Windows Server 2012 R2 might get this?
LindaT
Alteryx
Alteryx

It appears to be available for all OS's at this time.  You can find it on the Microsoft site, if the November 14, 2017 Monthly Rollup hasn't shown up in your Windows Updates.

LindaT
Alteryx
Alteryx

For your convenience, this is the link to the fix for Windows 10 

https://support.microsoft.com/en-us/help/4048954/windows-10-update-kb4048954

 

And this is for Windows 7 and other OS

https://support.microsoft.com/en-us/help/4048957/windows-7-update-kb4048957

jakub-mg
6 - Meteoroid
Thank you for the quick reply Linda - so reading the description, once we apply this rollup, there should be no need to install the Microsoft Excel Legacy driver on our servers :)
LindaT
Alteryx
Alteryx

That is correct!   We verified their update and everything is back to normal with the XLS files.

CailinS
Alteryx
Alteryx

I would add that going forward it may be prudent to start a habit of building workflows (in an out of Alteryx), in as many scenarios as possible, that do not rely on the JET driver. Perhaps this experience will mean that in the future the driver won't break without warning, but that isn't to say it will continue to be supported long-term.

DavidAngevine
7 - Meteor

I was able to get this to work using a .xlsx conversion.   Do I still need to create an upgrade to Windows 7 OS for future use of .xls files?

Thanks for publicizing this issue and various methods of fixing the issue.

 

jakub-mg
6 - Meteoroid
I'd recommend applying security updates regardless. It sounds like you have control over the format of your source data file though? I guess my interpretation of the thread (including the note from CailinS about avoiding the JET driver) was that if we can avoid .xls sources - we should :)
DavidAngevine
7 - Meteor

Jakub - I work in a corporate environment where I have no control over which updates are applied.   Therefore, the update from 12-13-2017 was applied to my PC but this optional Windows Update (KB4041686) was not applied.   Unfortunately, I do not have admin rights for my PC and I am now applying to my IT department to get this fix manually pushed to my PC.   A little red tape here, but I will get it applied.   The reason I need want the update is that I will not always be able to avoid the .xls source.   This update will prepare me for future files that might be .xls.  However, I have fixed my issue by renaming my current file and my workflow runs smoothly again.

Thanks for your input on avoiding .xls sources in the future as I will incorporate that in new workflows.

 

 

 

CailinS
Alteryx
Alteryx

@DavidAngevine yes the only total fix available at this time is the Microsoft update - hopefully you can work it out with IT!

Anne-Lise
7 - Meteor

Dear all,

 

We have the same type of problem here in October 2018 with one of the Windows patch. We tried the manipulation you've suggested last year, but we have another error:

"Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519

NicolaK
5 - Atom

I am getting the same error like Anne-Lise ("Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519") and the solution provided above didn't help.

 

Any other suggestion?

CailinS
Alteryx
Alteryx

@NicolaK @Anne-Lise I believe a Customer Support case has been opened on your behalf. If you have not received communication about your experience yet from someone in Support please feel free to open a new case with us for follow-up! 

domo_arigatoo
7 - Meteor

I have the same issues with @NicolaK and @Anne-Lise. I have also created a new case with Alteryx Support but no resolutions are given to me. And now Alteryx Support is about to close my case. Please assist. 

Anne-Lise
7 - Meteor

No news on my side either since the unsuccessfull attemps by installing Access 2010 driver.

domo_arigatoo
7 - Meteor

Hi @Anne-Lise, did you download the 32bit or 64bit Access Database Engine? 

ravisiddharth
6 - Meteoroid

Hi,

I have the same issue too and i downloaded the 32 bit Access database engine as suggested but ended up getting the error ""Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519"

Anne-Lise
7 - Meteor

Hi @domo_arigatoo

 

I don't know, this is our IT Department which manages version... for other applications I'm running with 64 bits, so I would assume this is also the case here...

JerryTsang
5 - Atom

Have this issue solved? I also encounter same problem, I have downloaded the 32 bit Access database engine as suggested above, but also got error 

"Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519"', is there any solution?

lepome
Alteryx Alumni (Retired)

@JerryTsang @ravisiddharth 

Have you resolved your issue?  If not, you should check here and here.  A common cause of the error you report, "Error opening connect string: Microsoft Access Database Engine: External table is not in the expected format.\3274 = -328602519", is that you are using a Dynamic Input tool in a way that was not intended.  That tool requires that you specify a template and that all the files you read in with it have exactly the same columns and data types (all the same "schemas").  If this is not the case, then you are better off using a batch macro.  There are a number of resources that describe how to write one, particularly here and here.

gantaanvesh
8 - Asteroid

Hi Guys,

 

can anyone help me the with this error, when am trying to import the xls file into Alteryx am getting the following error?

after changing the file type manually into excel workbook am able to import .. but I need to automate this workflow 

aaaa.JPG

lepome
Alteryx Alumni (Retired)

@gantaanvesh 

I think the issue lies with the way you changed the file type.  If you open the file in Excel and then save it as an .xlsx, can Alteryx read the .xlsx file?  You don't mention the version number of Alteryx you are running, but assuming that it has the "classic" Input Data UI, you might find that attempting to open using the "Legacy" driver may help.  In some cases, outputs from third-party programs that say they are in an Excel format are not written exactly as the driver (Microsoft Access Database 2010 Redistributable 32-bit) expects.

 

Input Data Excel file types.png

 

If this is a consistent issue for you and you have a standard license, I'd encourage you to open a case with support@alteryx.com.

chinu267
8 - Asteroid

Installed access engine 2016 and it does not help.

These are XLS files imported from a website.

I am using 2019.2 version of alteryx

Error reading xls.PNG

lepome
Alteryx Alumni (Retired)

@chinu267 

I don't know whether the 2016 version works.  The 2010 does. 

 

As noted in my earlier comment above however, sometimes Excel files from third parties are not completely compatible with that driver.  You might have more success if you open the worksheet in Excel and save it directly from Excel (as an .xlsx).  Please also see this comment for ideas on how to automate that step.  

chinu267
8 - Asteroid

@lepome Thanks for looking into it. Appreciate your response.

 

Sorry forgot to tell that I had the 2010 driver and had the same error. So installed 2016 but no luck.

 

i have 1000+ files and manually can not save them as xlsx. Will look into the link for automating it.

 

Sometimes I feel like using notepad++ instead of alteryx to process my data as it has its limitations 

Slushercw
8 - Asteroid

Has this error been fixed?  I've got a workflow I am trying to automate without having to touch the file.  It's a .xls file.  I have tried using both the 32 bit and 64 bit access drivers with both the .xls and .xlsx (legacy) type selected and I either get the "install the 32 bit driver" error or I get the 328602519 error.  I pulled a macro from the KB to convert the files from .xls to .xlsx but it doesn't seem to be working (I'm guessing for the same reasons the input tool isn't reading the file).  I'm using Alteryx version 2019.2.  My file is automatically dropped on the network daily so I am trying to get the workflow to pll the file based on today's date without having any manual function in the process.  I'm guessing I will need a dynamic input/directory tool to do that part, but I am having trouble understanding how to structure those tools even after looking through the tool mastery guides and watching some videos.

 

Any help would be appreciated.  Thanks!

lepome
Alteryx Alumni (Retired)

@chinu267 @Slushercw 

 

I don't know for sure whether this will resolve the latest issue, but I think it's definitely worthwhile to try these steps:

  1. Uninstall the Microsoft Access Database Engine Redistributable 2016 32-bit if you have it,
  2. Install the Microsoft Access Database Engine Redistributable 2016 64-bit if you need a 2016 driver, and then 
  3. Install the Microsoft Access Database Engine Redistributable 2010 32-bit.

If you try this, please report back on whether it helped.

chinu267
8 - Asteroid

I used the converter available to convert my excel to new format and that resolved the issue.

 

C:\Program Files (x86)\Microsoft Office\root\Office16\excelcnv.exe -oice "path\targetfile.xls" "path\targetfile.xlsx"

.

Refer to below URL for details:

 

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/quot-InvalidArgument-Value-of-0-is-not... 

Slushercw
8 - Asteroid

@lepome following those steps didn't work.  I'm still getting the 328602519 error after installing the 64 bit 2016 driver and the 32 bit 2010 driver.

 

@chinu267 that seems to have worked for me.  I just needed to figure out how to use the macro properly haha.

 

What's the best practice for reading in a file with dynamic name changes?  I'm assuming it's the dynamic input tool but it's almost like all of the tutorials I've seen are too complicated.  I have a file that's basically titled with the current date + report and I'd like to set up the workflow to pull whatever file has today's date in the filename.  Is there a simple way to do this?

lepome
Alteryx Alumni (Retired)

@chinu267 

This is off-topic for this post, but you could use a batch macro and then place it after a Formula tool you use to construct the filename.

kurtwz
8 - Asteroid

The simplest solution I've found to this problem involves changing the exact way you tell Alteryx to connect to the file.  When I would try to paste the path of the file into the Input Data tool in the Connect a File or Database box, I would get this error. 

 

Instead do this:

1. Click the Input Data dropdown,

2020-03-11_17-25-21.png

 

2. On the Data connections window, select Files on the left side

2020-03-11_17-30-17.png

3. Drag and drop the file to the Select a File space. 

2020-03-11_17-31-20.png

After you release it to that space, you should see the Data connections disappear and the Select Excel Input window appear.  If it does, you're good to go. Somehow Alteryx Designer doesn't like the path paste method, but luckily there was an easy workaround for me.  Hope it works for you too!