Batch Processing

[Push2025]

Objective:

Read a CSV file which contains 2 columns:

• JIRASQL [to save the file as]
• FINALQUERY – SQL Syntax

Our goal is to run 46 SQL Queries and save each file with the name listed in JIRASQL.

Main Workflow:

Data Input Tool:  Connection to CSV File

2 lines feedling to Macro Tool, line 1 t Question Mark and Line 2 to other node.  Outnode has nothing.

Macro Tool: 

GroupBy Tab:  

Control GroupBy Field:  JIRASQL,

Input2 GroupBy Field:  JIRASQL

Questions Tab:

                                                Choose Field: JIRASQL:  JIRASQL

                                                Choose Field: FINALQUERY:  FINALQUERY

                                                Choose Field Inject FINALQUERY:  FINALQUERY

Batch Macro:

                Path:  Fully path of batch macro file.

                Batch Macro Button is selected

Engine: IterationNumber: 0 is selected.

Macro Input Tool

                File Input:  Full path of the file with JIRASAL and FINALQUERY

                Show Field Map box is checked

                Optional income connection is checked.

                2 lines coming out of Macro Input Tool.

Line 1 connected to SELECT which retains FINALQUERY and feeds into DYNAMIC Input Tool.

Line 2 is connected to SELECT Tool which retains the JIRASQL then to SAMPLE to ensure 1 record, then to RECORD ID to add a column ‘rowid’ to JOIN with output from Dynamic Input Tool.

Configuration of DYNAMIC Input Tool

Control Parameter is connected to Action Tool

ACTION Tool:

                                Select an action type:  Update Value

                                Value or Attribute to update:

                                                Dynamic Input – Modification – Modify – ReplaceText-value = [Query in SQL Editor]

                                Replace a specific string

                                                [query listed in SQL Editor]  

                DYNAMIC Input Tool:

                                Edit: Connected to SQL Server with a valid query in SQL Editor

                                Modify a Specific String:  Replace a Specific String:  [Query in SQL Editor]

                                Output is connected to JOIN Tool.

                JOIN Tool:

                                Connections from DYNAMIC Input & JIRASQL

                                Output is connected to Data Output Tool.

                Data Output Tool:

                                Write to File or Database:

                                                Full path as ‘Dummy_File.csv’

                                Take File/ Table Name:

                                                File Containing File Name or Part of the File Name: JIRA SQL

Summary:  When we have a 1 record to read, it gives valid results.  However, when we expand to more record, loops runs that many times but same query is run again and again, and results are appending to same file.

[binuacs]

@Push2025 , I don't think you need a macro for batch processing because the dynamic input tool can process the request as batches. Here is the sample workflow attached, which connects to the access database and processes the given SQL statement and writes into the same file but different sheets.

[Push2025]

Thank you so much for this splendid solution.  Just one question.  We need to  run this query on the SQL Server.  Am I missing something?  I don't see the connection to SQL Server.  Perhaps I wasn't clear on my original request.  Thank again for your help.

[binuacs]

@Push2025 I don’t have SQL Server installed, so I used an Access database for the sample workflow. I am sure it is pretty much similar.  @apathetichell would you advise on the SQL server connection question? 

you can also go through the below post

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Problem-With-Dynamic-Input-For...

[Push2025]

This works perfectly for one file.  However when I list multiple files, it saves under the right name query are different, but each run has same number of rows are the first one.  

3rd file never gets generated

[binuacs]

@Push2025 @What is the outfile configuration? Also is there any warning message or error messages showing in the result window?

[Push2025]

there are 3 filed to be read:  

Configuration of Dynamic Tool

Union Tool:

Output Tool:

Output:

First file is created correctly.  2nd file has the correct name but double the same data as in first file.

[Push2025]

Here is the log: 

Thank you so much for your help.

[apathetichell]

Your error messages say that that values you are sending into your output data tool are not paths. I see a lot of ending ) --- those cannot be there. You need to clean those up.

[Push2025]

I see what the issue is:

These files have different number of fields, when that happens, it skips like this message say:

That's why only 2 file which have same structed were created.