Processing Large CSV Files with Python: From Raw Data to Color-Coded Excel (Beginner Friendly)

Working with large datasets is something every developer eventually faces.

Recently, I had a practical requirement:

• A CSV file stored on a server
• Containing lakhs of records
• I needed to download it, process it, and generate an Excel file

But there was a twist:

The Excel output needed to visually represent progress using colors.

• 🔴 Not Started
• 🟡 In Progress
• 🟢 Completed

At first glance, this sounds straightforward. But once you factor in file size, memory constraints, and Excel formatting, things get interesting.

In this blog, I’ll walk you through how I solved this using Python — as a beginner — in a way that you can reuse as a plug-and-play module.

🤔 Why Python for This?

Before diving into the solution, let’s talk about why Python is such a great fit here.

✅ Simple and Readable

Python code feels almost like English. This makes it ideal if you’re still learning.

✅ Powerful Libraries

We’ll use:

• pandas → for reading and processing large CSV files
• openpyxl → for creating Excel files with formatting

These libraries remove a lot of complexity.

✅ Handles Large Data Efficiently

Python supports:

• Chunk-based file processing
• Streaming downloads
• Memory-efficient transformations

✅ Easily Reusable

You can turn this into:

• A script
• A scheduled job
• A backend utility

🧩 Problem Breakdown

We’ll solve this step-by-step:

1. Download CSV from server
2. Read large file (efficiently)
3. Process status column
4. Create Excel output
5. Apply color formatting
6. Optimize for large datasets (chunk processing)

📥 Step 1: Download the CSV File

import requests

def download_file(url, output_path):
    response = requests.get(url, stream=True)
    response.raise_for_status()
    with open(output_path, 'wb') as file:
        for chunk in response.iter_content(chunk_size=8192):
            file.write(chunk)
    print("File downloaded successfully!")

💡 Why this works well

• Uses streaming → avoids memory overload
• Works even for very large files

📊 Step 2: Read CSV (Basic Version)

import pandas as pd

def read_csv(file_path):
    df = pd.read_csv(file_path)
    return df

This works fine for moderate-sized files.

But for very large files, this approach can crash your system.

🚀 Step 3: Chunk-Based Processing (For Large Files)

This is where things get powerful.

Instead of loading everything into memory:

def read_csv_in_chunks(file_path, chunk_size=10000):
    return pd.read_csv(file_path, chunksize=chunk_size)

💡 What this does:

• Reads 10,000 rows at a time
• Keeps memory usage low
• Allows processing of files with millions of rows

🎨 Step 4: Status Coloring Logic

def get_status_color(status):
    status = str(status).strip().lower()

if status == "not started":
        return "FF0000"  # Red
    elif status == "in progress":
        return "FFFF00"  # Yellow
    elif status == "completed":
        return "00FF00"  # Green
    return "FFFFFF"

📄 Step 5: Writing Excel with Colors

from openpyxl import Workbook
from openpyxl.styles import PatternFill

def write_chunk_to_excel(ws, df):
    for row in df.itertuples(index=False):
        ws.append(row)
        status = getattr(row, "status", "")
        color = get_status_color(status)
        fill = PatternFill(start_color=color, end_color=color, fill_type="solid")
        for cell in ws[ws.max_row]:
            cell.fill = fill

🧠 Step 6: Full Chunk-Based Processing Pipeline

This is the real-world scalable solution.

def process_large_csv_to_excel(csv_path, excel_path):
    chunks = read_csv_in_chunks(csv_path, chunk_size=10000)

wb = Workbook()
    ws = wb.active
    ws.title = "Processed Data"
    header_written = False
    for chunk in chunks:
        # Normalize column names
        chunk.columns = [col.strip().lower() for col in chunk.columns]
        if not header_written:
            ws.append(list(chunk.columns))
            header_written = True
        # Clean status column
        if 'status' in chunk.columns:
            chunk['status'] = chunk['status'].astype(str)
        write_chunk_to_excel(ws, chunk)
    wb.save(excel_path)
    print("Excel file created successfully!")

🔌 Step 7: Plug-and-Play Script

def run_pipeline(url, csv_path, excel_path):
    download_file(url, csv_path)
    process_large_csv_to_excel(csv_path, excel_path)

# Example usage
run_pipeline(
    url="https://example.com/large_file.csv",
    csv_path="data.csv",
    excel_path="output.xlsx"
)

⚠️ Real-World Problems You Will Face

1. Memory Issues

If you try to load everything at once:

💥 Your script crashes

✅ Solution:

• Always use chunk processing for large files

2. Dirty Data

Statuses might look like:

• “Completed”
• “completed “
• “COMPLETE”

✅ Solution: We normalize using:

status = str(status).strip().lower()

3. Excel Performance

Applying styles row-by-row is slow for huge datasets.

✅ Tip:

• Accept slight delay for formatting
• Or only color specific columns instead of full rows

4. Missing Columns

If status column is missing:

❌ Script breaks

✅ Add validation:

if 'status' not in chunk.columns:
    raise Exception("Missing 'status' column")

🚀 Why This Approach Works

This solution:

• Handles lakhs to millions of records
• Avoids memory crashes
• Produces clean, readable Excel output
• Can be reused across projects

🧠 What I Learned as a Beginner

As someone still learning Python, this exercise taught me:

• Large problems become easy when broken into steps
• Python libraries save massive effort
• Real-world data is always messy
• Writing reusable code is more valuable than quick fixes

💡 One Key Takeaway

Don’t load large files — stream and process them in chunks.

This single idea will save you from most data-processing issues.

🚀 Final Thoughts

If you’re starting with Python and want practical experience:

Data processing is one of the best places to begin.

It’s:

• Useful
• In demand
• Immediately applicable

🙌 What You Can Do Next

You can extend this further by:

• Uploading output to cloud storage
• Scheduling the script (cron job)
• Adding logging
• Building a CLI tool