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

sqlitebiter file is a subcommand to convert tabular data file(s) to a SQLite database file. Following table show the acceptable data information: Format name and available file extensions corresponding format.

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

3.2.1. Table naming

Table name will be generated automatically as follows for each format:

Format Table Name
CSV <filename>
Excel <Sheet name>
HTML
<title>_<key>. <title> will bee replaced with title tag.
<key> will be 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>
  • <filename> is replaced with filename of converting file (without extensions)
    • e.g. <filename> of the sample.csv is sample
  • <title>_<key>. <title> will bee replaced with title tag. <key> will be replaced with: (1) id attribute of the table tag. (2) unique string if id attribute not present in the table tag.

3.2.2. sqlitebiter file subcommand help

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

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

Options:
  -o, --output-path PATH  Output path of the SQLite database file
  -h, --help              Show this message and exit.

3.2.3. 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] convert 'sample_data.csv' to 'sample_data' table
[INFO] convert 'sample_data.xlsx' to 'samplesheet1' table
[INFO] convert 'sample_data.xlsx' to 'samplesheet3' table
[INFO] convert 'sample_data_multi.json' to 'sample_data_multi_table_b' table
[INFO] convert 'sample_data_multi.json' to 'sample_data_multi_table_a' table
[INFO] convert 'sample_data_single.json' to 'sample_data_single_json3' table
$ ls sample.sqlite
sample.sqlite
Designate multiple file path
$ sqlitebiter file hoge.csv sample_excel.xlsx
[INFO] convert 'hoge.csv' to 'hoge' table
[INFO] convert 'sample_excel.xlsx' to 'sheet3' table
[INFO] convert 'sample_excel.xlsx' to 'sheet1' table

Note

  • Available JSON Schema is limited, acceptable format is described in here
  • Wildcards cannot be used in Windows