How To Dynamically Generate Files

Reading time: 3 minutes

Generating files for data extracts, reporting and even data migration is a crucial part of any back-end team. A typical data team will have to generate many files to be sent to various clients or consumers inside or outside of the organisation. As with all things, the question is how can the generation of those files be made simpler, and can it be automated to such a degree that files are dynamically generated?  

The short answer is yes, it can be done dynamically.

A slightly more nuanced answer is that it truly depends on the file that needs to be generated, where the data comes from and what file we need to generate. For example, we want to generate a CSV file from any table passed in. This is completely doable. In Linx, we can create a process that will: 

  1. Receive the table name, file name and database connection string as input parameters 
  2. Create a query that will select the headers 
  3. Create a file with the headers 
  4. Create a query to select the fields in the table separated by a comma 
  5. Write these rows to a file 

The below process showcases how such a process will look in Linx.  

Process to dynamically generate a file

Here is an example solution. (You can copy this function into your own solution.)

Note: This solution will specifically work on Microsoft SQL Server databases as it requires the CONCAT function and that specific way to retrieve table headers. However, you can use it as a template and alter the queries.  

The most important part of this process is to dynamically create the SQL statement that will create an output that is expected to be written into the CSV file. When working with MS SQL you can do this by using CONCAT, and then concatenate each field with a comma or semicolon in between. The below screenshot shows how this can be done: 
Dynamically alter a SQL script to retreive custom data

With this logic, you can essentially now dynamically create queries to be executed. This has some great potential once you realise that you are not limited to hardcoded SQL statements. Create what you need on the fly. 

This functionality can be expanded by passing in views instead of table names. Meaning you can craft highly specific data sets. That said, this process is perfect for dynamically creating files for data extracts.  

You can now use this process to generate files on a schedule, generate files based off API calls (you can even return the file in the API response body if you are feeling up for it), or you can create these files and send them to a specific location.  

Sign up to our Newsletter