Migrate from spreadsheets: import CSV, keep your numbering

Migrate from spreadsheets without losing your history: import CSVs into typed, validated records and keep your existing document numbering intact.

Moving off spreadsheets is mostly a data problem, and the data is the part people get wrong. This guide walks a migration into AxisIQ in the order that actually works: shape your fields first, import CSV in files of up to 500 rows with every row validated on the way in, and carry your existing invoice and order numbers across so nothing in your filing has to change.

By the end you will have customers, products and open orders sitting in AxisIQ as real records — typed, searchable and linked to each other — with numbering that continues from your last spreadsheet row instead of restarting at one.

Most of the work is deciding what your columns are really called.

What you need before you start

  • Your sheets exported as CSV, one file per kind of thing: customers, products, orders, invoices. A sheet holding five kinds of row gets split first.
  • Permission to create and edit object types and fields in the workspace. The owner has it; anyone else needs it granted.
  • A decision about history. Open items — unpaid invoices, unshipped orders, live stock — must be right on day one; closed history can follow later or stay where it is.

New to the workspace? Run through the getting-started guide first, then come back to Step 1.

Some workbooks should stay workbooks. A quoting model or a planning sheet full of formulas is not a set of records — import that one whole into Axis Sheets (XLSX in, formulas stay live) and keep this guide for the data that is records: customers, products, orders.

Step 1 — Shape first, load second

A spreadsheet column has no type; every cell in it is a suggestion. In AxisIQ each field has a real type, so this is where you decide what your data actually is: a date as a date, an amount as money, a status as a fixed list of options, a customer on an order as a link to that customer's record rather than their name typed again.

If the AI workspace builder set up your workspace, it has already created types for what your business keeps — typically customers, products, stock, orders and suppliers — beside the built-in invoices, payments, accounts and locations. See how much of your sheet is already covered before you invent anything, then add the fields that are only yours: carton size, HSN code, warranty end date, the technician who fitted it. A sheet with no equivalent at all becomes a new type. How records, types and fields fit together is worth ten minutes before you commit to names.

While you are here, set the rules you want enforced from now on — required, allowed options, number ranges, a format for codes, uniqueness on the fields that must not collide. Those rules bite on every write after the import too, which is the point of moving.

If a field is required but your old data never captured it, give it a default. Defaults fill absent values on create, so one sensible default stops a required field blocking a decade of imperfect rows.

Step 2 — Let the system tell you the header shape

Do not guess the column names. Create one record by hand in the type you are about to load, fill it the way a real row should look, and export that type to CSV. What comes back is the header the importer reads — id, created_at, then one column per field — with your worked example underneath. The first two are ignored on the way back in; align your spreadsheet to the rest, renaming in the sheet rather than in your head.

Columns the type does not recognise are ignored rather than rejected — forgiving when your sheet carries notes you do not want, unforgiving when you mistype a header: the import succeeds and that field stays empty on every record. Check one imported record before running the rest.

An empty cell counts as absent rather than as a blank value, so a field with a default gets its default and a field without one is simply left unset.

Step 3 — Decide your numbering before the first row lands

Your customers, your accountant and your own archive know your invoices by number, and a migration that renumbers them creates years of small arguments.

AxisIQ generates numbers from a pattern you set on a text field — the number field on invoices or on your order type, or one of your own. A pattern is literals plus exactly one sequence token, with optional zero-padding and an optional reset.

  • INV-{yyyy}-{seq:4} produces INV-2026-0001, INV-2026-0002
  • SO/{yy}{mm}/{seq:3:monthly} restarts at 001 each month
  • {seq:5} on its own is fine if you never want a prefix

Date tokens cover the year in four or two digits, the month and the day, and a sequence can reset daily, monthly or yearly. Choose a reset and the pattern must carry the matching date token — a sequence restarting each year without the year in it would hand out last year's numbers again.

Then the rule that matters most in a migration: a new record gets the next generated number, but an imported row that brings its own number keeps it. Import INV-2019-0117 and it stays INV-2019-0117. Once your history is in, set the next number so new records continue past the highest you loaded; the counter only moves forward, never back over numbers already issued.

One honest quirk: if a record takes a number and then fails to save, that number is not handed to the next one. You get an occasional gap. That is the deliberate trade — a gap you can explain beats a duplicate you cannot.

Step 4 — Import in parent-before-child order

A link between records needs its target to exist already — a stock movement pointing at a product you have not loaded has nothing to point at. Load in dependency order:

  1. The people you trade with — customers and their contacts, then suppliers
  2. What you sell — products, then variants
  3. Locations, then the stock movements that point at products
  4. Orders still in flight, then their lines
  5. Unpaid invoices and the payments received against them

Each file carries up to 500 data rows. Every row goes through the full set of checks a manually created record faces — required fields, formats, allowed options, uniqueness, number ranges. Nothing is coerced quietly to make it fit.

What comes back is a count created, a count failed, and every failure with its row number and reason. Rejects are itemised rather than fatal: the good rows are in, the bad ones are a worklist. Fix those in a small CSV of their own and run it again.

An import creates records; it never matches and updates an existing one. Run the same file twice and you have two sets of rows, unless a uniqueness rule stops the second run — a good reason to put uniqueness on your customer code or SKU before you start.

Step 5 — Scan what you just loaded

Your old rules were only ever enforced by whoever was typing, so even a disciplined sheet accumulates a renamed supplier, a status nobody uses any more, and three date formats.

Run a Data Quality scan across the types you loaded. It re-checks stored records against the field definitions you set in Step 1 and reports what does not hold: missing required values, values outside the allowed options, formats that drifted, links pointing at records that are not there. It reports rather than repairs — every fix is an ordinary record edit — but it turns "I think the import went fine" into a worklist. Do it the same day, while you still remember which sheet each row came from.

Splitting a large sheet

Sort on a stable column so the split is repeatable, cut the sheet into chunks named in order — customers-01.csv, customers-02.csv — and import them in sequence, checking each result before the next file.

Twenty thousand customers is forty files. Dull, but every batch gets checked rather than assumed.

The spreadsheet does not go away

Migrating is not a divorce. Every type exports back to CSV — up to 10,000 rows, and it tells you when it has been capped — and any Analytics result has its own CSV download, so a question you answer in SQL is in a sheet a second later.

The spreadsheet stays your scratchpad for modelling something new; AxisIQ holds the version everyone else works from. Where spreadsheets break as a production system covers that split in more detail.

The limits, plainly

  • 500 rows per import file. There is no bulk loader that swallows a whole sheet.
  • 10,000 rows per export. Larger types come back truncated, with a flag saying so.
  • References need their targets first. Import parents before children or the links land empty.
  • Import creates, it does not update. No match-and-merge on a key, so re-running a file duplicates unless a uniqueness rule blocks it.
  • Header alignment happens in your spreadsheet, not in a mapping screen.

None of them is hard to work around, and all are easier to know now than at row 4,000.

FAQ

How many rows can I import at once? Up to 500 data rows per CSV file. Larger sheets are split into batches of 500 and run in sequence, each with its own validation report.

Can I keep my existing invoice numbers when I migrate? Yes. An imported row that carries its own number keeps it; only records created without one get a generated value. After the import you set the counter so new numbers continue past your highest legacy number.

What happens to rows that fail validation? They are rejected individually and reported with their row number and reason, while the valid rows in the same file import normally. You correct them in a smaller file and import that.

Does importing update records that already exist? No. Every row creates a new record, so importing the same file twice produces duplicates unless a field with a uniqueness rule refuses the second copy.

In what order should I import my sheets? Whatever gets pointed at goes in first: trading partners and products before the stock movements, orders and invoices that reference them, because a link needs its target record to exist at the moment the row is created.