macro input cannot be used as question input for this action type - mass uploading

Basically I have a directory with a bunch of files I want to iteratively (or I guess in parallel if anyone knows a way) put into a redshift data base.

I am trying to bring in the paths for dynamic input, and before the macro input I create a new string to match the odbc string type for output. I also use dynamic input to load the file and a stop until done to make sure the file doesn't error. My issue is it won't let me bring the manipulated string from the macro input to the output to replace the string that designates the table on the output. workflow attached. What would be fed into this macro is a directory input with the files.

When I try to upload I get this error: macro input cannot be used as question input for this action type so it basically won't let me bring my manipulated odbc string into the top input of the output.

macroissue.PNG

Macros

Iterative Macro

Accepted answers

jwalder

You as long as everything is going to the same database, you can specify the table name from a field on the input stream.I presume you are using the Redshift Bulkloader, so something like this:

2018-08-15 11_44_10-Clipboard.png

All comments

jwalder

There are a number of ways to attack this, but I recommend direct use of S3 and Copy commands. On the partner tools page you will find both tools for moving files to S3 as well as SQL execution that support streaming the commands/sql on the incoming row. As such, you can use the directory tool to list the files, copy to S3, use a formula tool to dynamically create the COPY command and feed that to the Execute SQL tool. https://community.alteryx.com/t5/Alteryx-Knowledge-Base/Partner-Tools/ta-p/123297

BenMoss

Can you not pass the manipulated string into a control parameter to, then use this to overwrite the string within the output data tool.

In fact I would consider scrapping the macro input at all; consider using an input data tool and just overwrite the connection string/query with the control parameter directly rather than having to use the dynamic input.

If you could show sample screen shots of the data flowing through at each stage that would be helpful.

Ben

jogianni

yeah I can get rid of the macro input and just have the directory input read in. But I don't think control parameters have any input option? is that the parameter type you meant? I have attached some screenshots (sorry for the redaction of private info)

step 1: read in the directory

step 2: grab the file name, then concatenate with a hard code of the connection string that the output data uses to write to redshift and append the filename (which will be the table name) to the end of it, just like the connection string does when you output to redshift with the output tool

step 3: use a select tool to just grab filename, the new connection string I created in the formula tool, and the path.

once in the macro: the filename and path are used to dynamically load the table, the new connection string is supposed to be fed into the output data tool but thats the part I can't figure out how to do.

step3.PNG

step2.PNG

step1.PNG

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235