Batch Macro to process excel files throwing error message

mutuelinvestor

I'm trying to create a batch macro that will:

1. read files (xls files) from a directory,
2. import the files into Alteryx,
3. write to a table in my sql database. 

My macro module is comprised of two data integration tools that get and set the file name for the input tool.   The input tool imports an excel file(s) and then connects to an output tool that creates a table in my sql database.   When I run the macro module independently, it works like a charm.  However, when when I insert the macro into my processing module I get the following error:

Ole db connection strings should be in the form: odb: ConnectString | table.

I'm assuming because I'm running a sql query against my excel sheet Alteryx thinks it's working with a database so its expecting a oledb connect string instead of a path and file name.  

My processing module is comprised of a directory tool, a select tool, a formula tool and the macro that I created. 

Has anyone come across this problem and developed a reliable workaround.   I read post in the forum that excel files can be problematic with the input tool from time to time.  Thanks for your input and I hope I can return the favor sometime.

I've included images of my modules and output below for additional clarity:

Macro Module

Processing Module

Output

rdoptis

Hi Jim,

The things I would want to look at in the order of importance:

1) Which tool in the macro is tool 26. Do you know for certain if tool 26 is the input or output tool? I have seen Ole DB errors with Excel files before, but just want to be sure before chasing down a problem in the wrong tool.

2) Ensure the sheet names and path are being manipulated properly for the input tool. Example: if each Excel file has the same sheet name, ensure the action tool is only replacing the path, but not the sheet name. The connection string has the path and sheet name concatenated and pipe delimited for Excel files.

3) Test the values coming through the control parameter to the action tool. Make sure the paths are as expected.

     a. Injecting the paths into a field with a formula tool, then attaching a macro output directly after is a way to test this.

Those are common places iv seen macros like this have issues. If none of these are the cause, post a copy of the workflow and we can dig deeper.

Best regards,

Ryan