Import transactions

Master data and transactions load from CSV, which is how nearly every migration and most bank feeds actually arrive.

• Import customers, vendors, parts, parts groups and price lists.
• Import transactions — invoices, payments and journal entries — not just master data.
• Import a chart of accounts when setting a dataset up, if you would rather not start from a country template.
• Map your columns onto the fields, so the file does not have to be rewritten to suit us.
• Rows are checked before anything posts, and errors are reported per row rather than failing the whole file.
• Bank statement import for reconciliation.
• If the file is awkward, send it to us — migrating data is part of what we do.

Where to find it Import › Customers  ·  Import › Vendors  ·  Import › Parts  ·  Import › Chart of Accounts  ·  Import › Sales Orders  ·  Import › General Ledger

Loading a CSV file

1. Open Import and the table you are loading. Choose the file, the delimiter, and whether strings are quoted.
2. Press Continue. The first row of the file is shown against the fields of the table; pick which of your columns feeds which field.
3. Save the mapping as a map file and it is offered next time, so a bank export or a supplier price list loads the same way every month.
4. Rows are checked before anything is written; errors are reported by row number. Fix the file and run it again — nothing partial is left behind.

What each import expects

Twelve imports live under Import: customers, vendors, parts, services, labour/overhead, groups, sales invoices, payments, sales orders, purchase orders, chart of accounts and general ledger. Each shows a preview with a tick box per row before anything is written; rows that fail validation have no box.

1. Customers / vendors: customernumber (or vendornumber), name, firstname, lastname, contacttitle, phone, fax, email, notes, address1, address2, city, state, zipcode, country.
2. Parts: partnumber, description, unit, partsgroup, listprice, sellprice, lastcost, rop, bin, image, drawing, notes. Duplicates are checked on part number; a group that does not exist yet is created and marked + in the preview. Taxes and account links are chosen on the upload screen and applied to every row.
3. Sales invoices: one row per line, repeating the header columns for a multi-line invoice — invnumber, transdate, duedate, customernumber, curr, invoicedescription, partnumber, qty, sellprice, employeenumber, AR, department, warehouse, with optional ordnumber, ponumber, terms, notes, projectnumber, serialnumber, deliverydate, taxincluded and more.
4. Payments: datepaid, memo, debit, credit, dcn. Each payment is matched to an open AR or AP invoice first on the DCN column, then on the amount; choose the bank account and currency on the upload screen.
5. General ledger: reference, transdate, description, notes, accno, debit, credit, source, memo. One transaction per unique reference, any number of lines, and it must balance; a row whose account is not in the chart shows **** in the preview and is refused.
6. Chart of accounts: accno, description, charttype (H or A), category (A/L/Q/I/E), link — the link column carries the drop-down flags, e.g. AR_paid:AP_paid for a bank account, IC for inventory.
7. Dates follow your own date format from Preferences. Keep the file in UTF-8, and quote any field that contains a comma.

From the blog

• Data import CSV sample files — Attached ZIP file contains sample csv files which you can use as a starting point to import data into SQL-Ledger.