Transform data into CSV file and store in database

Greetings, dear reader! Today, we'll dive into the exciting world of Xano and learn how to create and store CSV files directly in your Xano database. This powerful feature allows you to seamlessly integrate data from various sources, transform it into a CSV format, and store it securely within your application's database. Let's get started!

Step 1: Understanding the Process

Before we begin, let's briefly overview the process we'll be following:

1. Obtain Data: We'll start by gathering the data you want to convert into a CSV file. This could be from an existing Xano database table or an external source.
2. Transform Data: Next, we'll use a custom function to transform the data into a CSV format, creating an array of column headers and rows.
3. Create CSV File: Once the data is in the desired format, we'll create a CSV file resource within Xano.
4. Store Metadata: Finally, we'll store the metadata of the created CSV file in a Xano database table, allowing you to access and manage it easily.

Step 2: Setting Up the Custom Function

The first step is to create a custom function that will handle the data transformation and CSV file creation. Here's how you can do it:

1. Navigate to the "Functions" section in your Xano instance.
2. Create a new function and give it a descriptive name (e.g., "CreateCSVFile").
3. Copy and paste the following code snippet into the function:

javascript
 // Get column headers from the first item in the input data
 const columns = Object.keys(data[0]);
 
 // Create an empty array to store the rows
 const rows = [];
 
 // Loop through the input data and extract the values for each row
 for (const item of data) {
  const singleRow = Object.values(item);
  rows.push(singleRow);
 }
 
 // Create the CSV data using the columns and rows
 const csvData = csv.create({ columns, rows });
 
 // Create a file resource with the CSV data
 const attachment = createFileResource(csvData, `%s.csv`);
 
 // Return the attachment metadata
 return attachment;
 

This function takes an array of objects as input (`data`) and performs the following tasks:

• Extracts the column headers from the first item in the input data.
• Creates an empty array to store the rows.
• Loops through the input data, extracts the values for each row, and appends them to the `rows` array.
• Creates the CSV data using the `csv.create` filter, passing in the columns and rows.
• Creates a file resource with the CSV data, allowing you to specify a custom filename using the `sprintf` filter.
• Returns the metadata of the created file attachment.

Step 3: Transforming and Storing Data

Now that we have our custom function set up, let's see how we can use it to transform and store data from an existing Xano database table.

1. Navigate to the "API" section and create a new endpoint (e.g., "CreateCSVFromMovies").
2. In the endpoint's function stack, start by querying the data you want to convert to CSV. For example, if you have a table named "Movies," you can use the following code:

javascript
 const movies = await x.data.movies.find().toArray();
 

1. Next, call the custom function we created earlier, passing in the data you want to transform:

javascript
 const csvAttachment = await createCSVFile(movies);
 

1. Create a new table (e.g., "CSVFiles") to store the metadata of the created CSV files.
2. In the same endpoint function stack, insert the CSV file metadata into the "CSVFiles" table:

javascript
 await x.data.csvFiles.create({ file: csvAttachment });
 

1. Save and run the endpoint.

After running the endpoint, you should see a new record in the "CSVFiles" table containing the metadata of the created CSV file.

Step 4: Accessing and Downloading the CSV File

To access and download the CSV file, you can use the metadata stored in the "CSVFiles" table.

1. Navigate to the "Data" section and open the "CSVFiles" table.
2. Click on the record containing the CSV file metadata you want to access.
3. In the record details view, you should see a field named "file" with a download icon next to it.
4. Click on the download icon to download the CSV file to your local machine.

Alternatively, you can use the provided URL to access the CSV file directly. The URL is constructed by combining your Xano domain and the `path` property from the file metadata. For example:

 https://your-xano-domain.xano.io/api/files/attachment_id
 

Replace `your-xano-domain` with your actual Xano domain, and `attachment_id` with the value of the `path` property from the file metadata.

Conclusion

Congratulations! You've successfully learned how to create and store CSV files directly in your Xano database. This powerful feature opens up a world of possibilities, allowing you to seamlessly integrate data from various sources, transform it into a convenient and widely-used format, and store it securely within your application.

Whether you're working on a personal project or building a professional application, Xano's no-code approach and robust features make it an excellent choice for developers and non-technical users alike. Happy coding (or no-coding)!