CSVKIT and SQLITE

CSVKIT is a collection of command line tools that I find very useful when working with CSV files. It's also cross-platform since it can be easily installed via Python package manager, with:

   $ pip install csvkit

For instance, I can display my ToDo list from the command line with the following commands:

   $ csvcut.exe -C 1,2,6,9 todo.csv |csvlook.exe 

csvcut filters out four columns that are irrelevant and csvlook arranges the data in a smart-looking table.

    | priority | project      | description                       | due date   | start date |
    | -------- | ------------ | --------------------------------- | ---------- | ---------- |
    | M        | +wintermnote | Display todo list on wintermnote  | 2021-09-27 | 2021-09-24 |
    |          | +csvtodo     | Annotation after ar               |            |            |
    | L        | +wintermnote | Fix wintermnote fast search       |            |            |
    

Another possible application, it's checking the due-date for a particular task/subtask. The command csvgrep works like the Unix grep command, it searches a pattern inside the CSV file and returns the entire matched row. Note that at least one column has to be specified (the -c option).

SQLITE

From the command line I can create a database and a table that mirrors the content of my CSV file:

   # Create table and Load todo.csv into it
   # from https://gist.github.com/martijngastkemper/6d62245565a49081db6024f5f432fe63
   csvsql --db sqlite:///todo.db --table todo --insert todo.csv

A single SQLITE binary file can contain several tables in case I need to stack up more than one CSV data file. The following node script can retrieve the database and extract the CSV file on a different machine when the database is located on a web server (or has a valid url, Ngrok or similar can provide that for files in a local folder):

    #!/usr/bin/env node
    //filename: sync_todo.js
    //source https://stackoverflow.com/questions/11944932/how-to-download-a-file-with-node-js-without-using-third-party-libraries/26233209#26233209
    // updated http -> https
    // + sqlite3 and shelljs modules - npm install sqlite3 shelljs
    var https = require('https'),
        fs = require('fs'),
        sqlite3 = require('sqlite3'),
        shell = require('shelljs');
    

    const url = "https://mylocalurl/todo.db"
    //to use a test url uncomment the next row
    //const url = "https://tinyurl.com/yg3556no"
    
    var request = https.get(url, function(response) {
        if (response.statusCode === 200) {
            var file = fs.createWriteStream("todo.db");
            response.pipe(file);

        let db = new sqlite3.Database('./todo.db', (err) => {
          if (err) {
            console.error(err.message);
          }
          console.log('Unpacking.....');
          // get tables 
          db.serialize(function() {
      
              db.each("SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';", function(err, row) {
                  console.log('Table:' + row.tbl_name);
                  shell.exec("sqlite3 -header -csv todo.db \"select * from "+row.tbl_name+"\" >"+row.tbl_name+".csv")
              });
          });
        });
        }
        request.setTimeout(12000, function () {
            request.abort();
        });
    });