When it comes to a data storage for your application, there are multiple options. Depends on your needs you may choose a JSON file or a proper SQLite database. Some times you may need to convert a JSON to SQLite DB and that is the topic of the current post.
First idea that you may get is to solve the problem in code. Well, we are developers and we are best in it after all. However, SQLite CLI is powerful enough to solve our issue or at least to solve it partly. SQLite CLI supports multiple data sources but not JSON. We will have to convert JSON to CSV and depending on the data structure it could be complex or an easy task to do. Here you could write some code or use an existing solutions (google it) in order to make the conversion happen.
The interesting part is the data import itself and it’s pitfalls. I had to replace a ‘,’ separator by “|” since the SQLite CLI ignored my quoted nested commas. Another thing to keep in mind that leading and trailing spaces are not ignored and they are a part of the value. You can define the table scheme before the import or alter it after the import, depends on your needs. For doing it before you just have to create a table manually, otherwise you will have to use the SQLite CLI or a SQLite browser to alter an auto-generated table.
Let’s take a look on a simple case where we will import a CSV file with a custom separator without defining the table scheme before the import.
CSV contents:
A|B|C1|2|34|5|6
sqlite3 dummy.dbSQLite version 3.19.3 2017-06-27 16:48:08Enter “.help” for usage hints.sqlite> .mode csvsqlite> .separator |sqlite> .import dummy.csv ABCsqlite> select * from ABC;1|2|34|5|6sqlite>
Hey Evgeny,
In the future, if you do this again, I recommend trying out the Flow framework.
They have a real interesting approach to the JSON conversion (utilizing their new file format called ‘generic data’). It’s at the very least worth checking out. I nerded out over it. There are no table definitions required and you can choose whatever delimiter you desire for your CSV export and export it directly to your database. The platform has all sorts of other wild automation / AI / machine learning features, I’ve now found, as well. None of it requires code.
Here’s the blog post I found out about it from: https://flow-analytics.com/blog/how-to-normalize-json-using-flow-analytics
LikeLike
Hello Kyle,
It looks interesting. On the video I can see that the client application is running on Windows, do you know if they support Mac as well?
Thanks for sharing.
LikeLike