Calling/running a sql query present in excel from alteryx

[NehaYadav]

Issue is that there is sql query in excel which we need to call and run that in alteryx.

How we can do that because every time  sql query will be updated and we need to run that sql query.

[NehaYadav]

MORE INFORMATION:

Input- sql query in excel file

Issue: We need to call sql query and run it which is there in excel . How we can achieve this ?

Current logic:

Input data tool is fetching excel file and dynamic input tool is connected to the database where the file is present on the path and is option for modify sql query is opted.

SQL query : select table from table name is there as sql query/

Please help me in this i tried alot but no luck and stuck.

[JoeS]

Is there any reason why you wouldn't move the SQL Query to the input tool in Designer?

Failing that I believe the best way to run the query would be via a VB script.

More details can be found here: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Run-Command-Executing-Excel...

[NehaYadav]

 Thanks for ur reply.

I did not get your question.

We need to implement it using input and dynamic input tool 

[Christina_H]

@NehaYadav Joe was asking if there is a reason for the Excel step.  Alteryx can connect directly to the SQL server through an input tool and you could modify the query there.

If you need to do it this way, I haven't tried from Excel but I can update a dynamic input with SQL from a text input tool.  What is the problem you're having?

[NehaYadav]

Thnkyou Cristina for your reply.

 if there is a reason for the Excel step.  Alteryx can connect directly to the SQL server through an input tool and you could modify the query there.-Yes there is. Because source is sending files in excel and sql query is modified.

As per the existing workflow we are having input tool which is calling the excel file and dyamic input tool to modify and i tried it the way but its not working.

Could you please suggest how we can do this.

[Christina_H]

Hi @NehaYadav 

Without seeing what the issue is, it's hard to know what to suggest.  When the data is input from Excel, is it in a single row or split onto multiple rows?  I think you need it as a single string to work correctly, so you may need to modify your process to create one.  You might also need to double check your syntax.  You could try copying the SQL into an input tool to see if it runs correctly from there.

Can you share any error messages you're getting on your workflow?

[danilang]

Hi @NehaYadav 

Where is the SQL query stored in the Excel file?  Is it in one of the cells?  If so you can read in that sheet, extract the query and run it through a dynamic input.  Is the query built in an Excel macro?  If so, there's no current way to read the macro code from Alteryx.  There are ways to call the macro from an Alteryx workflow and then read the data from the Excel workbook.  Check out this page for more information 

In any case, can you please attach a sample of the workbook? 

Dan  

[apathetichell]

following up on @danilang - assuming the query is stored inside of a cell you will still need a connection field for dynamic in-db. If you have a existing connection - use dynamic in-db output to capture your connection string and append it to the excel cell with your query. Then use dynamic in-db to feed them in to your connection/query parameters.

If the query is currently in multiple rows - you will attached a summarize tool to concatenate it first - using /n - NOT , as your separator.

There are a variety of things that could go wrong throughout this process so the best things to do are:

1) test your connection string in Alteryx. - Is your ODBC working?

2) test your query language - port your excel query into Alteryx and past it in your SQL code block. Does it work? What changes do you need to make?

3) implement any changes you need to make so that when new query data arrives - it can be formatted for 2.

If you are just chopping and placing one non-dynamic query - skip everything after 2. Copy and paste in your query. Fix. Save.Done.

[NehaYadav]

Thanks for your reply.

My input is excel file containing sql query in row2 column 2

select Distinct * from tablename
where [column name] IN ( '2220','2390')
and BSTAT <> 'S' and
([column name] >='0003000000' AND [column name] <='0003999999'
or [column name] >='0008000000' AND [column name] <='0008999999'
or [column name] >='0032000000' AND [column name] <='0032999999'
or [column name] >='0010000000' AND [[column name] <='0010999999'
or [[column name] >='0037000000' AND [[column name] <='0037999999'
or [[column name] >='0043000000' AND [[column name] <='0043999999'
or [[column name] in ('0018303610','0018311938','0018324588','0018326842','0018328038','0018328779','0018328781','0018330700','0018668460','0018676803'))

input tool properties:

connection: file path  

table or QuERY: FILE NAME$

input tool output:

Variable        value

 div                    select distinct from table table name

dynamic input tool properties:

input data source: stg gallery database connection

modify sql query:

select * from tablename1

dynamic input tool Output

expecting suppose 100 records but we are getting 120 records .where clause is not working.

Can anyone please let me know how we can extract those records ?