Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

'Tis the season to be spooky! Read our new blog, How Spooky is Your City? Mapping and Predicting Scary Stuff. In it, @SusanCS provides a fun glimpse into using data to figure out the creepy quotient of where you live! And don't forget to check out our Digital Costume Thread to get yourself in the mood for a candy binge!

"InvalidArgument=Value of '0' is not valid for 'itemIndex' error

Highlighted
12 - Quasar

I have one solution for the " InvalidArgument=Value of '0' is not valid for 'itemIndex' " error when it is in a workflow... 

...but now I need a solution for this error when using the interface-file browse tool.

 

USE CASE:

 

I am forced to work with files that are tab delimited but they have a .xls extension. Excel opens these files after throwing a... "The file you are trying to open, 'FILENAME.XLS', is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?' ... Excel can do it but I couldn't even get to configuring the input data tool in Alteryx before I would get the "InvalidArgument=Value of '0' is not valid for 'itemIndex'" error.

 

I brought this problem to the solutions center at Inspire'17 and Ned Harding helped me solve this problem by dragging the file to the canvas, forcing the format to .csv in option 2, bringing it in with no delimiters in option 5, and unchecking 'First Row Contains Filed Names' in option 6. From there I can parse out what I need and carry on. All good!

 

SOLUTION:

Capture.PNG

 

NEW PROBLEM:

I built out all my workflows and they are working great, however, I'm trying to turn them into apps so that others on my team can use these tools. The workflows work smoothly but when using the interface-file browse tool in the app, it throws the same invalid argument error. I can't get past it.

 

How do I override the file extension with an action tool? I need to be able to use the file browse tool to let users upload their own tab delimited saved as .xls files. I've played with it for hours and scoured the community but I can't find a solution to this. Please help.

Highlighted
Alteryx Alumni (Retired)

Hi @ddiesel,

 

Take a look at the options for your input data tool.  Option #2 specifies the file format.  This is changeable through an action tool - you just need to know the number that corresponds to your file format.  I think csv is designated with a "0". 

 

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@ddiesel,

 

@Ned gave you great advice, but I want to provide you with an alternative approach.  If the files all look like *.xls and you would prefer them to look like .csv or .tab then why not copy/rename them with Alteryx and then read them in to the workflow/app?

 

Your first workflow would do the rename/copy (possibly with a run command of xcopy) and then the next chained workflow would have an easier time.

 

Just wondering....

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
14 - Magnetar

I took a look to see if I could come up with a direct solution for this and unfortunately the workarounds I know of to allow uploading a custom file don't handle files with "improper" extensions well.

One potential option you might be able to look at (HIGHLY dependent on your organization's security policy, as well as how often the app will be run) would be to set the workflow up to read in .xls files saved in a specific network drive location, and then clear that location out after the fact.  This has some scalability concerns, but might resolve this specific need.

Highlighted
11 - Bolide

If the workflow is running correctly before you put it in a macro, then I doubt it's a file issue.  It could just be that Alteryx is changing the file format on you when you select a XLS file.  Whenever I change the file to one with a new file extension, Alteryx deletes all of my previous configurations on the input data source and updates it to match the new file format.  Alteryx is probably doing this behind the scenes, my suggestion is to either have a macro take the file, rename it to a CSV then read that file in OR use another interface component to set the file format back to CSV (you may also need to do this with the delimiter and any other items Ned helped you out with).

 

Does your workflow work correctly if you manually change the file extension to CSV?

Highlighted
12 - Quasar

First, I want to thank everyone for their replies! I have accomplished so much with Alteryx in the past few weeks and I could not have done it without this community.

 

I am working on other projects and traveling for the next two weeks so I will have to revisit this post when I return. However, I want to share what worked for me as an alternate solution.

 

While waiting for responses on this post, I changed my workflow into a batch macro following a directory tool. That did the trick for me. The files were read and passed through the batch macro with no error.

 

When I have time, I think I will try renaming the files first as per the above suggestions from @MarqueeCrew and @adm510. I'm almost sure those solutions will work but I have my task accomplished for now.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@ddiesel,

 

 

I've created a macro to help resolve the XLS issue.  It uses a Microsoft program (excelcnv.exe) that is already on your computers.  It might vary in the path to where it is stored, but that is an easy update.

 

 

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

Knowing that this command line solution will fix a single file, I created a macro that has no input/outputs and is configured by selecting a folder to operate on.  The macro will make new .xlsx files for all .xls files in that directory.

 

This is a work-around.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
8 - Asteroid

Has something changed with this tool?  I had previously been using the convert tool, but recently had to get a new machine and now for some reason it isn't working anymore.  I updated the location of my excelcnv executable and everything appears to be correct, but when I run it, it doesn't produce any xlsx files in the target directory.  Does the excelcnv file *have* to be in the default location?

 

CSlusher_0-1598303512691.png

CSlusher_1-1598303564264.png

 

 

 

Highlighted
8 - Asteroid

Update - I reinstalled office just to be sure.  It saved the excelcnv tool in the default location (and I updated the configuration in the macro).  I ran the macro and it appeared to be working, but none of the converted files are being output.

 

There are 5 .xls files in the target folder and this was the result of running the macro:

CSlusher_0-1598304850691.png

But then, when I open the target directory, it's still just the .xls versions

CSlusher_1-1598304909034.png

Not sure what else to do to get this working properly.  Any help is appreciated.

Highlighted
8 - Asteroid

I figured my issue out.  I just needed to include "-nme" (without quotes) in the command line prompt after drilling down into the macro.  Thanks!

Labels