How to read, parse, and add a CSV file to the Xano database

This should be easier than it is...

For those of you here who just Googled the title tho this post, I’ll just dive into the meat of the post.

TL;DR

1. Make sure your database is set up correctly.

2. Get the CSV file as an input from the API call.

3. Use “Get File Resource Data” to prepare the CSV for decoding.

4. Take the data path of the result of step 2 and apply the csv_decode filter.

5. This will create an array of arrays, with the first array being your column titles and each subsequent array representing a row of the CSV.

6. Access the first (index == 0) array in your decoded_csv, which represents the column headings of your CSV and will be the keys to the objects you are going to build. Store as a new variable

7. Remove the first array (use the keys variable as the target for removal) from the decoded_csv, leaving behind only arrays of values.

8. Create a new array_of_values_to_add_to_db to which you will add the objects you are going to build.

9. Run a for loop through your array of values. In each iteration, use the create_object filter to build an object, with keys as the value when creating the variable and values as the values when using the create_object filter. Each resulting object represents one row in your database.

10. Add each object to the empty array_of_values_to_add_to_db you created previously.

11. Bulk add records to the database, using the array_of_values_to_add_to_db.

Tips

• I have some conditional logic here to only run this if the CSV input isn’t null.

• You probably want to make at least part of this a generic function and use it whenever you need to parse a CSV file.

• As of this writing, there is a bug (of which Xano is aware) that breaks the Get File Resource Data function if there are other inputs to the same API that are booleans. My hope is it will be resolved soon, but please be aware of this if you are reading this post soon after publication.

Mistakes to avoid

Figuring out the above took me a long time, unfortunately, as I had to pull together a bunch of videos, sparse documentation, and back-and-forth with the Xano tech support team.

I was really struggling with how to upload a CSV to my app, and considered using Bubble (which I am using only as my front end) to do some of the parsing and then send each record one-by-one to Xano via API call. Building a for loop in Bubble is very complex, so strongly recommend you don’t do that if you don’t have to. And in my case this would have put some of the back-end logic into Bubble, creating something of a Frankenstein’s monster of an app.

Additional resources

Here are some videos I found along that way that might be useful as well.

Convert an array to CSV in Xano

Transform data into CSV file and store in database