如何通过Shell脚本创建SQLite表并导入CSV？设置CSV模式遇语法问题

Got it, let's fix this CSV import issue with SQLite in a shell script—no Python/PHP dependencies required, just plain sqlite3 commands you can run non-interactively.

Full Working Script Example #

Here's a complete, reusable script that creates your database, enables CSV mode, and imports your CSV into a new table:

#!/bin/bash

# Set your file paths and table name here
DB_NAME="test.db"
CSV_FILE="your_data.csv"
TABLE_NAME="your_table"

# Run SQLite commands without entering interactive mode
sqlite3 "$DB_NAME" <<EOF
-- Switch SQLite to CSV mode for proper file parsing
.mode csv
-- Import the CSV into the target table (SQLite auto-creates columns from CSV headers)
.import "$CSV_FILE" $TABLE_NAME
-- Optional: Quick check to confirm the import worked
SELECT COUNT(*) FROM $TABLE_NAME;
EOF

Key Syntax Breakdown #

• .mode csv: This is the command you were missing! It tells SQLite to handle input/output as CSV format, which is mandatory for the .import command to read your file correctly.
• .import "$CSV_FILE" $TABLE_NAME: This reads your CSV and creates the table (if it doesn't exist) using the first row of the CSV as column names. If your CSV doesn't have headers, add .headers off right before .import—SQLite will then name columns col1, col2, etc.
• Non-interactive wrapper: The <<EOF syntax lets you pass multiple commands to sqlite3 in one go, so you don't have to manually type into the SQLite prompt.

Troubleshooting Common Issues #

• "No such table" error: Double-check your CSV file path (use absolute paths if you're unsure) and table name spelling.
• Misaligned columns: Ensure your CSV uses commas as separators (no extra spaces around commas). If you use a different delimiter (like semicolons), add .separator ";" right after .mode csv.
• Want to define column types manually?: If you don't want SQLite to auto-detect columns, create the table first before importing:

  CREATE TABLE $TABLE_NAME (
      id INTEGER PRIMARY KEY,
      username TEXT,
      signup_date DATE
  );
  .import "$CSV_FILE" $TABLE_NAME
  

That's all you need! This script will create your database, set up CSV mode, and import your data all in a non-interactive shell session.

内容的提问来源于stack exchange，提问作者d-b