From Spreadsheets to Sanity: How I Automate Repetitive Data Tasks With Plain JavaScript

#javascript #productivity #data #csv

If you've ever found yourself copying and pasting the same data across Excel tabs for the umpteenth time in a week… trust me, I get it.
At some point, those spreadsheets that were supposed to make our lives easier start feeling more like an unpaid internship.

In this post, I’m eager to show you how I escaped the spreadsheet cycle and automated those repetitive data cleanup tasks with plain JavaScript—no frameworks or fancy libraries involved. It’s all about a bit of logic and some Node.js!

The Problem

Clients would send me data like this:

Huge CSV files with inconsistent column names (Full Name, full_name, name_full)
Mixed date formats (DD-MM-YYYY, YYYY/MM/DD)
Duplicates and empty rows
Repetitive filtering tasks (like removing inactive users)

I kept doing the same things in Excel until I decided: enough. Let’s script it.

Step 1: Read the File

For CSVs, I used csv-parse:
const fs = require('fs');
const parse = require('csv-parse');

fs.createReadStream('input.csv')
  .pipe(parse({ columns: true }))
  .on('data', (row) => {
    // handle row
  });

For Excel files:

const XLSX = require('xlsx');
const workbook = XLSX.readFile('data.xlsx');
const sheet = workbook.Sheets[workbook.SheetNames[0]];
const json = XLSX.utils.sheet_to_json(sheet);

Step 2: Clean the Data

Normalize headers:

function normalizeHeaders(row) {
  const normalized = {};
  for (let key in row) {
    const newKey = key.trim().toLowerCase().replace(/\s+/g, '_');
    normalized[newKey] = row[key];
  }
  return normalized;
}
data = data.map(normalizeHeaders);

Remove blank rows:

data = data.filter(row => Object.values(row).some(val => val !== ''));

Format dates:

function formatDate(dateStr) {
  const date = new Date(dateStr);
  return date.toISOString().split('T')[0]; // yyyy-mm-dd
}
data = data.map(row => ({
  ...row,
  joined_date: formatDate(row.joined_date)
}));

Step 3: Export the Cleaned Data

const { writeFileSync } = require('fs');
const { stringify } = require('csv-stringify/sync');

const output = stringify(data, { header: true });
writeFileSync('cleaned.csv', output);

Boom — reusable cleanup in under 5 seconds.

Lessons Learned

Plain JavaScript is enough for most data cleanup tasks.
csv-parse + csv-stringify make CSV parsing easy.
Once you write a cleanup script once, you never do it manually again.

TL;DR

Ditch repetitive Excel formulas.
Read CSV/Excel in JS.
Normalize headers, clean rows, convert formats.
Export back out — all automated.

Let me know if you've built similar automations or want to share some CSV horror stories

DEV Community