Get Row by Row Data for executing rest of the workflow.

[spandana16]

Hello all,

Please help me with this scenario: I have list of Jobs listed in an excel. I need each row to be selected at a time , generate the output through a SQL, write the output to a CSV File and then loop through second row.

I have attached the input file and the Macro workflow I am using.

[rzdodson]

@spandana16 I may be able to help here, but needing some more details to help you out.

When you say "generate the output through a SQL" what are you needing to be accomplished? Are we, let's say, iterating through each JobID, running the query based on that particular Job ID, and then outputting as a CSV? Or, are you looking for the workflow to do something else?

[spandana16]

Yes, You're correct @rzdodson. I am attaching my half baked flow :)

[rzdodson]

@spandana16: the above workflow was not saved as a packaged workflow, so I cannot see the data you are working with.

However, I have created a dummy workflow that will help walk through the process I built out. What you are essentially wanting to do is create a nested batch/standard macro to process your SQL queries in to their respective CSVs.

First. I would recommend that for each row of data, you bring in the SQL query you want to run. For this use case, I have a sample one that I used, which can be found in the first formula tool.

Next you are going to want to create a batch macro that relies on the Job ID as its control parameter. What I am doing here is telling Alteryx to replace the Job ID each time we initiate a new batch.

Next, I am going to create a standard macro (last tool from above picture) that I will use to dynamically bring in the SQL query syntax that is associated with the Job ID. For this use case, I have an OLEDB connection to the Microsoft SQL Server environment I am operating in.  As detailed below, I will use that SQL Syntax field I created in the first step to push in that new query.

Then, I am going to need to tell Alteryx where to output that data. In the picture below, I have levered the Engine.WorkflowDirectory to dynamically bring in the directory where the workflow was saved. I will use that to build out the final output path that Alteryx will need. As a note, make sure you delete the OLEDB or OLE alias portion of the SQL string - we do not need that since it isn't an organic part of the SQL query.

Finally, I will alter the Take File/Table Name From Field settings so that I am using the Output Path field to output the data to where I need it to go. I'll ensure that the "Keep Field In Output" is deselected so it does not show up in the final output.

Hope that helps! :)