Convert CSV to SQLite

Converting a CSV file to an SQLite database involves reading the CSV data and inserting it into an SQLite table. Here’s a detailed step-by-step guide and example code in Python, which is a common and efficient way to do this:

Step 1: Prepare Your Environment

Make sure you have Python installed with the following modules:

• sqlite3 (built-in with Python)

• csv (built-in with Python)

• Optional: pandas (for easier CSV handling but not required)

Step 2: Create SQLite Database and Table

You need to create an SQLite database file and define a table schema matching your CSV columns.

Step 3: Read CSV and Insert into SQLite

Below is a sample Python script to convert CSV to SQLite:

python
CopyEdit
import sqlite3
import csv

# CSV file path
csv_file_path = 'data.csv'

# SQLite database file
sqlite_db = 'database.db'

# Table name
table_name = 'my_table'

# Connect to SQLite database (it will create if not exists)
conn = sqlite3.connect(sqlite_db)
cur = conn.cursor()

# Read the CSV header to create table columns dynamically
with open(csv_file_path, 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    headers = next(reader)  # Extract the header row
    
    # Create table with columns as TEXT
    columns = ', '.join([f'"{header}" TEXT' for header in headers])
    create_table_query = f'CREATE TABLE IF NOT EXISTS {table_name} ({columns});'
    cur.execute(create_table_query)
    
    # Prepare insert query placeholder
    placeholders = ', '.join(['?' for _ in headers])
    insert_query = f'INSERT INTO {table_name} ({", ".join(headers)}) VALUES ({placeholders})'

    # Insert rows
    for row in reader:
        cur.execute(insert_query, row)

# Commit and close
conn.commit()
conn.close()

Explanation:

• Opens the CSV file, reads the header to create columns.

• Creates an SQLite table with all columns as TEXT type.

• Inserts each CSV row into the table.

• Commits and closes the database.

Optional Improvements:

• Detect and set appropriate SQLite data types (INTEGER, REAL, TEXT).

• Use pandas for complex CSVs.

• Add error handling for duplicates or invalid data.

If you want me to provide a specific solution in another language or with more advanced features, just let me know!