Create CSV Files with the API

Hey there! In this blog post, we'll explore how you can create downloadable CSV files using Xano APIs. Follow along as we dive into different scenarios and step-by-step guides to help you master this handy feature. Let's get started!

Basic Example: Creating a Simple CSV File

Let's kick things off with a basic example of creating a downloadable CSV file. Here's what you need to do:

1. Set the HTTP Headers: First, you'll need to set the appropriate HTTP headers for your API endpoint. These headers will instruct the browser to treat the response as a downloadable file. Add these two functions to your function stack:

• Set Content Disposition Header: This header specifies that the response should be treated as a file attachment and sets the file name. Use the `HTTP Header` utility function and set the value to `attachment; filename=example.csv`.
• Set Content Type Header: This header tells the browser the type of content being returned. Set the value to `text/csv` using the `HTTP Header` utility function.

1. Create the CSV Data: Next, you'll need to generate the actual CSV data. Use the `CSV Create` filter to create a CSV format data stream from a list of column headers and data rows.

• Define Headers: Create an array variable containing the column headers for your CSV file. For example: `headers = ["name", "age", "location", "email"]`.
• Define Rows: Create another array variable with the data rows. Each row should be an array with values corresponding to the column headers. For example: `rows = [["Andrew", 30, "Los Angeles", "andrew@email.com"]]`.
• Use the CSV Create Filter: Apply the `CSV Create` filter to your `headers` and `rows` variables to generate the CSV data stream. Assign the result to a new variable, let's call it `csv`.

1. Return the CSV Data: Finally, return the `csv` variable from your API endpoint. This will send the CSV data as the response, which the browser will treat as a downloadable file based on the headers set earlier.

That's it! You've now created a basic downloadable CSV file using Xano APIs. You can test it out by running the API endpoint or accessing it through a browser. The CSV file should automatically download or prompt you to save it.

Creating a CSV from a Table

What if you want to create a CSV file from data stored in one of your Xano database tables? Here's how you can do it:

1. Get the Table Headers: First, retrieve the column headers (keys) from your table. You can do this by accessing the first item in your table array and getting its keys using dot notation: `headers = table[0].keys()`.
2. Initialize an Empty Rows Array: Create an empty array variable to store the data rows: `rows = []`.
3. Loop Through the Table Records: Use a `foreach` loop to iterate through each record in your table.

• For each record, get the values using the `object.values()` function: `values = row.values()`.
• Append the values to the `rows` array using the `array.addToEnd()` function: `rows.addToEnd(values)`.

1. Create the CSV Data: Use the `CSV Create` filter with your `headers` and `rows` variables to generate the CSV data stream, just like in the basic example.
2. Return the CSV Data: Return the CSV data from your API endpoint to trigger the file download.

By following these steps, you can easily convert any table in your Xano database into a downloadable CSV file.

Creating a CSV from JSON Input

In some cases, you might receive data in JSON format and need to convert it into a CSV file. Here's how you can achieve that with Xano:

1. Define the JSON Input: Start by creating a JSON input type in your function stack. This will allow you to pass in JSON data to your API endpoint.
2. Get the Headers: Determine the column headers based on the structure of your JSON input data.
3. Initialize an Empty Rows Array: Create an empty array variable to store the data rows: `rows = []`.
4. Loop Through the JSON Data: Use a `foreach` loop to iterate through each item in your JSON input data.

• For each item, get the values using the `object.values()` function: `values = item.values()`.
• Append the values to the `rows` array using the `array.addToEnd()` function: `rows.addToEnd(values)`.

1. Create the CSV Data: Use the `CSV Create` filter with your `headers` and `rows` variables to generate the CSV data stream.
2. Return the CSV Data: Return the CSV data from your API endpoint to trigger the file download.

By following this approach, you can convert any JSON data into a downloadable CSV file using Xano APIs.

Making the File Name Dynamic

In some cases, you might want to make the file name dynamic based on certain conditions or input values. Here's how you can achieve that:

1. Use the `sprintf` Filter: The `sprintf` filter allows you to perform variable text substitution. It replaces placeholders in a given string with values from additional arguments.
2. Define the File Name Pattern: Create a string with placeholders for the dynamic parts of the file name. For example: `"report_%s.csv"`.
3. Pass the Dynamic Values: In the `sprintf` filter, provide the dynamic values as additional arguments after the file name pattern string.
4. Update the Content Disposition Header: Replace the static file name in the `Content Disposition` header with the output of the `sprintf` filter.

By following these steps, you can easily create dynamic file names for your downloadable CSV files based on any criteria or input values you need.

That's it! You now have a comprehensive guide on how to create downloadable CSV files using Xano APIs. Whether you're working with static data, database tables, or JSON inputs, you can leverage Xano's powerful filters and functions to generate CSV files on the fly.

Remember, these examples are just the tip of the iceberg. With Xano's no-code platform, you can build and deploy backend services for web and mobile applications without writing a single line of code. Explore the platform further to unlock even more possibilities and streamline your development process.

Happy coding (or should I say, happy no-coding)!