Working with CSV, Excel, and SQL Files in Python

Why Handle CSV, Excel & SQL Data in Python?

Python makes it easy to read, write, and manipulate data from multiple sources like:

  • CSV files (Comma-Separated Values)
  • Excel files (.xls/.xlsx)
  • SQL databases (SQLite, MySQL, PostgreSQL)

This is essential for:

  • Data analysis
  • Reporting
  • Automation
  • ETL processes


1. Working with CSV Files in Python using pandas

Read CSV File

import pandas as pd

df = pd.read_csv('data.csv')
print(df.head())

Write to CSV File

df.to_csv('output.csv', index=False)

Select Specific Columns

df[['Name', 'Age']]

Filter Rows

df[df['Age'] > 25]


2. Working with Excel Files in Python

Read Excel File

df = pd.read_excel('data.xlsx', sheet_name='Sheet1')
print(df.head())

Write to Excel File

df.to_excel('output.xlsx', sheet_name='Results', index=False)

Read Multiple Sheets

excel_file = pd.ExcelFile('data.xlsx')
df1 = excel_file.parse('Sheet1')
df2 = excel_file.parse('Sheet2')


3. Working with SQL Databases in Python

Required Libraries

pip install sqlalchemy

Using SQLite for Local Database (no server required)

import sqlite3

# Connect to SQLite DB (or create one)
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create table
cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT, age INTEGER)''')

# Insert data
cursor.execute("INSERT INTO users (id, name, age) VALUES (1, 'Alice', 30)")
conn.commit()

# Fetch data
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print(rows)

# Close connection
conn.close()

Read SQL into Pandas

import pandas as pd
import sqlite3

conn = sqlite3.connect('example.db')
df = pd.read_sql_query("SELECT * FROM users", conn)
print(df)

Using SQLAlchemy (more scalable)

from sqlalchemy import create_engine

# Create SQLite engine
engine = create_engine('sqlite:///example.db')

# Read SQL data into pandas
df = pd.read_sql('SELECT * FROM users', engine)


Real-Life Use Case: Read CSV, Clean Data, Save to SQL

import pandas as pd
from sqlalchemy import create_engine

# Load CSV
df = pd.read_csv('employees.csv')

# Clean data
df.dropna(inplace=True)

# Save to SQL
engine = create_engine('sqlite:///employees.db')
df.to_sql('employees', con=engine, if_exists='replace', index=False)


Summary

Task Library Method
Read CSV pandas pd.read_csv()
Write CSV pandas to_csv()
Read Excel pandas pd.read_excel()
Write Excel pandas to_excel()
Connect to SQL (basic) sqlite3 sqlite3.connect()
Connect to SQL (advanced) SQLAlchemy create_engine()
Read SQL into DataFrame pandas pd.read_sql()