Programatically Write CSV Files from SQL Databases with WayScript

Introduction

CSV documents are vital for importing and exporting data due to the ease with which they are created and can be imported. Today, we’ll learn how to make the creation of these documents by using SQL data to write the file with the help of an HTTP endpoint. Essentially, we want to pass an SQL query parameter to the endpoint so it can perform a conditional select on the database. This may sound complicated, but the next few paragraphs will break it down further.

Building our Script

The HTTP Trigger module will be the starting point for this script. Once inserted, this provides a URL where a query parameter can be requested. To do this, click the “Add Query Parameter” underneath “Outputs” on the left side toolbar. Create a variable by adding a name, for example “sqlselection.” Whenever the provided URL is clicked it passes a value for the query parameter (“sqlselection”) to be used throughout the program, querying the SQL database.

Next, the SQL module is added below the HTTP Trigger. In the toolbar, select a database from any already connected or add an account to upload an unconnected database. Now it’s time to edit the SQL code. The images below are an example of the starting code and the table that is produced.

Add a WHERE statement to the code to pull information conditionally from the query parameter created earlier in the script (“sqlselection”). Doing this creates a value for the parameter when the HTTP endpoint is used.

Let’s run the current script. Activate the HTTP Trigger and copy the URL into a new tab. Attach the query parameter to the end of the URL and set it equal to “marketing,” for example. This leads to an error message due to the lack of any content, however when returning to the script it shows that the SQL table has now been queried using “marketing.”

Import the last 3 columns from this table.

Create a loop to add in the script underneath the SQL module if there is more than one output. Select each imported column to loop over in the left side toolbar. Once this is done the CSV module is added, so that the information can be written to this file. There are options to either add to or replace the file each time the URL is visited. Again, add the previously imported columns. The final step is to add a CSV read file just so that the user is able to see it, with your final script looking like this.

Using WayScript’s easy-to-navigate, drag and drop programming you have the ability to create dynamic CSV files like the one above. To see the step-by-step process of creating this script, watch our YouTube video below!

Conclusion

Questions about this script or anything else? Join our discord. We're always around to help. If you want to work the full script template, just find it here.