eturn null; } function yy(s) { return s.length === 2 ? 2000 + +s : +s; } function detectOrder(values) { var dmy = 0, mdy = 0; values.forEach(function (v) { var p = splitDate(v); if (p && !p.fixed) { if (p.a > 12) dmy++; if (p.b > 12) mdy++; } }); if (dmy && !mdy) return "DMY"; if (mdy && !dmy) return "MDY"; if (dmy && mdy) return "MIXED"; return "AMBIGUOUS"; } function toIso(v, order, counters) { var p = splitDate(v); if (!p) return null; var y = p.y, mo, d; if (p.fixed) { mo = p.m; d = p.d; } else { var o = order; if (o === "MIXED" || o === "AMBIGUOUS") { if (p.a > 12 && p.b <= 12) o = "DMY"; else if (p.b > 12 && p.a <= 12) o = "MDY"; else { o = "MDY"; if (p.a <= 12 && p.b <= 12 && p.a !== p.b) counters.ambiguous++; } } if (o === "DMY") { d = p.a; mo = p.b; } else { mo = p.a; d = p.b; } } if (mo < 1 || mo > 12 || d < 1 || d > 31 || y < 1900 || y > 2100) return null; return y + "-" + two(mo) + "-" + two(d); } function formatDate(iso, style) { var p = iso.split("-"); if (style === "MM/DD/YYYY") return p[1] + "/" + p[2] + "/" + p[0]; if (style === "DD/MM/YYYY") return p[2] + "/" + p[1] + "/" + p[0]; return iso; } // amounts as integers scaled by 10^scale, so sums never suffer float error function parseMoney(v, commaDecimal) { var s = String(v).trim(); if (s === "") return { empty: true }; var neg = false; if (/\b(dr|db)\b\.?$/i.test(s)) { neg = true; s = s.replace(/\b(dr|db)\b\.?$/i, ""); } else if (/\bcr\b\.?$/i.test(s)) s = s.replace(/\bcr\b\.?$/i, ""); s = s.replace(/[^\d.,()+\-]/g, ""); if (/^$.*$$/.test(s)) { neg = !neg; s = s.slice(1, -1); } if (/-$/.test(s)) { neg = !neg; s = s.slice(0, -1); } if (/^-/.test(s)) { neg = !neg; s = s.slice(1); } s = s.replace(/^\+/, ""); // decide the decimal separator for THIS value; the column-wide hint only settles the ambiguous // case of a single separator followed by exactly three digits ("1,500" / "1.500") var lastComma = s.lastIndexOf(","), lastDot = s.lastIndexOf("."); var dec = null; if (lastComma !== -1 && lastDot !== -1) dec = lastComma > lastDot ? "," : "."; else if (lastComma !== -1 && s.split(",").length === 2) dec = (s.length - lastComma - 1 !== 3 || commaDecimal) ? "," : null; else if (lastDot !== -1 && s.split(".").length === 2) dec = (s.length - lastDot - 1 !== 3 || !commaDecimal) ? "." : null; if (dec === ",") s = s.replace(/\./g, "").replace(",", "."); else if (dec === ".") s = s.replace(/,/g, ""); else s = s.replace(/[.,]/g, ""); if (!/^(\d+\.?\d*|\.\d+)$/.test(s)) return { bad: true }; var parts = s.split("."); var frac = parts[1] || ""; return { neg: neg, int: parts[0] || "0", frac: frac, scaled: Number((parts[0] || "0") + frac), scale: frac.length }; } function moneyString(cents, scale) { var neg = cents < 0; var s = String(Math.abs(cents)); while (s.length <= scale) s = "0" + s; var out = s.slice(0, s.length - scale) + (scale ? "." + s.slice(s.length - scale) : ""); return (neg ? "-" : "") + out; } function canonMoney(p) { var frac = p.frac; while (frac.length < 2) frac += "0"; var iv = p.int.replace(/^0+(?=\d)/, ""); var isZero = /^0*$/.test(iv + frac); return (p.neg && !isZero ? "-" : "") + iv + "." + frac; } function findCol(header, re) { for (var i = 0; i < header.length; i++) if (re.test(header[i].toLowerCase())) return i; return -1; } function repairCsv(raw, dateStyle, dateOrder, dupMode) { var text = raw.replace(/^\ufeff/, ""); var delim = detectDelimiter(text).delim; var rows = parseDelimited(text, delim); var st = { rows: 0, dates: 0, badDates: 0, ambiguous: 0, amounts: 0, badAmounts: 0, merged: 0, duplicates: 0, order: "" }; var notes = []; if (!rows.length) return { text: "", st: st, notes: ["The file is empty."] }; var header = rows[0].map(function (h) { return h.trim(); }); var data = rows.slice(1).filter(function (r) { return r.some(function (c) { return c.trim() !== ""; }); }); st.rows = data.length; var iDate = findCol(header, /date|posted|booking/); var iDebit = findCol(header, /

Bank Statement CSV/OFX/QFX Reconciler

Guides for this problem

Feedback

Leave feedback