Query CSV files with SQL from the command line easily, using SQLite

csvquery.sh

#!/bin/bash

set -eou pipefail

# Function to display usage instructions
display_usage() {
    echo "This script uses SQLite3 to load a CSV file into memory and then query it with SQL, printing the results to stdout."
    echo "The table name will be 'csv', so use that in your queries."
    echo ""
    echo "Usage: $0 -f|--file <file_path> [-q|--query <query>] [-p|--print-header] [-h|--header-fix]"
    echo "Arguments:"
    echo "   -f, --file <file_path>       Path to the CSV file"
    echo "   -q, --query <query>          Optional query to execute"
    echo "                                If no query is provided, the default query will be used which returns the top 10 results."
    echo "   -p, --print-header           Flag to print the CSV header before executing the query"
    echo "   -h, --header-fix             Flag to replace spaces in the first line of the CSV file with underscores and make it lowercase"
}

# Check if SQLite is installed
check_sqlite() {
    if ! command -v sqlite3 &> /dev/null; then
        echo "Warning: SQLite is not installed in your path. Please install SQLite to run this script."
        exit 1
    fi
}

# Default query if no query is provided via --query parameter
query="SELECT * FROM csv LIMIT 10;"

# Flag to print the CSV header
print_header=false

# Flag to fix header
fix_header=false

# Parse command-line arguments
while [[ $# -gt 0 ]]; do
    key="$1"
    case $key in
        -f|--file)
            file_path="$2"
            shift
            shift
            ;;
        -q|--query)
            query="$2"
            shift
            shift
            ;;
        -p|--print-header)
            print_header=true
            shift
            ;;
        -h|--header-fix)
            fix_header=true
            shift
            ;;
        *)
            echo "Error: Invalid argument - $1"
            display_usage
            exit 1
            ;;
    esac
done

# Check if SQLite is installed
check_sqlite

# Check if the file path argument is provided
if [[ -z "$file_path" ]]; then
    echo "Error: Missing required file path argument!"
    display_usage
    exit 1
fi

# Fix the header if the flag is set
if [[ "$fix_header" = true ]]; then
    # replace spaces with underscores
    sed -i '1s/ /_/g' "$file_path"
    # make lower case
    sed -i '1s/.*/\L&/' "$file_path"
fi

# Print the CSV header if the flag is set
if [[ "$print_header" = true ]]; then
    echo "Columns and Types:"
    echo ""
    sqlite3 :memory: -cmd '.mode csv' -cmd ".import $file_path csv" "SELECT name, type FROM pragma_table_info('csv');"
    echo ""
fi

# Execute the SQLite commands
sqlite3 :memory: -cmd '.mode csv' -cmd ".import $file_path csv" "$query"

Quick install to ~/.local/.bin/

curl -sSL "https://gist.github.com/withakay/f6c8e907fc06731f66b6fb9f900782d8/raw/csvquery.sh" -o ~/.local/bin/csvquery && chmod +x ~/.local/bin/csvquery