Tabul - How to load a CSV file into a SQL table

About

With tabulify, you can load any resource into a SQL table.

You can load

For the purpose of this howto, we will load a CSV file with the upsert operation.

Steps

Prerequisites

You should have Tabulify installed on your computer. Learning Tabulify - Step 1 - Installation

The CSV file to load

We will load the characters.csv file of the HowTo connection. The file can be seen with the head command. 

tabul data head characters.csv@howto

The first 10 rows of the data resource (characters.csv@howto):
last_name           first_name   birth_date   wikipedia_page
-----------------   ----------   ----------   --------------------------------------------------------------
Schwarzenegger      Arnold       1947-07-30   https://en.wikipedia.org/wiki/Arnold_Schwarzenegger
Norman              Don          1935-12-25   https://en.wikipedia.org/wiki/Don_Norman
Harbison Carnagey   Dale         1888-11-24   https://en.wikipedia.org/wiki/Dale_Carnegie
Kahneman            Daniel       1934-03-05   https://en.wikipedia.org/wiki/Daniel_Kahneman
Pittman             Bob          1953-12-28   https://en.wikipedia.org/wiki/Robert_Pittman_(media_executive)

If you want to change a CSV attributes, use the a attribute

Example with:

  • a text file extension,
  • another separator
  • and no header
tabul data head -a "media-type=csv" -a 'delimiterCharacter=;' -a 'headerRowId=0' characters.txt@howto

Loading the CSV file with the upsert command

We can load the above data using the upsert transfer command and the sqlite built-in database 

tabul data upsert characters.csv@howto @sqlite

Transfer results
input                  target              latency   record_count   error_code   error_message
--------------------   -----------------   -------   ------------   ----------   -------------
characters.csv@howto   characters@sqlite   0.55s                5

Check the data structure

We can check with the structure command that the process has created a table with the same structure. 

tabul data describe characters@sqlite

Structure of the resource characters@sqlite
position   name             type                precision    scale   primary_key   nullable   autoincrement   comment
--------   --------------   -----------------   ----------   -----   -----------   --------   -------------   -------
1          last_name        character varying   2147483647   0                     x
2          first_name       character varying   2147483647   0                     x
3          birth_date       character varying   2147483647   0                     x
4          wikipedia_page   character varying   2147483647   0                     x

We can check with the print command that the same data was loaded 

tabul data print characters@sqlite

characters@sqlite
last_name           first_name   birth_date   wikipedia_page
-----------------   ----------   ----------   --------------------------------------------------------------
Schwarzenegger      Arnold       1947-07-30   https://en.wikipedia.org/wiki/Arnold_Schwarzenegger
Norman              Don          1935-12-25   https://en.wikipedia.org/wiki/Don_Norman
Harbison Carnagey   Dale         1888-11-24   https://en.wikipedia.org/wiki/Dale_Carnegie
Kahneman            Daniel       1934-03-05   https://en.wikipedia.org/wiki/Daniel_Kahneman
Pittman             Bob          1953-12-28   https://en.wikipedia.org/wiki/Robert_Pittman_(media_executive)
Task Runner