About
How to load a local CSV file into a SQL table with the data transfer command.
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 property, 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 transfer command
We can load the above data using the transfer command and the sqlite built-in database
tabul data transfer characters.csv@howto @sqlite
Transfer results
input target latency record_count error_code error_message
-------------------- ----------------- ------- ------------ ---------- -------------
characters.csv@howto characters@sqlite 0.62s 5
Check the data structure
We can check with the describe 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
Print the Sql table
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)