sqlite – AgileAdam.com

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.

#!/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."

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

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

# Check if CSV file exists

if [[ ! -f "$csv_file" ]]; then

echo "Error: File '$csv_file' not found" >&2

exit 1

# 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"

# 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

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"

}

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