CSV cleaner

• All my tests pass
• Output contains “3 rows kept, 3 dropped”
• No uncaught errors

import { INPUT } from './input';
import { cleanRows, toCsv } from './clean';

const { cols, clean, dropped } = cleanRows(INPUT);
console.log(toCsv(cols, clean));
for (const d of dropped) console.log(`dropped line ${d.line}: ${d.reason}`);
console.log(`${clean.length} rows kept, ${dropped.length} dropped`);

// Paste your CSV between the backticks (first line: the column names).
export const INPUT = `sku,name,price,qty
MM-00012, Organic apples ,1.20,4
MM-00013,Value bananas,"0,85",10
,Mystery item,2.00,1
MM-00012,Organic apples,1.20,4
MM-00044,Family rice 5kg,12.50,two
mm-00051,Daily milk,1.10,6
`;

// CSV cleaner: read a CSV, trim and fix values, drop broken rows with a reason, write clean CSV.

/** Splits CSV text into rows of fields (quoted fields may hold commas and doubled quotes). */
export function parseCsv(text) {
  const rows = [];
  let row = [], field = '', quoted = false;
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (quoted) {
      if (c === '"' && text[i + 1] === '"') { field += '"'; i++; }
      else if (c === '"') quoted = false;
      else field += c;
    } else if (c === '"') quoted = true;
    else if (c === ',') { row.push(field); field = ''; }
    else if (c === '\n') { row.push(field); rows.push(row); row = []; field = ''; }
    else if (c !== '\r') field += c;
  }
  if (field || row.length) { row.push(field); rows.push(row); }
  return rows.filter((r) => r.some((f) => f.trim()));
}

/** Clean rows as objects, and every dropped row with its line number and the reason. */
export function cleanRows(text) {
  const [header, ...rows] = parseCsv(text);
  const cols = header.map((h) => h.trim().toLowerCase());
  const seen = new Set();
  const clean = [], dropped = [];
  rows.forEach((r, i) => {
    const line = i + 2;
    const rec = Object.fromEntries(cols.map((c, j) => [c, (r[j] || '').trim()]));
    rec.sku = rec.sku.toUpperCase();
    const price = Number(rec.price.replace(',', '.'));
    const qty = Number(rec.qty);
    if (!rec.sku) return dropped.push({ line, reason: 'no SKU' });
    if (!Number.isFinite(price) || price < 0) return dropped.push({ line, reason: `price "${rec.price}" is not a number` });
    if (!Number.isInteger(qty) || qty < 0) return dropped.push({ line, reason: `qty "${rec.qty}" is not a whole number` });
    const key = JSON.stringify([rec.sku, price, qty]);
    if (seen.has(key)) return dropped.push({ line, reason: 'duplicate of an earlier row' });
    seen.add(key);
    clean.push({ ...rec, price: price.toFixed(2), qty: String(qty) });
  });
  return { cols, clean, dropped };
}

const cell = (v) => (/[",\n]/.test(v) ? `"${v.replace(/"/g, '""')}"` : v);
export function toCsv(cols, rows) {
  return [cols.join(','), ...rows.map((r) => cols.map((c) => cell(String(r[c] ?? ''))).join(','))].join('\n') + '\n';
}

import { parseCsv, cleanRows, toCsv } from './clean';

test('parseCsv keeps commas inside quotes', () => {
  expect(parseCsv('a,b\n"1,5",x\n')).toEqual([['a', 'b'], ['1,5', 'x']]);
});
test('cleanRows trims, upper-cases SKUs and reads 0,85 as 0.85', () => {
  const { clean } = cleanRows('sku,price,qty\n mm-1 ,"0,85",3\n');
  expect(clean).toEqual([{ sku: 'MM-1', price: '0.85', qty: '3' }]);
});
test('cleanRows drops broken and duplicate rows with a reason', () => {
  const { dropped } = cleanRows('sku,price,qty\nA,1,1\n,1,1\nB,x,1\nA,1,1\n');
  expect(dropped.map((d) => d.reason)).toEqual(['no SKU', 'price "x" is not a number', 'duplicate of an earlier row']);
});
test('toCsv quotes fields that need it', () => {
  expect(toCsv(['name'], [{ name: 'Rice, 5kg' }])).toBe('name\n"Rice, 5kg"\n');
});