What I Learned Cleaning 1 Million Rows of CSV Data Without Pandas

Cleaning a small CSV? Pandas is perfect.
Cleaning up a million rows on a limited machine or using a serverless function? That's when Pandas really struggles.

That’s exactly the problem I faced.

In this post, I’ll share:

• Why I avoided Pandas
• My Node.js pipeline with csv-parser
• How I handled common data issues: dates, phone numbers, missing fields
• What I’d do differently next time

Let’s dive in.

Why Not Pandas?

Pandas is fantastic, but it does have a downside: it loads the entire file into memory. If you're working with files larger than about 500MB, you might run into some issues.

• Memory errors
• Slow performance
• Crashes in limited environments (e.g., cloud functions, small servers)

In my case, I had:

• 1 million+ rows
• Dirty data from multiple sources
• A need to stream and clean data row by row

My Setup: Streaming CSV Processing in Node.js

Here’s the core pipeline using csv-parser and Node streams:

const fs = require('fs');
const csv = require('csv-parser');

let rowCount = 0;
let errorCount = 0;

fs.createReadStream('bigfile.csv')
  .pipe(csv())
  .on('data', (row) => {
    rowCount++;

    // Clean data
    row.email = cleanEmail(row.email);
    row.phone = cleanPhone(row.phone);
    row.date = parseDate(row.date);

    // Validate required fields
    if (!row.email || !row.date) {
      errorCount++;
      logError(row);
      return;
    }

    // Save row to DB, another file, or API...
  })
  .on('end', () => {
    console.log(`✅ Processed ${rowCount} rows`);
    console.log(`⚠️  Found ${errorCount} bad rows`);
  });

function cleanEmail(email) {
  return email?.trim().toLowerCase() || null;
}

function cleanPhone(phone) {
  const digits = phone?.replace(/\D/g, '');
  return digits?.length === 10 ? digits : null;
}

function parseDate(date) {
  const parsed = Date.parse(date);
  return isNaN(parsed) ? null : new Date(parsed).toISOString();
}

function logError(row) {
  fs.appendFileSync('errors.log', JSON.stringify(row) + '\n');
}

Common Data Issues I Ran Into (and How I Fixed Them)

• Inconsistent date formats (MM-DD-YYYY vs DD/MM/YYYY) → Used Date.parse() + fallback logic.
• Phone numbers in weird formats → Removed non-digits, validated length
• Missing fields → Set defaults or marked as null
• Extra columns → Stripped to schema fields
• Encoding problems → Saved CSVs as UTF-8

Pro Tips for Large CSV Cleaning

• Stream, don’t load → Avoid memory issues by processing row by row
• Validate early → Catch bad data before it pollutes your system
• Log errors → Keep a separate file of rejected rows for review
• Test on a small sample → Always test your logic before full-scale runs
• Handle edge cases → Empty cells, extra commas, inconsistent headers—these will happen!

What I’d Do Differently Next Time

• Use a schema definition (like JSON Schema or Zod) to validate and transform rows automatically
• Build a mapping layer for multi-source CSVs (e.g., different column names)
• Consider tools like DuckDB or Polars if I need more advanced queries

Final Thoughts

Handling big data files involves more than just coding; it’s about crafting durable pipelines that can navigate the complexities and messiness of real-world scenarios.

If you’re working with CSVs, remember:

• Validate early
• Clean thoughtfully
• Log everything

And when in doubt, stream it, don’t load it all at once.

Have you ever tackled the challenge of cleaning up a huge dataset? What tools or tips have you found to be the most helpful? I’d love to hear your thoughts!