I’m a big fan of Trello as a way to organize and collaborate. Recently I needed to export a Trello board to CSV. They have an export to JSON, but you have to buy a years worth of business class for the export to CSV. The business class is probably worth it for other reasons as well, but I thought I would see what it would take to convert JSON to CSV using Delphi 10 Seattle’s REST Client and FireDAC. I’m really pleased with what I came up with, and it didn’t take much effort.
I used the REST Client to connect to the JSON export on Trello. The REST Client is overkill for this since it is just a simple HTTP GET request, but the REST Response DataSet adapter is what I was after. This piped the JSON into a TFDMemTable.
Now the JSON from Trello contains a few collections (JSON Array’s of objects) so I used 3 different adapters (each with a different root node) and 3 different memory tables: Cards, Lists and Labels. There are other collections, but I wasn’t interested in them right now.
Once I have the JSON in a DataSet, I need to work with it. So I put down a TFDLocalSQL component and pointed it to the Mem Tables. Then attached a TMemo to the Query of a TFDQuery component, and I was able to run SQL querries against the Trello board. This made it easy to select exactly the data I wanted to export.
For the export I just used the FireDAC ETL functionality to pump the DataSet resulting from the Local SQL query to a text writer which saves a CSV file. I love the way FireDAC provides all this great high level functionality like Local SQL and the Text Writers. It makes so many tasks so easy to deal with.
There is still a lot of polish to do for this, but right now it does the job really well. I thought I would share it with anyone else who might be interested in exporting Trello to CSV. You could take a look at the source code and use it to convert most any REST/JSON datasource to CSV.
9 replies on “Export Trello to CSV”
Gr8 article, just what I need. But the card.desc column is limited to 255, but the JSON stream has full desc text. Can’t see how I change in the source? cheers
Did you check the underlying JSON? Maybe that is all that is exported.
HI Jim, no I checked, the full description is exported. I’m not experienced with FMX or Rest so this is a little trial & error for me. I changed the FieldDefs of CardTable at design time manually, but soon found they’re recreated automatically.
Yeah, I just verified this too. Odd. Something must be assuming a short string.
BTW, I pushed some other changes and bug fixes (like persistent settings).
Thanks Jim. I had to make some adjustments to the save as I’m not yet on DX10, but I will look at your changes THnx
I found the source of the 255 byte limit, but not sure how to fix it yet.
I just checked in a fix for the description length issue. Now has a length limit of 2147483647 characters.
Thanks Jim – appreciate it.
Hi Jim, thanks I made the change to Rest.Adapter.Pas thanks 🙂 Any chance of a follow up article on how to sort out OAuth to use this with private boards? Cheers