Quick Script: Convert a CSV file to a SQLite database file
Updated on October 4, 2025 to detect INTEGER/REAL/TEXT column types.
Here’s a quick bash script to convert a .csv file into a .sqlite file so you can query your CSV data in tools like TablePlus.
The file size is only slightly larger than the original CSV, and you can share it (it’s just a file!).
Just create the file csv-to-sqlite somewhere in your PATH and chmod +x csv-to-sqlite to make it executable.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 |
#!/bin/bash # csv-to-sqlite - Convert CSV file to SQLite database with type detection # Usage: csv-to-sqlite [options] <csvfile> set -e # Default values table_name="main" show_help=false # Function to show usage show_usage() { cat << EOF Usage: $0 [OPTIONS] <csvfile> Convert a CSV file to an SQLite database with automatic type detection. Arguments: csvfile Path to the CSV file to import Options: -t, --table-name NAME Name for the table (default: main) -h, --help Show this help message Examples: $0 ~/mycsvfile.csv $0 --table-name employees ~/data/employees.csv $0 -t products ~/products.csv Output: Creates a SQLite database with the same name as the CSV file but with .sqlite extension in the same directory as the input file. Column types are automatically detected: - INTEGER for whole numbers - REAL for decimal numbers - TEXT for everything else EOF } # Parse command line arguments while [[ $# -gt 0 ]]; do case $1 in -t|--table-name) table_name="$2" shift 2 ;; -h|--help) show_usage exit 0 ;; -*) echo "Error: Unknown option $1" >&2 show_usage exit 1 ;; *) # This should be the CSV file if [[ -z "${csv_file:-}" ]]; then csv_file="$1" else echo "Error: Multiple CSV files specified" >&2 show_usage exit 1 fi shift ;; esac done # Check if CSV file was provided if [[ -z "${csv_file:-}" ]]; then echo "Error: CSV file not specified" >&2 show_usage exit 1 fi # Check if CSV file exists if [[ ! -f "$csv_file" ]]; then echo "Error: File '$csv_file' not found" >&2 exit 1 fi # Expand tilde in path csv_file=$(eval echo "$csv_file") # Generate SQLite database filename # Remove extension and add .sqlite base_db_file="${csv_file%.*}.sqlite" db_file="$base_db_file" # If file exists, add timestamp suffix if [[ -f "$db_file" ]]; then timestamp=$(date +"%Y%m%d%H%M%S") # Extract directory, basename without extension, and add timestamp db_dir=$(dirname "$base_db_file") db_name=$(basename "$base_db_file" .sqlite) db_file="${db_dir}/${db_name}_${timestamp}.sqlite" echo "Warning: '$base_db_file' already exists, creating '$db_file' instead" fi # Validate table name (basic check for SQL injection prevention) if [[ ! "$table_name" =~ ^[a-zA-Z_][a-zA-Z0-9_]*$ ]]; then echo "Error: Invalid table name '$table_name'. Table names must start with a letter or underscore and contain only letters, numbers, and underscores." >&2 exit 1 fi echo "Converting '$csv_file' to SQLite database '$db_file'" echo "Table name: $table_name" # Detect column types using awk # This analyzes the first 1000 rows to determine INTEGER, REAL, or TEXT types create_table_sql=$(awk -F',' ' BEGIN { SAMPLE_SIZE = 1000 } # Process header row NR == 1 { for (i = 1; i <= NF; i++) { # Remove quotes and whitespace from header gsub(/^[ \t"]+|[ \t"]+$/, "", $i) # Use column number if header is empty if ($i == "" || length($i) == 0) { headers[i] = "col" i } else { headers[i] = $i } # Initialize all columns as INTEGER types[i] = "INTEGER" } next } # Process data rows (sample only) NR <= SAMPLE_SIZE + 1 { for (i = 1; i <= NF; i++) { value = $i # Remove quotes and whitespace gsub(/^[ \t"]+|[ \t"]+$/, "", value) # Skip empty values if (value == "") continue # If already TEXT, skip further checks if (types[i] == "TEXT") continue # Check if value is an integer if (types[i] == "INTEGER") { if (value !~ /^-?[0-9]+$/) { # Not an integer, check if its a real number if (value ~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) { types[i] = "REAL" } else { types[i] = "TEXT" } } } # Check if value is a real number else if (types[i] == "REAL") { if (value !~ /^-?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?$/) { types[i] = "TEXT" } } } } END { # Generate CREATE TABLE statement print "CREATE TABLE IF NOT EXISTS temp_table (" for (i = 1; i <= length(headers); i++) { # Escape double quotes in column names safe_name = headers[i] gsub(/"/, "\"\"", safe_name) printf " \"%s\" %s", safe_name, types[i] if (i < length(headers)) { print "," } else { print "" } } print ");" } ' "$csv_file") # Import CSV to SQLite with proper schema sqlite3 "$db_file" << EOF -- Create temporary table with detected types $create_table_sql -- Import CSV data .mode csv .import "$csv_file" temp_table -- Create final table with correct name CREATE TABLE "$table_name" AS SELECT * FROM temp_table; -- Drop temporary table DROP TABLE temp_table; EOF echo "Successfully created SQLite database: $db_file" echo "Table '$table_name' contains $(sqlite3 "$db_file" "SELECT COUNT(*) FROM $table_name;") rows" echo "" # Show schema echo "Table schema:" sqlite3 "$db_file" "PRAGMA table_info($table_name);" | awk -F'|' '{printf " %-20s %s\n", $2, $3}' echo "" echo "You can now open this SQLite database in applications like TablePlus for convenient data viewing and manipulation." |
As a side note, you can use something like dsq to query a csv file. I’ve stopped using that for this particular task.