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.
Make sure your database is set up correctly.
Get the CSV file as an input from the API call.
Use “Get File Resource Data” to prepare the CSV for decoding.
datapath of the result of step 2 and apply the
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.
Access the first (index == 0) array in your
decoded_csv, which represents the column headings of your CSV and will be the
keysto the objects you are going to build. Store as a new variable
Remove the first array (use the
keysvariable as the target for removal) from the
decoded_csv, leaving behind only arrays of values.
Create a new
array_of_values_to_add_to_dbto which you will add the objects you are going to build.
Run a for loop through your array of values. In each iteration, use the create_object filter to build an object, with
keysas the value when creating the variable and
valuesas the values when using the create_object filter. Each resulting object represents one row in your database.
Add each object to the empty
array_of_values_to_add_to_dbyou created previously.
Bulk add records to the database, using the
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.
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
Thanks for reading Xanomorph! Subscribe for free to receive new posts.
@walter - Thanks for the article, much helpful....
Any idea what to do if input csv contains page breaks? (in csv_decode it behaves like it's new record in CSV. Even if the value has " enclosure)