Sqlite Or Flat Text File?

I process a lot of text/data that I exchange between Python, R, and sometimes Matlab. My go-to is the flat text file, but also use SQLite occasionally to store the data and access

Solution 1:

If all the languages support SQLite - use it. The power of SQL might not be useful to you right now, but it probably will be at some point, and it saves you having to rewrite things later when you decide you want to be able to query your data in more complicated ways.

SQLite will also probably be substantially faster if you only want to access certain bits of data in your datastore - since doing that with a flat-text file is challenging without reading the whole file in (though it's not impossible).

Solution 2:

A flat text file (e.g. in csv format) would be the most portable solution. Almost every program/library can work with this format: R and Python have good csv support and if your data set isn't too large you can even import the csv into Excel for smaller tasks.

However, text files are unhandily for larger data sets since you need to read them completely for almost all operations (depending on the structure of your data).

SQLite allows you to filter the data very easily (even without much SQL experties) and as you already mentioned can do some computation on its own (AVG, SUM, ...). Using the Firefox Plug-in SQLiteManager you can work with the DB on every computer without any installation/configuration trouble and thus easily manage your data (import/export, filter).

So I would recommend to use SQLite for larger data sets that needs a lot of filtering to extract the data that you need. For smaller data sets or if there is no need to select subsets of your data a flat (csv) text file should be fine.