Csv to sqlite

· mategpt's blog

Read a csv a write it in a sqlite database #

To read a CSV file and write its data into an SQLite database, you can use a programming language like Python. Python provides various libraries to handle CSV files and SQLite databases easily. Here's a step-by-step guide to achieving this task:

  1. Install the required libraries: Make sure you have Python installed on your system. Additionally, you'll need the pandas library to handle the CSV file and the sqlite3 library to interact with the SQLite database. You can install these libraries using pip if you haven't already:
1pip install pandas sqlite3

  1. Create a CSV file: For the purpose of this example, let's assume you have a CSV file named data.csv with columns 'Column1', 'Column2', and 'Column3'.

  2. Python script to read CSV and write to SQLite database: Create a Python script (e.g., csv_to_sqlite.py) with the following content:

 1import pandas as pd
 2import sqlite3
 3
 4# Function to read CSV and create a SQLite database
 5def csv_to_sqlite(csv_file, db_file, table_name):
 6    # Read CSV into a Pandas DataFrame
 7    df = pd.read_csv(csv_file)
 8
 9    # Connect to SQLite database
10    conn = sqlite3.connect(db_file)
11    cursor = conn.cursor()
12
13    # Convert DataFrame to SQLite table
14    df.to_sql(table_name, conn, if_exists='replace', index=False)
15
16    # Close the database connection
17    conn.close()
18
19if __name__ == "__main__":
20    csv_file_path = "data.csv"  # Replace with the actual path to your CSV file
21    db_file_path = "data.db"    # Replace with the desired SQLite database file name
22    table_name = "my_table"     # Replace with the desired table name in the database
23
24    # Call the function to read CSV and write to SQLite
25    csv_to_sqlite(csv_file_path, db_file_path, table_name)
  1. Run the Python script: Place the script in the same directory as the data.csv file and execute the script:
1python csv_to_sqlite.py

This script will read the CSV file, create an SQLite database (if it doesn't exist), and write the data from the CSV file into a table named my_table (you can change the table name in the table_name variable).

After running the script, you will have a SQLite database file (data.db in this example) containing the data from the CSV file, ready to be queried and manipulated using SQL commands.

last updated: