Bulk CSV Handling

The job

I built this in the chat at Octolane. Someone drops a spreadsheet and wants those companies in the CRM.

The file might be a column of domains. It might be a column of work emails. It might be a mess of both, buried in notes. Three rows and a hundred thousand rows take the same path.

Read it like mail. The company is the address on the envelope. acme.com is already an address. jane@northwind.io carries the company in the email. A link such as contoso.com/about is the same company, with a page attached. sam@gmail.com is a person, so it stays in the pile.

What went wrong

At first the model did the reading and the writing. It searched the web for companies whose domains were already in the file. It asked the person to type domains by hand. Then it tried again. A large file died when the reply ran out of room.

The model still chooses the step. Ordinary code reads the file and writes the rows. That code does the same thing every time, whether the file has three rows or a hundred thousand.

What you see

A batch is a group of rows we write together. Skip means the company was already in the CRM, so we leave the existing record alone.

The file lands in the thread. If the message clearly asks to add companies, we start reading. If the message is only “here’s the list,” we ask one question. If the person asked for something else, a summary, an email, or deals, this flow stays out of the way.

While the file is read, the card says “Parsing accounts from your file,” then “Checking which companies are already in your CRM.” A live line shows “Reading 1,200 of 10,000 rows.”

Ten or fewer new companies stay as a list in the thread. Each row can be checked or unchecked. Eleven or more become a compact card: a few logos, a chip such as “10K more,” and a count of rows, unique companies, and companies already in the CRM. Opening that card uses the side panel. The message itself does not grow a scrollbar.

Approve is the yes. Dismiss leaves the card closed. After approval the card shows created, skipped, and failed. Stop pauses the next batch. The batch already sent still finishes. If the model’s sentence never arrives, the card still says what happened.

What the message means

We read the message in a fixed order. The first match wins.

“Don’t add these” stops the flow, even though the sentence also contains “add.” A question such as “how many companies are in here?” is a read, so we answer it. “Add these companies,” “import this list,” and “add the rest” start the flow. Drafting an email or making deals is a different job, and that job keeps its own path. Anything else said next to a fresh spreadsheet counts as unsaid, so we ask once.

type Intent = "stop" | "answer" | "add" | "other" | "ask";

function readIntent(message: string, hasFile: boolean): Intent {
  const text = message.trim();

  if (declines(text)) return "stop";
  if (asksAQuestion(text)) return "answer";
  if (asksToAddCompanies(text)) return "add";
  if (asksForAnotherJob(text)) return "other";

  return hasFile ? "ask" : "other";
}

Where the chat is

The stage comes from what has already happened in the conversation. A flag set at the start of the turn goes stale as soon as the chat takes an unexpected step.

First we read the sheet. Then we find the domains. Then we show the card and wait. We write only after approval. While one of these steps owns the turn, the model is given that step. Web search, a freeform question, and a one-off create stay off the table, so the model cannot wander into them.

type Stage = "read" | "domains" | "preview" | "wait" | "write";

function stageFrom(done: ReadonlySet<string>): Stage {
  if (!done.has("sheet")) return "read";
  if (!done.has("domains")) return "domains";
  if (!done.has("preview")) return "preview";
  if (!done.has("approved")) return "wait";
  return "write";
}

What kind of file

Before we look anything up, we decide what kind of file this is.

A clean list has a domain or website column, or no header at all, just domains down the page. That column is at least about 80% filled, and about 80% of the filled cells are real hosts. If the header row itself looks like a list of domains, we treat it as data.

Messy text still contains hosts, but they sit in notes, in mixed email cells, or under a title row. Names only means company names and no hosts. That path is the last resort.

A “clean” read that finds zero domains is a misread. If any token looks like a host, we treat the file as messy text. Otherwise we treat it as names only.

type FileKind = "clean" | "messy" | "names";

function kindOfFile(cells: string[]): FileKind {
  const filled = cells.map((cell) => cell.trim()).filter(Boolean);
  const hosts = filled.filter(isHost);
  const filledRatio = cells.length === 0 ? 0 : filled.length / cells.length;
  const hostRatio = filled.length === 0 ? 0 : hosts.length / filled.length;

  if (filledRatio >= 0.8 && hostRatio >= 0.8) return "clean";
  if (hosts.length > 0) return "messy";
  return "names";
}

One messy file makes the whole turn messy. A clean list wins over a names-only file, so a tidy domain list is kept as a clean list when a second file is only names.

function kindForTurn(files: FileKind[]): FileKind {
  if (files.includes("messy")) return "messy";
  if (files.includes("clean")) return "clean";
  return "names";
}

Where a domain comes from

We try the cheapest source first. A later source runs only for rows the earlier source could not resolve. A clean list stops at the file. It never goes to the web.

A website cell can list more than one domain. We keep the first usable one for that row. A work email is next. nicole@us.acme.co.uk becomes acme.co.uk, the company domain, including endings like co.uk. A personal inbox such as Gmail is a person, so it stays out.

function companyDomain(email: string): string | null {
  const host = email.split("@")[1]?.toLowerCase();
  if (!host || isPersonalInbox(host)) return null;
  return registrableDomain(host);
}

function domainForRow(row: Row, kind: FileKind): string | null {
  const fromFile =
    domainFromWebsite(row.website) ??
    companyDomain(row.email) ??
    domainInText(row.text);

  if (fromFile) return fromFile;
  if (kind === "clean") return null;

  return domainFromCrm(row.name) ?? domainFromWeb(row.name);
}

We drop a duplicate domain and a duplicate company name. A row with only a domain gets its name from that domain. A row with neither is counted and left out.

function keepUnique(rows: Company[]): Company[] {
  const seen = new Set<string>();

  return rows.filter((row) => {
    const key = row.domain || row.name;
    if (!key || seen.has(key)) return false;
    seen.add(key);
    return true;
  });
}

For messy text we scan the cells we already extracted. If that scan is empty, one fixed script reads the whole file. A crashed run is tried a few more times. The script’s output then goes through the same cleanup, so it cannot invent a directory domain.

Names only are matched against companies already in the CRM. What is still missing gets one web pass, then a grader that may use only those results. Rows from the file are high confidence and start checked. A web guess can be medium or low. A low guess starts unchecked.

Writes

Approve stores a job for that file and that selection. The click itself creates no accounts. The same click twice reuses the live job. A queue is a line of work waiting its turn. The first message on that line says “plan.”

async function onApprove(fileId: string, selectedIds: string[]) {
  const key = stableKey(fileId, selectedIds);
  const live = await findLiveJob(key);
  if (live) return live;

  return enqueue(key);
}

The worker counts the selected rows, picks a batch size, and marks the job running.

Three rows never split. Fifty thousand rows are cut into batches of about 100 and those batches run side by side on one queue. A hundred thousand rows use that same cut, so the field of batches is about twice as wide. Past about 500 rows, each batch is its own message. Smaller jobs still walk the batches in order.

function batchSize(rowCount: number): number {
  if (rowCount <= 50) return rowCount;
  if (rowCount <= 200) return 50;
  return 100;
}

async function writeJob(rows: Row[]) {
  const size = batchSize(rows.length);

  if (rows.length > 500) {
    for (let start = 0; start < rows.length; start += size) {
      await sendBatch({ start, size });
    }
    return;
  }

  for (let start = 0; start < rows.length; start += size) {
    await writeWindow(rows.slice(start, start + size));
  }
}

Each batch reads that window of rows, turns each cell into a domain, and calls one bulk create. A company already in the CRM is skipped. When every batch is done, the job is completed, partial, failed, or cancelled. Partial means some rows landed and some failed. Rows that landed are marked at the end, so a later pass does not offer them as new. Marking them in the middle would make the next window skip rows that were never written.

What keeps a run safe

async function writeWindow(rows: Row[]) {
  if (rows.length === 0) {
    return { created: 0, skipped: 0, failed: 1 };
  }

  let created = 0;
  let skipped = 0;
  let failed = 0;

  for (const row of rows) {
    const domain = readDomain(row.cell);
    if (!domain) {
      failed += 1;
      continue;
    }

    try {
      const outcome = await createCompany(domain);
      if (outcome === "skipped") skipped += 1;
      else created += 1;
    } catch (error) {
      failed += 1;
      if (isTemporary(error)) throw error;
    }
  }

  return { created, skipped, failed };
}

• One bad cell is counted as failed. The rest of the batch continues.
• A validation error is recorded and left there. A temporary error, such as a dropped connection, is tried again.
• If the reply’s counts do not add up to the batch, we correct them so progress cannot pass the size of that batch.
• Stop sets a pause. The worker holds the next batch. The batch already sent still finishes.
• If the queue is missing, the file is still parsing, or nothing is selected, the request fails in plain language. It does not pretend a job started.
• An empty window is a failed window. It is recorded, and the job keeps its place.
• Progress is one snapshot of the totals. We send created, skipped, and failed, rather than one event per row.

What to copy

Decide what kind of file you have before you pick a method. Read the file itself before you call the network. Give the model one step at a time.

Keep the rows in a store, and send the model a short count. Make each write safe to repeat, and send the rows in batches. Change the screen with the count: a list when it is short, a summary and a panel when it is long. Let the card say what happened, even when the model’s sentence never arrives.