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,

Would it be possible to see screenshots of the configuration for both the input and output tools in your macro? Or attach a copy of the workflow + macro (no data of course)?

Regards,
Ryan

[mutuelinvestor]

Ryan,

Thanks for the reply. I won't have access to my computer to post the config until later this evening, but I will gladly do so.  

Is there something in particular you are looking for, common mistakes or otherwise?

Thanks again for the reply I will be back to you with images of my configs.

JIm

[rdoptis · Accepted solution]

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

[mutuelinvestor]

Ryan,

Thanks so much for your input.  With your response and guidance, I was abel to get my macro working - it's my first.

I was not adding the pipe and sheet name to the path. Once I did (also inserting a # sign in the beginning and a $ at the end)  the macro worked like a charm. 

I really appreciate your help. Thank you.

Best regards,

Jim

[rdoptis]

No problem, Jim - I ran into that same issue with connection strings while building my first macros! 

Cheers,

Ryan