I’m new, so apologies if this has already been brought up. It would be convenient to have an option in the Datatables UI that would allow us to upload a CSV to the table. I know that there is a way to upload a file and then iterate through the cells to add the data to the table, but it would be great to do this without having to write a Python script.
Welcome, @sarah.hagen!
I think this is one of those “devil’s in the details” situations.
The options for how to handle that upload can have drastic consequences if wrong.
Safest and probably useful
Allow a empty table to populate from a csv
- Table must be empty
- All columns names in table must be found in csv
- Update discarded on any error. For example, if data in the csv can not be coerced into the table datatype.
This could be helpful for building new apps and would eliminate “Did I just delete half my database?” scares.
The broader can of worms
Mismatched column names
This is just a headache
Missing columns in csv
do you just fill with None, discard the row, abort everything?
Data conversion error
If the data in the csv can’t be forced into the table data type what do you do?
Append csv to table
This would happily create duplicates
Replace table with csv
Might as well be called “delete table”
Update table with csv
What do you use as the key/s for record matching? What do we do with records in the csv that are not matched in the table?
I’m sure the list continues…
Other Options
The best way right now is to write your own code for handling csv import. I would probably lean towards using Uplink for something like this. This would allow me to use things like Pandas for helping process the csv without having to burden my app’s server code, keeping it small and speedy.
Here is a quick tutorial on doing just that: CSV and Excel Import
1 Like
Having received CSV files from outside sources, I’d like to add, “and to do so with Murphy’s Law in mind.”
I have seen data and formatting errors that I would never have imagined possible. And if you haven’t imagined it, then your custom code probably doesn’t even look for it, much less detect it.
The tricky parts occur when a detected error can be resolved programmatically, but only for some of the incoming rows; the others requiring human judgement and/or editing. In many cases, these are detected only after Import is complete, and the downstream processing produces unwanted, nonsensical results, or crashes outright.
Garbage in, garbage out.
That said, given the potential difficulties, a successful import can be a really satisfying result, because it’s been earned.