CSV Import for Accounting and Finance Software

Accounting runs on exports. A bookkeeper closes a period, prints a journal report or a trial balance out of the ledger, and sends that file on to whoever asked for it. The file is a report first and a data feed second, so it carries subtotals, a grand total at the bottom, and blank cells under a value that was written once.

It also carries money and dates in whatever shape the old system printed them. Amounts arrive with thousands separators and a minus sign after the digits, account codes lose their leading zeros the moment a spreadsheet opens them, and the same day is written three ways across three exports.

The journal report a client sends holds 1,886 rows and closes with a grand total. That total reads 5,688,871.26 on the debit side and 5,688,871.26 on the credit side. The credit lines above it add up to 5,658,300.32.

An accounting platform keeps journal lines. Its customers export a report out of the system they are leaving, and moving that report into your tables is customer data onboarding. The 30,570.94 between the total and the lines went missing on the way out of the old system, and the report is the only place it shows.

The schema belongs to you. The report belongs to the accountant at the customer, who ran it out of whatever kept the books before. The one below is 167 KB of .csv over 10 columns, and one import of it produced every number printed here.

Several rows, one entry

A journal entry is a group of lines whose debits equal its credits. The group is the unit. One line of it says nothing.

MindBridge publishes the checklist its own platform asks a client's ledger to meet. Four fields are required, being Account ID, Effective Date, Debit and Credit. Transaction ID is described as "the unique code used to group entries within a given transaction", and both amount fields carry a control point named Unbalanced Debits and Credits.

A report prints that group the way a person reads it. The date, the type, the number and the author sit on the first line, and every line under it carries an account and an amount and nothing else.

The file the accountant drops in

Eleven of its rows and the grand total it closes with, spelled the way the export spelled them.

1Date,Transaction Type,No.,Memo/Description,Account No.,Account,Debit,Credit,Created By,Created201/01/2025,Journal Entry,1039,Payroll journal,6010,Salaries and wages,"43,958.73",,d.okonkwo,01/01/2025 2:47:25 PM3,,,Payroll journal,6020,Employee benefits,"3,956.29",,,4,,,Payroll journal,2150,Payroll liabilities:Withholding,,"10,550.10",,5,,,Payroll journal,2160,Payroll liabilities:Pension,,"2,197.94",,6,,,Payroll journal,01020,Cash and cash equivalents:Payroll account,,"35,166.98",,701/03/2025,Sales Receipt,1011,Licence renewal 4221,1010,Cash and cash equivalents:Operating account,"3,719.65-",,s.pettersen,01/03/2025 3:17:45 PM8,,,Licence renewal 4221,4010,Revenue:Product,,"3,099.71-",,9,,,Licence renewal 4221,2100,Sales tax payable,,619.94-,,10,,,Total for January 2025,,,"513,774.17","513,774.17",,1102/01/2025,Bill,1122,Supplier invoice 4448,6420,Travel and entertainment:Conferences,"8,346.58",,a.varga,02/01/2025 10:03:42 AM12,,,Supplier invoice 4448,2010,Accounts payable,,"8,346.58",,⋮1874 rows not shown1887,,,Grand total,,,"5,688,871.26","5,688,871.26",,

Entry 1039 runs five lines and only the first one says so. Entry 1011 writes its three amounts with the minus sign after the digits. The row between the two entries is a monthly total. One account code arrives padded to five characters, one names a sub-account after a colon, and one belongs to no account this customer had when they signed up.

The accountant drops that file into your platform and Updog opens it in the tab they are already sitting in. Reading, matching and checking all happen there, and your API sees a line of this ledger only after they press submit. Client-side and server-side import puts the two designs next to each other.

Your side of the ledger

Ten fields. Intuit publishes six of them as what a journal entry import needs, being Journal No., Journal Date, Account Name, Journal/Description, Debits and Credits. MindBridge adds the entered date and the user.

import type { DataEditorColumn } from "@updog/data-editor";
import { accountCodeColumn } from "./accountCode";
import { signedAmount } from "./signedAmount";

const TYPES = ["Journal", "Sale", "Purchase", "Payment", "Transfer", "Adjustment"];

export const buildColumns = (chart: Set<string>): DataEditorColumn[] => {
  return [
    { id: "entryNo", title: "Entry number" },
    { id: "postingDate", title: "Posting date", editor: { type: "date" } },
    {
      id: "entryType",
      title: "Entry type",
      editor: { type: "select", options: TYPES, enableCustomValue: false },
    },
    { id: "memo", title: "Memo", size: 220 },
    accountCodeColumn(chart),
    { id: "accountName", title: "Account name", size: 260 },
    {
      id: "debit",
      title: "Debit",
      editor: { type: "number" },
      transformer: signedAmount,
      validators: [{ type: "number", min: 0, decimalPlaces: 2 }],
    },
    {
      id: "credit",
      title: "Credit",
      editor: { type: "number" },
      transformer: signedAmount,
      dependentFields: ["debit"],
      validators: [
        { type: "number", min: 0, decimalPlaces: 2 },
        {
          type: "function",
          fn: (value, row) => {
            const debit = String(row.debit ?? "").trim();
            const credit = String(value ?? "").trim();
            return Boolean(debit) === Boolean(credit)
              ? { level: "error", message: "A line carries a debit or a credit" }
              : null;
          },
        },
      ],
    },
    { id: "createdBy", title: "Created by" },
    {
      id: "enteredDate",
      title: "Entered date",
      editor: { type: "date" },
      validators: [{ type: "date" }],
      transformer: (value) => {
        const parts = /^(\d{2})\/(\d{2})\/(\d{4})/.exec(String(value ?? ""));
        return parts ? parts[3] + "-" + parts[1] + "-" + parts[2] : value;
      },
    },
  ];
};

Every amount passes through the number editor before it is stored, so 43,958.73 lands as 43958.73 and a currency symbol falls away with the comma. 1,581 rows carry a grouped amount and 1,529 of them lose the comma with no rule of yours. The two transformer entries are there for the shapes that editor leaves alone, and both have a section below.

Two headers that both say account

Nine of the ten headers land with no configuration. Debit, Credit and Created By are the field name under different punctuation. Date, Memo/Description and Account each sit inside the field they claim. No., Transaction Type and Account No. share one word out of two with theirs.

Two headers say account and two fields say account. One assignment runs over the whole header row, so Account takes accountName and Account No. takes accountCode. Neither one crowds the other.

Created reaches nothing. It shares no word with entered date and sits too far from it to score. One alias settles that, and the rest of the table exists for the next customer, whose export out of Xero calls the same columns JournalNumber, JournalDate, AccountCode and NetAmount.

export const synonyms = {
  columns: {
    entryNo: ["no.", "journalnumber", "transaction number"],
    postingDate: ["journaldate", "effective date"],
    accountCode: ["accountcode", "account number", "account id"],
    memo: ["details", "description"],
    enteredDate: ["created", "entered date"],
  },
  values: {
    Sale: ["invoice", "deposit", "credit memo"],
    Purchase: ["bill", "expense", "vendor credit"],
  },
};

Where each transaction type lands

Value matching collects the type column across the whole upload. Eleven distinct strings arrive against six options, and five of them land on their own.

The six left over are decisions about accounting, and the spelling has nothing to do with it. A deposit and a credit memo both touch a customer, and a vendor credit reverses a purchase. Which side of your enum each one lands on is your platform's call. Writing all six into the values table settles them once, in code, where an accountant never has to meet them.

What each field receives

Common CSV import errors covers those failures in the shape any file can produce. The sections below take the ones a ledger produces.

The entry number written once

1,065 of the 1,886 rows leave No. blank. The value those rows need sits on a row above them, and how far above depends on how many lines that entry ran to.

No rule that reads one row can see it. A function validator receives a value and the row around it, and the row around it is exactly what is missing. The entry number is the one field a ledger line cannot supply for itself.

{ type: "asyncFunction" } is the rule that reaches the rest. It receives every cell in the column at once, in the order the file arrived in, and each cell carries its row. So the sweep walks the column, remembers the last number it saw, and hands that number to every line under it.

That is the fill-down, and it happens inside the rule that needs it. Nothing is written back into the grid, so the person still sees the report the way the report was printed.

The minus that arrives after the number

23 entries in this file carry every amount negative, and the export wrote the sign at the end of each one. That is 55 cells reading 3,719.65-. An entry written that way undoes an earlier one.

The convention comes from mainframe accounting output and it is still shipping. SAP's own ABAP documentation says that for a numeric type "the last place on the right is reserved for the sign", and that negative values are given a minus there while positive values are given a blank.

Updog reads a number by peeling display noise off both ends and taking the sign from the front. The four ways a ledger writes a negative land in three places.

So all 55 reach the grid untouched and the number rule reports each one.

A number the importer cannot read casts no vote either. The scan that decides whether a file writes 1,250.00 or 1.250,00 skips those 55 entirely, so 23 reversals settle nothing about the other 798 entries.

Three lines in the schema take the sign back to the front.

export const signedAmount = (value: unknown): unknown => {
  const written = String(value ?? "");
  return written.endsWith("-")
    ? "-" + written.slice(0, -1).replace(/,/g, "")
    : value;
};

All 55 become numbers, and every one of them is negative, so min: 0 reports each as out of range. A negative debit is an accounting decision, and your platform is the one that settles whether it stores one.

The date with a time after it

Every filled Created cell carries a time after the date, as 01/01/2025 2:47:25 PM. The date patterns the importer holds all end at the year, so a trailing time matches none of them. All 821 stay as text and the date rule flags each one.

The posting date beside it needs nothing. The file writes 01/13/2025, the thirteen can only be a day, and one verdict of month-first covers every date column in the file. All twelve months of 2025 come out where they belong.

A transformer runs after the parser and its output is never sent back. So a transformer that returns 01/01/2025 leaves the cell exactly as flagged as it was. The one on enteredDate in the schema above writes the ISO string itself, and the 821 flags go to zero.

The account your customer never opened

Your app already holds this customer's chart of accounts, so the account column is checked against the codes that customer actually keeps. 19 rows in this ledger name codes 4030, 6260 and 6420, which the customer opened after the chart was pulled. Intuit tells its own users to add new accounts to the chart of accounts before the import runs.

114 rows write the code padded to five characters, so 1020 arrives as 01020. Xero asks for account codes that match exactly, leading zeros included. Padding both sides before the comparison lets one rule accept both spellings, and no row in this file fails for that reason.

The name beside the code carries its parent, as Payroll liabilities:Withholding. Intuit requires that full path on the way in and answers a bare sub-account with a Line Account Invalid error. Keeping the whole string is the cheaper choice, because the code is what identifies the account and the name is what a person reads.

When the two sides do not agree

The same sweep that rebuilds the grouping adds up each entry.

import type { DataEditorColumn, ValidationError } from "@updog/data-editor";

const cents = (value: unknown): number => {
  const parsed = Number(value);
  return Number.isFinite(parsed) ? Math.round(parsed * 100) : 0;
};

export const accountCodeColumn = (chart: Set<string>): DataEditorColumn => {
  return {
    id: "accountCode",
    title: "Account code",
    validators: [
      { type: "required" },
      {
        type: "function",
        fn: (value) => {
          const code = String(value ?? "").trim().padStart(5, "0");
          return chart.has(code)
            ? null
            : { level: "error", message: "No account with this code" };
        },
      },
      {
        type: "asyncFunction",
        fn: async (cells) => {
          const totals = new Map<string, number>();
          const entryOf: string[] = [];
          let current = "";

          for (const cell of cells) {
            const declared = String(cell.row.entryNo ?? "");
            if (declared) current = declared;
            entryOf.push(current);
            totals.set(
              current,
              (totals.get(current) ?? 0) +
                cents(cell.row.debit) -
                cents(cell.row.credit),
            );
          }

          return entryOf.map((entry): ValidationError | null => {
            const off = totals.get(entry) ?? 0;
            return off === 0
              ? null
              : {
                  level: "error",
                  message: "Entry " + entry + " is out by " + (off / 100).toFixed(2),
                };
          });
        },
      },
    ],
  };
};

Debits and credits are totalled in whole cents, because two floating-point sums of 1,873 amounts disagree in the last place often enough to matter. Every line of a failing entry carries the message, so the person sees the whole entry.

The sweep flags 50 entries over 118 rows. 17 of them are out by 0.01, which is a rounding difference on a foreign-currency line. The rest are out by between 449.46 and 18,037.83, and behind each one is a line that left the file on the way out.

A sweep is handed the clean cells only. Anything empty, or already carrying an error, waits until the person settles it, so this account column handed over 1,854 of its 1,873 lines. The 19 held back are the off-chart codes, and an entry missing a line stops balancing.

So 19 of those 50 entries carry the flag because the rule above them fired. Fix the codes first. The sweep then runs on all 1,873 lines, the flag lands on 31 entries over 68 rows, and those 31 go back to the client.

The rows that are not lines

Every total line in the report leaves the account column empty, so required catches all 13. They sit in the same list as everything else the person works through, and the person selects the 13 and deletes them in one move.

MindBridge tells its own users that subtotal lines do not belong in a general ledger file. Deleting them in the grid marks them isDeleted, and the handler below drops them before it groups anything.

The code that stays in your repository

Updog ships no chart of accounts, no ledger template, no document type list and no connector to any accounting system. Updog Importer reads CSV, TSV, JSON, XML, XLSX, XLS, XLSB and ODS, so a Xero export that lands as tab-separated text opens the same way a spreadsheet does. A bank statement that lands as a PDF reaches a parser you supply and comes back as rows the wizard treats like any other. Above that layer, the ten columns, the six options, the alias table and the sweep are all yours to maintain.

A sweep carries one cost, and it is worth knowing before you write your first. The file's arrival is the only moment the rule sees the whole column. After that it is called with whatever one edit touched, so correcting a single account code hands it one cell and no entry around that cell. The balance flag on that entry keeps whatever the opening pass gave it.

This file arrives once, on the day a customer moves in. The SDK holds no schedule, no queue and no server of ours between the accountant and your API.

What the handler writes

<DataEditor
  columns={useMemo(() => buildColumns(chart), [chart])}
  synonyms={synonyms}
  primaryKey="entryNo"
  blockSubmitOnError
  onComplete={async (result) => {
    const lines = result.sources
      .flatMap((source) => source.rows)
      .filter((r) => r.isValid && !r.isDeleted)
      .map((r) => r.row);

    const entries = new Map<string, LedgerLine[]>();
    let current = "";
    let header = { postingDate: "", enteredDate: "", entryType: "", createdBy: "" };

    for (const line of lines) {
      if (line.entryNo) {
        current = line.entryNo;
        header = {
          postingDate: line.postingDate,
          enteredDate: line.enteredDate,
          entryType: line.entryType,
          createdBy: line.createdBy,
        };
      }
      entries.set(current, [
        ...(entries.get(current) ?? []),
        {
          ...header,
          entryNo: current,
          accountCode: line.accountCode.padStart(5, "0"),
          memo: line.memo,
          amount: Number(line.debit || 0) - Number(line.credit || 0),
        },
      ]);
    }

    await postEntries([...entries.entries()]);
  }}
/>

Every row comes back through onComplete under the source it arrived in, tagged isNew, isChanged, isDeleted and isValid. The handler walks them the way the sweep did, because they come back in the order they were read.

The signed amount is folded here, in the handler. A transformer receives one value and nothing else, so a rule that spans two columns lives in a validator and a value that spans two columns lives in your handler.

primaryKey is required, and this report offers it one column. The entry number is filled on the first line of each entry, distinct across all 821 of them, and blank on the 1,065 lines under them. A key on it merges those 821 and lands the other 1,065 again, so a second upload of the same year takes 1,886 rows to 2,951. The identity your platform assigns to a line is the only thing that settles it.

The list the accountant works down

Out of 1,886 rows, 199 carry something a rule caught. 118 rows in an entry that does not balance, 55 amounts written with a trailing minus, 27 lines carrying both amounts or neither, 19 off-chart account codes and 13 total lines. 33 rows carry two of those at once, and the same file against a schema with no transformers carried 940.

The accountant works down that list against the ledger the report came out of, which is the only place those answers live. Then 1,873 lines become 821 entries, the two sides of each one agree to the cent, and the 30,570.94 the grand total was carrying on its own is back on the lines.