This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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.
@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.
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.
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.
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.