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. Following table shows that the acceptable data format (format name and file extensions):

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.1. Example

Using wildcard to designate multiple files
$ 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 'sample_data_multi_table_b' table
[INFO] sqlitebiter file: convert 'sample_data_multi.json' to 'sample_data_multi_table_a' table
[INFO] sqlitebiter file: convert 'sample_data_single.json' to 'sample_data_single_json3' table
$ sqlite3 sample.sqlite
sqlite> .schema
CREATE TABLE 'sample_data' (attra INTEGER, attrb REAL, attrc TEXT);
CREATE TABLE 'samplesheet1' (a INTEGER, b REAL, c TEXT);
CREATE TABLE 'samplesheet3' (aa INTEGER, ab TEXT, ac TEXT);
CREATE TABLE 'sample_data_multi_table_b' (a INTEGER, b REAL);
CREATE TABLE 'sample_data_multi_table_a' (attra INTEGER, attrb REAL, attrc TEXT);
CREATE TABLE 'sample_data_single_json3' (attra INTEGER, attrb REAL, attrc TEXT);
Designate multiple file path
$ 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

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'.
  -h, --help              Show this message and exit.

3.2.3. 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