How to create .sql as and output file

I have a excel table as below. I want to output the table below with "SQL Query" column as .sql. can you please help how to achieve this

Input

Code	SQL Query
123	SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

I am doing a output file as "123.sql"

when I open the "123.sql" file I am getting the column heading and the codes are inside a " " like below. How can I get "123.sql" file without the "SQL Query" and without the Quotes " "

SQL Query
"SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;"

Developer

Accepted answers

philipmannering

@Lumjing Try mirroring this configuration in the Output Data Tool...
Screenshot 2024-03-01 095824.png

Output SQL.yxmd

All comments

philipmannering

@Lumjing Try mirroring this configuration in the Output Data Tool...
Screenshot 2024-03-01 095824.png

Output SQL.yxmd

Lumjing

@PhilipMannering is there a way to align the query in the output it is coming as one line when I open the .sql file.

Thank you for the quick help.

jdminton

@Lumjing

switch the delimiter to \N in the input config

philipmannering

@Lumjing And if that doesn't work, use Split-to-Columns tool to split your text field into rows on a "\n". Example attached.

Output SQL.yxmd

Lumjing

@PhilipMannering thank you for the option. The "SQL Query" in my case is in line and when I use the \n it does not split them in rows. can you please suggest any alternatives

Lumjing

@jdminton I switch the delimiter to \n and the output is "SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;"

jdminton

@Lumjing I think what you are asking is for a way to format the code so that it is more readable. There's no default for this as each coder has his or her own preferences. You can try what I've included in the No New Line container in the workflow. I've added my first explanation as well to what Philip provided. There would be lots of parsing to get this how you want based on the SQL commands used within the code. You might consider using a different tool to help with this. I use Flyspeed SQL Query tool to help with formatting or developing complex SQL queries. I believe the free version will perform the formatting you are looking for. Please confirm if that is your question. Thanks..

Output SQL.yxmd

Lumjing

@jdminton Thank you for the detailed description. I will check and update. Can you please share me the .txt files

jdminton

Sorry, here is the packaged workflow with the text files.

Output SQL.yxzp

Lumjing

@jdminton @PhilipMannering thank you for all the help. I am having a "SQL Query" column with one line item like below "Customers.CustomerID;" is also in one line. With the help you provided I was able to generate the ".SQL" file without the " ", but when I open the ".SQL" file it is coming out as one line code. I want the SQL codes to be formatted in sql format. This we are able to achieve using a python "import urllib from sql_formatter.core import format_sql" package and using the code "lambda x: format_sql(x)". But I am not sure how I can implement this in Alteryx using the Python tool. do you have any suggestions?

Code	SQL Query
123	SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

apathetichell

Hey - You're talking about that python package as though it's a standardized format. It's not. It has 32 stars which isn't huge in Python-land and the latest version was released close to 3 years ago. I get that you like it - it's cool. but to get it to match exactly, I'd recommend building it yourself - or using that specific python package in the python tool.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703