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.
• Columns are matched by their names, in any order; a map file can translate headings that arrive the same way every month.
• Rows are checked in a preview before anything posts, and a bad row is left out rather than failing the whole file.
• Payment files from the bank, as CSV or the Swiss .v11 format, matched to open invoices and posted.
• 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. The first row of the file names the columns, using the field names listed under What each import expects below; columns are matched by name, in any order.
3. If a file always arrives with its own headings — a bank export, a supplier price list — an import.map file in the dataset’s template folder translates them, so it loads the same way every month without being edited. Ask us and we set one up.
4. Press Continue. Every row is checked and shown in a preview before anything is written; rows that fail have no tick box and are left out. Untick anything you do not want and import the rest, or fix the file and run it again.

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. Inventory, income and expense accounts come from System › Defaults unless the file carries account columns.
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.