Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Manage Data Connections | Alteryx 11.0

Highlighted
6 - Meteoroid

I am trying to input data via a directory.  The directory includes both .xls and .xlsm files.  I ended up setting up two directories, one for .xls and another for the .xlsm files.  I know I can create a macro to read all excel files but haven't done it yet.  I get an error  trying to read the data with a dynamic input tool.  The error is below. 

Error: Dynamic Input (24): Error opening connect string: Microsoft JET Database Engine: External table is not in the expected format.\3274 = -328602519

 

I've looked this up in google and it basically says that it isn't really a table error but may be a driver issue.  Has anyone seen this before and do you have any suggestions?  Thanks. 

 

Highlighted
Alteryx
Alteryx

Hi @mm62733

 

What is the format of the file path your are passing into the dynamic input? Have you included the sheet name from the .xls or .xlsm files?

 

E.G. [FullPath]+"`|Sheet1$`"

 

I have often found adding the sheet name before the dynamic input and using the replace full path option in the dynamic input will remove this error message.

 

Best,

 

Jordan Barker

Solutions Consultant

Highlighted
6 - Meteoroid

Jordan,

 

I think I've figured out my problem.  My Dynamic Tool uses an .xls file as a template.  I don't know what to do about it.  Basically what I want to do is pull in a directory of files with various Excel file types and then rename the file path with the file name and the range name.  From there I want to use the data in the ranges to complete my process.  Do you know how I can use multiple Excel formats in a process?  Thanks.

 

Robin

Highlighted
11 - Bolide

@mm62733I have a feeling I have the same dynamics tool that you have.  I wasn't able to figure out how to get the file to open in Alteryx, so I have a PowerShell script that makes a copy of the file, opens it, then re-saves the file in the proper xlsx format.  I would definitely be interested if you find another way to resolve this issue.

 

jet error.png

 

 

Highlighted
Alteryx
Alteryx

@mm62733 & @adm510

 

Typically in this scenario users would use a filter tool to split the different excel file types and then use a dynamic input which correct template to read the files in.

 

E.G. From the dynamic input you can search full path for .xlsx and then pass these file paths into a dynamic input.

 

You will then be able to union them together after the files have been read in.

 

This could then be turned into a macro and have an repeatable tool to drag into the canvas.

 

best,

 

Jordan Barker

Solutions Consultant

Highlighted
6 - Meteoroid

Thanks Jordan.  I appreciate your quick response. That is most likely the approach I will need to use. 

 

Robin

Highlighted
11 - Bolide

@JordanB

Thanks, but unlike @mm62733, I only have one file type being generated from this tool and Alteryx doesn't like them.  The screenshot I posted above was using an input reader, not a dynamic input reader.

Highlighted
6 - Meteoroid

Is your file format on the input reader the same as the actual file type?  My problem came first at the input tool, my directory had several Excel file types.  I solved that by creating a directory for each file type and made sure the file format on the input tool matched the file format in the directory.  My next issue was that my dynamic input tool template was a specific file type which matched some but not all of the records input.  The records with the different file type were skipped and I received the same error message you have in your screenshot.  For now, I saved all of the Excel files as the same format.  Going forward, I'll likely use Jordan's solution as I don't own all of the files. 

Highlighted
11 - Bolide

My files are all xlsx format.  This is what happens when I try and open the same file with the different file format types:

 

xls - running this get me the Jet database error from the screenshot above

xlsx - all I get under Tables is "<List of Sheet Names>" but none of the actual sheet are listed or table ranges. Running the workflow will error

xlsm - acts the same as xlsx file types

 

screenshot is for xlsx/xlsm since they give the same error:

 

xlsx.png

Highlighted
6 - Meteoroid

First, don't use an input data tool to input more than one workbook.  Use the Directory tool instead.  This will input from a directory instead of one file.  Once you select your directory, put in the file type.   Use *.xlsx. (with the star first) as this will read everything in the directory that is an .xlsx file regardless of the name of the file.  Then you will want to append the range name or sheet name (assuming the same range or sheet is used in all files) for your data to the file name.  This is my formula for range name.  Trim([FullPath]) + "|'" + trim([Range]) + "'"  Then use the dynamic input tool to input the data in the files.  The template you use (any one of your files) should be the same file type (.xlsx) Make sure field is full path and action is Change entire file path.  That should get your data.  Hope this helps. 

Labels