3.2. Create a SQLite database from CSV/Excel/JSON/SQLite/etc. files

sqlitebiter file is a subcommand to convert tabular data file(s) to a SQLite database file.

3.2.1. Examples

Using wildcard to convert multiple files:

Example:
$ ls
sample_data.csv  sample_data.xlsx  sample_data_multi.json  sample_data_single.json
$ sqlitebiter file * -o sample.sqlite
[INFO] sqlitebiter file: convert 'sample_data.csv' to 'sample_data' table
[INFO] sqlitebiter file: convert 'sample_data.xlsx' to 'samplesheet1' table
[INFO] sqlitebiter file: convert 'sample_data.xlsx' to 'samplesheet3' table
[INFO] sqlitebiter file: convert 'sample_data_multi.json' to 'table_b' table
[INFO] sqlitebiter file: convert 'sample_data_multi.json' to 'table_a' table
[INFO] sqlitebiter file: convert 'sample_data_single.json' to 'sample_data_single' table
Output:
$ sqlite3 sample.sqlite
sqlite> .schema
CREATE TABLE 'sample_data' ("attr_a" INTEGER, "attr_b" REAL, "attr_c" TEXT);
CREATE TABLE 'samplesheet1' (a INTEGER, b REAL, c TEXT);
CREATE TABLE 'samplesheet3' (aa INTEGER, ab TEXT, ac TEXT);
CREATE TABLE 'table_b' (a INTEGER, b REAL);
CREATE TABLE 'table_a' ("attr_a" INTEGER, "attr_b" REAL, "attr_c" TEXT);
CREATE TABLE 'sample_data_single' ("attr_a" INTEGER, "attr_b" REAL, "attr_c" TEXT);

Designate multiple file path to convert:

Example:
$ sqlitebiter file hoge.csv sample_excel.xlsx
[INFO] sqlitebiter file: convert 'hoge.csv' to 'hoge' table
[INFO] sqlitebiter file: convert 'sample_excel.xlsx' to 'sheet3' table
[INFO] sqlitebiter file: convert 'sample_excel.xlsx' to 'sheet1' table

Note

  • Available JSON Schema is limited. Acceptable format described in here
  • Wildcard characters cannot use in Windows environments

3.2.2. sqlitebiter file subcommand help

Usage: sqlitebiter file [OPTIONS] [FILES]...

  Convert tabular data within CSV/Excel/HTML/JSON/LTSV/Markdown/SQLite/TSV
  file(s) to a SQLite database file.

Options:
  -o, --output-path PATH  Output path of the SQLite database file. Defaults to
                          'out.sqlite'.
  --encoding ENCODING     Encoding to load files. Defaults to 'utf-8'.
  -h, --help              Show this message and exit.

3.2.3. Supported data formats

Following table shows that the supported data formats:

Available data formats
Format File Extension Remarks
CSV .csv  
Excel .xlsx/.xls Create table for each sheet in the Excel workbook.
HTML .html/.htm
Scrape tabular data from <table> tags in the HTML file.
And create table for each <table> tag data.
JSON .json  
LTSV .ltsv  
Markdown .md
Extract tabular data in the Markdown file.
And create a table for each <table> tabular data.
SQLite .sqlite/.sqlite3  
TSV .tsv  

3.2.4. Table naming convention

Table name automatically decided as follows for each format:

Format Table Name
CSV <filename>
Excel <Sheet name>
HTML
<title>_<key>. <title> replaced with a title tag.
<key> replaced with: (1) id attribute of the table tag.
(2) unique string if id attribute not present in the table tag.
JSON  
LTSV <filename>
Markdown <filename>
TSV <filename>
  • Common behavior
    • <filename> replaced with filename of converting file (without extensions)
      • e.g. If the input file name is sample.csv, <filename> is sample

If a created table name already exists in the database, the behavior differs depending on the existing table (after this referred to as A) and create table (after this referred to as B) structure:

  1. A and B has the same table name and table structure
    • Append creating table data to the existing table data
  2. A and B has the same table name, but different table structure
    • sqlitebiter try to create unique table name for B by appending suffix id number