How to load a CSV file into a SQL table

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.

tabli data head 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

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

Example with:

  • a text file extension,
  • another separator
  • and no header
tabli data head -a "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

tabli data transfer characters.csv@howto @sqlite
Source                 Target              Latency (ms)   Row Count   Error   Message
--------------------   -----------------   ------------   ---------   -----   -------
characters.csv@howto   characters@sqlite   25             4

Check the data structure

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

tabli data struct characters@sqlite
Position   Column Name      Data Type   Primary Key   Not Null   Auto Increment   Description
--------   --------------   ---------   -----------   --------   --------------   -----------
1          Last Name        text                      x          false
2          First Name       text                      x          false
3          Birth Date       text                      x          false
4          Wikipedia Page   text                      x          false

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

tabli data print 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




Related Pages
Undraw Server
Data Operation - Transfer

transfer is a data operation that executes all this data resources operations: copy (Default) download, upload, load move, rename insert, append upsert update delete moveprocesstransfer...

Task Runner