← back to AbramsOS
connectors: no-signup bank import — Wells Fargo CSV upload (headerless + negative-debit aware)
ded692c895f1ac43659478bc41d0eb4f3cd1d91b · 2026-07-13 00:49:05 -0700 · Steve
- csv-parser: headerless fallback (WF/Quicken export: date,amount,*,,description) + signMode — WF debits are negative=spend, so normalize to positive + drop deposits; header CSVs unchanged
- upload.js: dedup key (csv:date:amount:merchant) + ON CONFLICT so re-uploading overlapping statements doesn't double
- connectors.ejs: 'Import CSV' upload form with WF download instructions — zero third-party signup path
- answers Steve: Plaid needs a free web signup (no CLI); CSV upload needs nothing
Files touched
M lib/csv-parser.jsM routes/upload.jsM views/connectors.ejs
Diff
commit ded692c895f1ac43659478bc41d0eb4f3cd1d91b
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jul 13 00:49:05 2026 -0700
connectors: no-signup bank import — Wells Fargo CSV upload (headerless + negative-debit aware)
- csv-parser: headerless fallback (WF/Quicken export: date,amount,*,,description) + signMode — WF debits are negative=spend, so normalize to positive + drop deposits; header CSVs unchanged
- upload.js: dedup key (csv:date:amount:merchant) + ON CONFLICT so re-uploading overlapping statements doesn't double
- connectors.ejs: 'Import CSV' upload form with WF download instructions — zero third-party signup path
- answers Steve: Plaid needs a free web signup (no CLI); CSV upload needs nothing
---
lib/csv-parser.js | 48 +++++++++++++++++++++++++++++++++++-------------
routes/upload.js | 11 ++++++++---
views/connectors.ejs | 25 +++++++++++++++++++++++++
3 files changed, 68 insertions(+), 16 deletions(-)
diff --git a/lib/csv-parser.js b/lib/csv-parser.js
index 6327f21..4b50bee 100644
--- a/lib/csv-parser.js
+++ b/lib/csv-parser.js
@@ -82,32 +82,54 @@ function parseStatement(csv) {
}
const header = parseLine(lines[0]);
- const dateIdx = findColumn(header, DATE_HEADERS);
- const amountIdx = findColumn(header, AMOUNT_HEADERS);
- const merchantIdx = findColumn(header, MERCHANT_HEADERS);
+ let dateIdx = findColumn(header, DATE_HEADERS);
+ let amountIdx = findColumn(header, AMOUNT_HEADERS);
+ let merchantIdx = findColumn(header, MERCHANT_HEADERS);
+ let startRow = 1;
+ // 'positive' = a debit/charge is a positive number (typical header CSVs).
+ // 'negative' = money out is a negative number (Wells Fargo / Quicken headerless export).
+ let signMode = 'positive';
+ // Headerless fallback (Wells Fargo etc.): first line is already data — field 0 is a
+ // date and some later field is numeric. Money-out is negative in these exports.
if (dateIdx < 0 || amountIdx < 0) {
- return {
- rows: [], skipped: 0,
- errors: [`missing required columns (date=${dateIdx}, amount=${amountIdx}); found: ${header.join(', ')}`],
- columns: { dateIdx, amountIdx, merchantIdx },
- };
+ const f0 = parseLine(lines[0]);
+ if (parseDate(f0[0]) && f0.some((v, i) => i > 0 && parseAmount(v) != null)) {
+ dateIdx = 0;
+ amountIdx = f0.findIndex((v, i) => i > 0 && parseAmount(v) != null);
+ // description = the longest mostly-alphabetic field
+ let best = -1, bestLen = 0;
+ f0.forEach((v, i) => { const t = String(v || '').trim(); if (/[a-z]/i.test(t) && t.length > bestLen) { best = i; bestLen = t.length; } });
+ merchantIdx = best;
+ startRow = 0; // no header row to skip
+ signMode = 'negative'; // WF/Quicken: purchases are negative
+ } else {
+ return {
+ rows: [], skipped: 0,
+ errors: [`missing required columns (date=${dateIdx}, amount=${amountIdx}); found: ${header.join(', ')}`],
+ columns: { dateIdx, amountIdx, merchantIdx },
+ };
+ }
}
const rows = [];
const errors = [];
let skipped = 0;
- for (let i = 1; i < lines.length; i++) {
+ for (let i = startRow; i < lines.length; i++) {
const f = parseLine(lines[i]);
const date = parseDate(f[dateIdx]);
- const amount = parseAmount(f[amountIdx]);
+ let amount = parseAmount(f[amountIdx]);
if (!date || amount == null) {
skipped += 1;
if (errors.length < 5) errors.push(`line ${i + 1}: bad row (date=${f[dateIdx]}, amount=${f[amountIdx]})`);
continue;
}
- if (amount <= 0) { // refund / deposit; CSV uploads are for purchases
+ // Normalize to positive spend; drop inflows (deposits/refunds/payments).
+ if (signMode === 'negative') {
+ if (amount >= 0) { skipped += 1; continue; } // deposit/credit
+ amount = Math.abs(amount);
+ } else if (amount <= 0) {
skipped += 1;
continue;
}
@@ -117,11 +139,11 @@ function parseStatement(csv) {
date,
amount,
merchant,
- raw: { line: i + 1, fields: f, header },
+ raw: { line: i + 1, fields: f, header: startRow ? header : null },
});
}
- return { rows, skipped, errors, columns: { dateIdx, amountIdx, merchantIdx } };
+ return { rows, skipped, errors, columns: { dateIdx, amountIdx, merchantIdx, signMode } };
}
module.exports = { parseStatement, parseLine, parseAmount, parseDate };
diff --git a/routes/upload.js b/routes/upload.js
index 7663506..0c325c4 100644
--- a/routes/upload.js
+++ b/routes/upload.js
@@ -28,13 +28,18 @@ router.post('/api/upload/csv', upload.single('file'), async (req, res) => {
let inserted = 0;
for (const r of parsed.rows) {
const purchaseId = id('purchase');
+ // Stable dedup key so re-uploading overlapping statements doesn't double rows.
+ const dedupKey = `csv:${new Date(r.date).toISOString().slice(0, 10)}:${r.amount}:${r.merchant}`.slice(0, 200);
try {
- await db.query(
+ const ins = await db.query(
`INSERT INTO purchase
(id, user_id, source_message_id, merchant_name, merchant_domain, order_number, purchase_date, total_amount, currency, confidence, raw_extract)
- VALUES ($1, $2, NULL, $3, NULL, NULL, $4, $5, 'USD', $6, $7)`,
- [purchaseId, DEV_USER_ID, r.merchant, r.date, r.amount, 0.85, r]
+ VALUES ($1, $2, NULL, $3, NULL, $4, $5, $6, 'USD', $7, $8)
+ ON CONFLICT (user_id, order_number) WHERE order_number IS NOT NULL DO NOTHING
+ RETURNING id`,
+ [purchaseId, DEV_USER_ID, r.merchant, dedupKey, r.date, r.amount, 0.85, r]
);
+ if (!ins.rows.length) continue; // duplicate of an already-imported transaction
await audit.log({
actorType: 'user',
actorId: DEV_USER_ID,
diff --git a/views/connectors.ejs b/views/connectors.ejs
index 5dd3aae..5b42ee2 100644
--- a/views/connectors.ejs
+++ b/views/connectors.ejs
@@ -15,6 +15,17 @@
</div>
<div id="bankList" style="margin-top:.75rem"></div>
<div id="bankStatus" class="subtle" style="margin-top:.5rem"></div>
+
+ <hr style="border:none;border-top:1px solid rgba(128,128,128,.2);margin:1rem 0">
+ <div>
+ <strong>No signup? Upload a statement instead.</strong>
+ <p class="subtle" style="margin:.25rem 0">In Wells Fargo online banking → your account → <em>Download Account Activity</em> → <em>Comma Delimited Format (CSV)</em>, then drop the file here. No Plaid, no third-party account — handles Wells Fargo's headerless format.</p>
+ <form id="csvForm" style="display:flex;gap:.5rem;align-items:center;flex-wrap:wrap">
+ <input type="file" name="file" accept=".csv,text/csv" required>
+ <button class="button" type="submit">Import CSV</button>
+ <span id="csvStatus" class="subtle"></span>
+ </form>
+ </div>
</section>
<% if (!connectors.length) { %>
@@ -125,4 +136,18 @@
})();
</script>
+<script>
+ document.getElementById('csvForm')?.addEventListener('submit', async (e) => {
+ e.preventDefault();
+ const s=document.getElementById('csvStatus'), btn=e.target.querySelector('button');
+ const fd=new FormData(e.target); if(!fd.get('file') || !fd.get('file').size){ s.textContent='pick a file'; return; }
+ btn.disabled=true; s.textContent='Importing…';
+ try{
+ const j=await (await fetch('/api/upload/csv',{method:'POST',body:fd})).json();
+ if(j.error){ s.textContent='Error: '+j.error+(j.details?(' — '+j.details.join('; ')):''); }
+ else { s.textContent=`Imported ${j.inserted} transactions`+(j.skipped?` (${j.skipped} skipped: deposits/dupes)`:''); setTimeout(()=>location.reload(),1500); }
+ }catch(err){ s.textContent='Error: '+err.message; } finally{ btn.disabled=false; }
+ });
+</script>
+
<%- include('partials/footer') %>
← a71abd4 docs: roadmap — banking (Plaid) track shipped; real-bank key
·
back to AbramsOS
·
purchases: collect 38 real Amazon orders from info@ (via Geo 4a02cce →