If you’re working with Excel, CSV, or other tabular data formats and need to integrate that data into a Python project, this guide is for you. This article and code will help you dynamically create local variables and SQLite tables based on your data. We’ll walk through a Python script that reads data from various formats, identifies its structure, and sets up corresponding SQLite tables to store your data effectively.

The best part — It’s automatic.

Step-by-Step Guide: Handling Excel, CSV, and More with Python

The Python script below is designed to take data from Excel, CSV, or similar formats and automatically generate SQLite tables to store the data. This process is dynamic, ensuring that the database schema matches the structure of your input data.

import sqlite3
import pandas as pd

def create_table(c, table_name, columns):
    columns_definition = ', '.join([f"{col} TEXT" for col in columns])
    create_statement = f"CREATE TABLE IF NOT EXISTS {table_name} ({columns_definition})"
    c.execute(create_statement)

def insert_into_db(table_name, df):
    conn = sqlite3.connect('example.db')
    c = conn.cursor()

    # Create table based on DataFrame columns
    create_table(c, table_name, df.columns)

    # Insert data into the table
    for _, row in df.iterrows():
        c.execute(f"INSERT INTO {table_name} ({', '.join(df.columns)}) VALUES ({', '.join(['?'] * len(df.columns))})", tuple(row))

    conn.commit()
    conn.close()
    print(f"Data from {table_name} has been inserted into the database.")

def process_excel(file_path, sheet_name=None):
    # Read Excel file
    df = pd.read_excel(file_path, sheet_name=sheet_name)
    table_name = sheet_name if sheet_name else 'excel_data'
    insert_into_db(table_name, df)

def process_csv(file_path):
    # Read CSV file
    df = pd.read_csv(file_path)
    table_name = file_path.split('/')[-1].replace('.csv', '')
    insert_into_db(table_name, df)

# Example usage
# Processing Excel file with multiple sheets
excel_file_path = 'data.xlsx'
sheet_names = pd.ExcelFile(excel_file_path).sheet_names

for sheet in sheet_names:
    process_excel(excel_file_path, sheet_name=sheet)

# Processing a CSV file
csv_file_path = 'data.csv'
process_csv(csv_file_path)

How It Works

1. Data Identification: The script uses pandas to read data from Excel and CSV files. For Excel files, you can specify a sheet name, or it will process the first sheet by default.
2. Dynamic Table Creation: The create_table function dynamically creates an SQLite table based on the columns of the DataFrame derived from the input data. Each column in the DataFrame corresponds to a column in the SQLite table.
3. Data Insertion: The script iterates through each row of the DataFrame and inserts the data into the corresponding SQLite table. This approach ensures that your database reflects the structure and content of your input data.
4. Handling Multiple Sheets: If you have an Excel file with multiple sheets, the script processes each sheet individually, creating a separate table for each.

Example Files

• Excel File: Suppose you have an Excel file named data.xlsx with sheets like “Customers”, “Orders”, and “Products”. Each sheet contains tabular data with different columns. The script will create separate SQLite tables named Customers, Orders, and Products and populate them with the respective data.
• CSV File: If you have a CSV file named data.csv with columns like “Name”, “Age”, and “Email”, the script will create an SQLite table named data and populate it with the contents of the CSV file.

This Python script provides a flexible, automated solution for integrating Excel, CSV, and other similar data formats into your SQLite database, making your data management tasks easier and more efficient.

Thank you for following along with this tutorial. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!

You can find the same tutorial on Medium.com.