CSV Fix Kit.html

<!doctype html>
<html lang="en"><head><meta charset="utf-8"><meta name="viewport" content="width=device-width,initial-scale=1"><meta http-equiv="Content-Security-Policy" content="default-src 'none'; script-src 'unsafe-inline'; style-src 'unsafe-inline'; img-src data:; connect-src 'none'; base-uri 'none'; form-action 'none'"><title>CSV Fix Kit — Offline</title><style>
:root{font:16px/1.6 system-ui,sans-serif;color:#14213a;background:#f3f6fc;color-scheme:light}*{box-sizing:border-box}body{margin:0}header,main,footer{max-width:1080px;margin:auto;padding:26px}header{display:flex;align-items:center;justify-content:space-between;border-bottom:1px solid #d7e0ef}header strong{letter-spacing:.05em}header span{font-size:14px;color:#456446}h1{font-size:32px;letter-spacing:-.035em;line-height:1.2;margin:10px 0}h2{font-size:20px;margin:0 0 16px}p{color:#53627a}.box{border:1px solid #d7e0ef;background:#fff;padding:24px;border-radius:9px;margin-top:24px}.step{font-size:13px;font-weight:600;color:#214bd0;margin-right:12px}label{display:block;margin:10px 0;font-size:15px}input,select,textarea,button{font:inherit}textarea{width:100%;height:178px;resize:vertical;padding:14px;border:1px solid #bdcbe2;border-radius:5px;font:14px/1.65 ui-monospace,monospace;tab-size:4}select{padding:8px 12px;border:1px solid #bdcbe2;background:#fff;border-radius:5px}button,.download{display:inline-block;padding:11px 16px;background:#214bd0;color:#fff;border:0;border-radius:5px;cursor:pointer;font-size:15px;text-decoration:none;font-weight:600}button.secondary{background:#eaf0ff;color:#214bd0}button:disabled{opacity:.45;cursor:not-allowed}button:focus-visible,a:focus-visible,input:focus-visible,select:focus-visible,textarea:focus-visible{outline:3px solid #d4a820;outline-offset:3px}.row{display:flex;align-items:center;gap:14px;flex-wrap:wrap}.row>label{margin:0}.columns{display:grid;grid-template-columns:1fr 1fr;gap:24px;margin-bottom:22px}fieldset{border:1px solid #d7e0ef;border-radius:5px;min-width:0;padding:14px 18px}legend{padding:0 8px;font-size:15px;font-weight:650}fieldset label{display:flex;align-items:flex-start;gap:10px;overflow-wrap:anywhere}input[type=checkbox]{width:18px;height:18px;flex-shrink:0;margin-top:3px;accent-color:#214bd0}small{font-size:13px;color:#53627a}.stats{padding:16px;background:#eaf0ff;white-space:pre-wrap;font-size:15px}.scroll{overflow:auto;max-height:440px;border:1px solid #d7e0ef;margin:16px 0}table{border-collapse:collapse;width:100%;font:13px/1.6 ui-monospace,monospace}th,td{padding:10px 12px;border-bottom:1px solid #d7e0ef;text-align:left;vertical-align:top;max-width:350px;overflow-wrap:anywhere;white-space:pre-wrap}th{background:#f0f4fc;position:sticky;top:0}pre{white-space:pre-wrap;overflow-wrap:anywhere;font-size:13px;padding:16px;background:#f5f7fc;max-height:330px;overflow:auto}#error{color:#9d2116;background:#fff0ee;border-left:3px solid #b42318;padding:12px 16px}#error:empty{display:none}[hidden]{display:none!important}footer{font-size:13px;color:#53627a}a{color:#214bd0}details summary{cursor:pointer}#file{max-width:100%;font-size:14px}.hint{border-left:3px solid #829bc7;padding-left:13px;font-size:14px}@media(max-width:620px){header,main,footer{padding:18px}.box{padding:18px}.columns{grid-template-columns:1fr;gap:16px}h1{font-size:28px}header span{font-size:12px}}
</style></head><body>
<header><strong>CSV FIX KIT</strong><span>OFFLINE · NO UPLOADS</span></header><main>
<h1>Clean your export. Keep control.</h1><p>Make only the changes you choose. Your original file stays unchanged.</p>
<div id="error" role="alert"></div>
<section class="box"><h2><span class="step">01</span>Choose your CSV</h2>
<label for="file">Open a UTF-8 CSV file (up to 5 MB)</label><input id="file" type="file" accept=".csv,.tsv,.txt,text/csv,text/tab-separated-values">
<label for="source">Or paste CSV text</label><textarea id="source" spellcheck="false" aria-describedby="csv-help"></textarea>
<p id="csv-help"><small>Header row required. Quoted commas and multiline fields are supported. Limits: 50,000 data rows and 256 columns.</small></p>
<div class="row"><label for="delimiter">Delimiter</label><select id="delimiter"><option value=",">Comma (,)</option><option value=";">Semicolon (;)</option><option value="tab">Tab</option></select><button id="read" type="button">Read columns</button><button id="example" class="secondary" type="button">Load example</button></div></section>
<section class="box" id="options" hidden><h2><span class="step">02</span>Choose explicit changes</h2><div class="columns"><fieldset><legend>Trim outer whitespace</legend><small>Only checked columns will change.</small><div id="trim-columns"></div></fieldset><fieldset><legend>Check for conflicting keys</legend><small>Check one or more identifier columns. Conflicts are flagged, never merged.</small><div id="key-columns"></div></fieldset></div>
<label class="row"><input id="dedupe" type="checkbox"> Remove exact duplicate rows after trimming</label><p class="hint">Identical rows can represent separate valid events. Leave this off unless duplicates should be removed.</p><button id="clean" type="button">Clean and review</button></section>
<section class="box" id="result" hidden><h2><span class="step">03</span>Review before you export</h2><div id="summary" class="stats" aria-live="polite"></div><p class="hint">CSV values remain text, including leading-zero IDs and formula-looking strings. A spreadsheet may still interpret them as formulas or numbers: import untrusted data and identifiers explicitly as text.</p><div class="scroll"><table id="preview"></table></div><p id="preview-note"></p><div class="row"><button id="download-csv" type="button">Download cleaned CSV</button><button id="download-report" class="secondary" type="button">Download report JSON</button></div><details><summary>Preview change report</summary><pre id="report"></pre></details></section>
</main><footer>CSV Fix Kit v1 · AI-assisted software · Keep the original and review the report. No automatic type conversion, email validation, date inference or conflict resolution. Questions: <a href="mailto:alvinhastra@gmail.com">alvinhastra@gmail.com</a></footer>
<script>
/** CSV Fix Kit: conservative, local-only CSV processing. */
const SPACE = /^[\u0009-\u000d\u001c-\u0020\u0085\u00a0\u1680\u2000-\u200a\u2028\u2029\u202f\u205f\u3000]+|[\u0009-\u000d\u001c-\u0020\u0085\u00a0\u1680\u2000-\u200a\u2028\u2029\u202f\u205f\u3000]+$/g;
const strip = value => value.replace(SPACE, '');

function parseCsv(input, delimiter = ',') {
  if (typeof input !== 'string') throw new Error('CSV input must be text.');
  if (![',', ';', '\t'].includes(delimiter)) throw new Error('Choose comma, semicolon or tab.');
  if (new TextEncoder().encode(input).length > 5 * 1024 * 1024) throw new Error('Browser limit: 5 MB. Use the Python tool for larger files.');
  const text = input.startsWith('\uFEFF') ? input.slice(1) : input;
  if (!text.length) throw new Error('A header row is required.');
  let row = [], field = '', quoted = false, closed = false, line = 1, startLine = 1, pending = false;
  const records = [];
  function finishField() {
    row.push(field); field = ''; closed = false;
    if (row.length > 256) throw new Error('Browser limit: 256 columns. Use the Python tool for wider files.');
  }
  function finishRow(endLine) {
    finishField();
    records.push({ values: row, record: records.length + 1, start_line: startLine, end_line: endLine });
    row = []; pending = false;
    if (records.length > 50001) throw new Error('Browser limit: 50,000 data rows. Use the Python tool for larger files.');
  }
  for (let i = 0; i < text.length; i++) {
    const c = text[i];
    if (quoted) {
      if (c === '"') {
        if (text[i + 1] === '"') { field += '"'; i++; }
        else { quoted = false; closed = true; }
      } else {
        field += c;
        if (c === '\r') {
          if (text[i + 1] === '\n') { field += '\n'; i++; }
          line++;
        } else if (c === '\n') line++;
      }
      continue;
    }
    if (c === delimiter) { finishField(); pending = true; continue; }
    if (c === '\r' || c === '\n') {
      if (!pending && !row.length && !field.length && !closed) throw new Error('Blank physical record at line ' + line + '. Remove it explicitly and try again.');
      finishRow(line);
      if (c === '\r' && text[i + 1] === '\n') i++;
      line++; startLine = line; continue;
    }
    if (closed) throw new Error('Unexpected character after a closing quote at line ' + line + '.');
    if (c === '"') {
      if (field.length) throw new Error('Quote inside an unquoted field at line ' + line + '.');
      quoted = true; pending = true;
    } else { field += c; pending = true; }
  }
  if (quoted) throw new Error('Unterminated quoted field starting in record ' + (records.length + 1) + '.');
  if (pending || row.length || field.length || closed) finishRow(line);
  const header = records.shift()?.values;
  if (!header?.length || header.some(name => !strip(name))) throw new Error('Every column needs a nonempty header.');
  if (new Set(header).size !== header.length) throw new Error('Duplicate column names are ambiguous.');
  for (const record of records) {
    if (record.values.length !== header.length) throw new Error('Record ' + record.record + ' has ' + record.values.length + ' fields; expected ' + header.length + '.');
  }
  return { header, records };
}

function cleanCsv(input, { trim = [], keys = [], dropDuplicates = false, delimiter = ',', source = 'input.csv' } = {}) {
  if (!Array.isArray(trim) || !Array.isArray(keys) || trim.some(x => typeof x !== 'string') || keys.some(x => typeof x !== 'string') || typeof dropDuplicates !== 'boolean') throw new Error('Invalid cleanup options.');
  trim = [...new Set(trim)]; keys = [...new Set(keys)];
  const { header, records } = parseCsv(input, delimiter);
  for (const name of [...trim, ...keys]) if (!header.includes(name)) throw new Error('Unknown column: ' + name + '. Read columns again after changing the header.');
  const trimIndices = trim.map(name => header.indexOf(name));
  const keyIndices = keys.map(name => header.indexOf(name));
  const output = [], changes = [], duplicates = [], missing = [], seen = new Map(), groups = new Map();
  for (const record of records) {
    const row = record.values.slice();
    const location = { record: record.record, start_line: record.start_line, end_line: record.end_line };
    const changed = [];
    for (const i of trimIndices) { const value = strip(row[i]); if (value !== row[i]) { row[i] = value; changed.push(header[i]); } }
    if (changed.length) changes.push({ ...location, columns: changed });
    const canonical = JSON.stringify(row), first = seen.get(canonical);
    if (first !== undefined) duplicates.push({ ...location, duplicate_of_record: first, removed: dropDuplicates });
    else seen.set(canonical, record.record);
    if (keyIndices.length) {
      const key = keyIndices.map(i => row[i]);
      if (key.some(value => !strip(value))) missing.push(location);
      else {
        const encoded = JSON.stringify(key);
        if (!groups.has(encoded)) groups.set(encoded, { key, variants: new Map() });
        const variants = groups.get(encoded).variants;
        if (!variants.has(canonical)) variants.set(canonical, { row, records: [] });
        variants.get(canonical).records.push(record.record);
      }
    }
    if (first === undefined || !dropDuplicates) output.push(row);
  }
  const conflicts = [];
  for (const group of groups.values()) {
    const variants = [...group.variants.values()];
    if (variants.length > 1) conflicts.push({
      key: Object.fromEntries(keys.map((name, i) => [name, group.key[i]])),
      records: variants.flatMap(variant => variant.records).sort((a,b) => a-b),
      differing_columns: header.filter((name, i) => new Set(variants.map(variant => variant.row[i])).size > 1),
    });
  }
  const report = {
    source,
    options: { trim_columns: trim, key_columns: keys, drop_exact_duplicates: dropDuplicates, delimiter },
    summary: { input_records: records.length, output_records: output.length, trimmed_records: changes.length, exact_duplicates: duplicates.length, removed_records: records.length-output.length, conflicting_key_groups: conflicts.length, records_missing_key: missing.length },
    trimmed: changes, duplicates, conflicts, missing_keys: missing,
  };
  const encode = value => value.includes(delimiter) || /["\r\n]/.test(value) ? '"' + value.replaceAll('"','""') + '"' : value;
  const csv = [header, ...output].map(row => row.length === 1 && row[0] === '' ? '""' : row.map(encode).join(delimiter)).join('\r\n') + '\r\n';
  return { header, rows: output, csv, report };
}

const el = id => document.getElementById(id);
let currentResult = null, sourceName = 'pasted.csv', fileRevision = 0;
const example = 'id,name,email\n00123, Alice ,alice@example.com\n00123,Alice,alice@example.com\n00123,Alice,other@example.com\n, Bob ,bob@example.com\n';
const delimiter = () => el('delimiter').value === 'tab' ? '\t' : el('delimiter').value;
function invalidate() { currentResult = null; el('result').hidden = true; el('error').textContent = ''; }
function fail(error) { invalidate(); el('error').textContent = error.message || String(error); }
function checkboxList(container, headers, prefix) {
  container.replaceChildren();
  headers.forEach((name, index) => {
    const label = document.createElement('label'), input = document.createElement('input'), text = document.createElement('span');
    input.type = 'checkbox'; input.value = name; input.id = prefix + '-' + index;
    input.addEventListener('change', invalidate); text.textContent = name;
    label.append(input,text); container.append(label);
  });
}
function readColumns() {
  invalidate(); el('options').hidden = true;
  try { const { header } = parseCsv(el('source').value,delimiter()); checkboxList(el('trim-columns'),header,'trim'); checkboxList(el('key-columns'),header,'key'); el('dedupe').checked = false; el('options').hidden = false; }
  catch (error) { fail(error); }
}
el('read').addEventListener('click',readColumns);
el('source').addEventListener('input',()=>{fileRevision++;sourceName='pasted.csv';invalidate();});
el('delimiter').addEventListener('change',()=>{invalidate();el('options').hidden=true;});
el('dedupe').addEventListener('change',invalidate);
el('example').addEventListener('click',()=>{fileRevision++;el('file').value='';el('source').value=example;el('delimiter').value=',';sourceName='example.csv';readColumns();});
el('file').addEventListener('change',async()=>{
  invalidate(); el('options').hidden=true;
  const file=el('file').files[0], revision=++fileRevision; if(!file)return;
  try {
    if(file.size>5*1024*1024)throw new Error('Browser limit: 5 MB. Use the Python tool for larger files.');
    const text=new TextDecoder('utf-8',{fatal:true}).decode(await file.arrayBuffer());
    if(revision!==fileRevision)return;
    el('source').value=text;sourceName=file.name;readColumns();
  } catch(error){if(revision===fileRevision)fail(error instanceof TypeError?new Error('The file must be valid UTF-8. Export it as UTF-8 CSV and retry.'):error);}
});
function selected(id){return Array.from(el(id).querySelectorAll('input:checked'),input=>input.value);}
el('clean').addEventListener('click',()=>{
  invalidate();
  try {
    const result=cleanCsv(el('source').value,{trim:selected('trim-columns'),keys:selected('key-columns'),dropDuplicates:el('dedupe').checked,delimiter:delimiter(),source:sourceName});
    currentResult=result;
    const s=result.report.summary;
    el('summary').textContent=s.input_records+' input rows → '+s.output_records+' output rows\n'+s.trimmed_records+' rows trimmed · '+s.exact_duplicates+' exact duplicates found · '+s.removed_records+' rows removed\n'+s.conflicting_key_groups+' conflicting key groups · '+s.records_missing_key+' rows with missing keys';
    const table=el('preview');table.replaceChildren();
    const head=document.createElement('thead'),body=document.createElement('tbody'),headRow=document.createElement('tr');
    result.header.forEach(value=>{const th=document.createElement('th');th.scope='col';th.textContent=value;headRow.append(th);});head.append(headRow);table.append(head,body);
    result.rows.slice(0,30).forEach(row=>{const tr=document.createElement('tr');row.forEach(value=>{const td=document.createElement('td');td.textContent=value.length>120?value.slice(0,120)+'…':value;tr.append(td);});body.append(tr);});
    el('preview-note').textContent='Showing '+Math.min(result.rows.length,30)+' of '+result.rows.length+' rows. Long cells are shortened in this preview only. Exports include full values and every kept row.';
    const json=JSON.stringify(result.report,null,2);el('report').textContent=json.length>24000?json.slice(0,24000)+'\n… Preview shortened. Download the full report.':json;
    el('result').hidden=false;
  }catch(error){fail(error);}
});
function download(name,content,type){const url=URL.createObjectURL(new Blob([content],{type}));const a=document.createElement('a');a.href=url;a.download=name;document.body.append(a);a.click();a.remove();setTimeout(()=>URL.revokeObjectURL(url),30000);}
el('download-csv').addEventListener('click',()=>{if(currentResult)download('cleaned.csv',currentResult.csv,'text/csv;charset=utf-8');});
el('download-report').addEventListener('click',()=>{if(currentResult)download('report.json',JSON.stringify(currentResult.report,null,2)+'\n','application/json');});
el('source').value=example;

</script></body></html>