Data Exchange
Data Exchange moves your lists in and out of LedgerLite as CSV files. You can add many customers, suppliers or products at once, change many products at once, and export a copy of your lists to read in a spreadsheet. Nothing here posts to your accounts. No journal entry, stock movement or VAT is created.
- Where
- Data Exchange in the left menu. Products also has an Import / export in Data Exchange button
- Number
- None. Each saved import has an Import ID, under Technical details
- Posts to
- Nothing. It creates or changes records, not accounting entries
- Who
- Managers and admins. Only admins can undo an import
What it’s for
Section titled “What it’s for”- Add many records at once. A first load of customers, suppliers or products from a spreadsheet. A file holds up to 3,000 rows and up to 5 MB.
- Update many products at once. Change names, prices, brands, costs, categories and codes of products that already exist, matched by item number.
- Export your lists. A copy of all your customers, suppliers or products to read in a spreadsheet. It is not a backup.
- Review and take back. Every file you preview is saved in History. An admin can undo a whole import of new records if nothing uses them yet.
What it does not do:
- It does not bring in opening stock, opening balances, or what customers and suppliers owe. Use Settings › Opening data for those. See Opening data.
- It does not set up bundles. Bundles are set up on the product screen.
- It does not change a customer or a supplier. A file for customers or suppliers only adds new ones. An existing record is never changed.
The records you add or change are described on Products, Customers and Suppliers.
Fields
Section titled “Fields”The screen has a few choices at the top, then the steps of one import.
| Field | What it means | What it changes |
|---|---|---|
| Customers, Suppliers, Products | The kind of record the file is about | Changes the template, the rules and the History list. You cannot switch while a request is being sent or is not confirmed |
| Import and export, History | Work on a file, or look back at saved imports | Shows the steps or the list of saved imports |
| What should the file do? (Products only) | Create new products or Update existing products | A creating file adds products. An updating file changes products found by item number |
| CSV file | The file you choose. It must be a UTF-8 CSV made from the template | Nothing yet. Preview file reads it |
| Reason (under Reason for the price changes) | One reason for every price the update file changes. At least 3 characters, up to 200 | Saved with each price change in the product’s price history. Apply needs it |
| Note (optional) (in the undo box) | Why you are undoing the import. Up to 500 characters | Kept with the record of the undo |
What each file holds
Section titled “What each file holds”You always start from the import template of the same kind. The template has the exact column titles and no data rows. Do not change the columns.
- Customers. Code, Name, Customer Type, Legal Name, contact details, Tax Number, Payment Terms Days, Credit Limit, Credit Hold and its reason, a billing address, a shipping address, and Notes. Payment terms left blank mean 0 days. Credit hold defaults to No. The shipping address defaults to the billing address.
- Suppliers. Code, Name, Email, Phone and Tax Number. Leave the code empty to have LedgerLite give the next code, such as SUP-000001, when you apply the file. A code you type is used as it is.
- Products. Item number (SKU), Name, Item Type, Barcode, Category, Brand, Model Number, Unit, Default Purchase Cost, Retail Price, Wholesale Price, Minimum POS Price, Tax Code, Reorder Level, Alternate Barcodes, PLU, Supplier Codes, Variant Group and Variant Label.
Notes for product files:
- Every new product needs a unit, a retail price, a category and an active tax code. The unit and the tax code must already exist in LedgerLite, and you name them by their code.
- Brand and Model Number are plain text, up to 120 characters each.
- Default Purchase Cost is optional: a number above zero, up to 4 decimal places, for one unit. It is only the cost suggested on purchase and goods receipt lines. It creates no stock and posts nothing. Leave it empty for a service. A comma may only come before each group of three digits, as in 1,250.00.
- A file from the earlier version 1 products template, without Brand, Model Number and Default Purchase Cost, still imports.
- Keep codes and barcodes as text in your spreadsheet so leading zeros are not lost. Alternate Barcodes and Supplier Codes take one plain code, or several in brackets such as
["00123","00456"].
How a Category cell works
Section titled “How a Category cell works”- A category name is matched ignoring capitals and outer spaces.
- A name that does not exist yet is created when you apply the file, spelled as it first appears in the file. The preview lists the new categories above the rows.
- A category that was archived is never created again or reactivated by a file. Reactivate it in Products, or use another category.
- Creating categories needs permission to create categories. Without it, use the name of an existing active category.
Updating products
Section titled “Updating products”An update file uses the same template.
- Each row finds its product by the item number in the first column.
- An empty cell keeps the value as it is. An upload never clears a value. Fill in the item number and only the columns you want to change.
- A code column (Alternate Barcodes, PLU, Supplier Codes) that has a code replaces the codes of that kind.
- Variant Group and Variant Label cannot be changed here. The Unit and Item Type of a product that already has stock or sales history cannot be changed either.
- Default Purchase Cost can be read or changed only by someone who may see purchase costs.
- A product that is archived, or a code the file repeats, is an error on that row.
Buttons
Section titled “Buttons”| Button | When you see it | What happens | Accounts |
|---|---|---|---|
| Download import template | Before you choose a file | Downloads the blank template of the chosen kind, for example products-v2-template.csv | No change |
| Export reference data | You may export this kind of record | Downloads a copy of your existing records, in files of up to 1,000 records. See below | No change |
| Download file 2, Start again from file 1, Export again | After a first export | Gets the next file, restarts from file 1, or exports once more | No change |
| Preview file | After you choose a file | Checks the file and saves what it says. It creates and changes nothing | No change |
| Review | On each row of a preview | Opens the values uploaded for that row. For an update, first what the row changes | No change |
| Download row errors, Download 3 errors | The file or an attempt has errors | Downloads the errors as a CSV file | No change |
| Apply 18 new customers, Apply 12 product updates | The preview is ready, or an earlier Apply failed | Adds all the rows, or changes all the products, together or not at all | No accounting entries |
| Choose another file, Import another file, Update from another file | After a preview or a result | Starts again with a fresh file box | No change |
| View imported records, View products | After an import was applied | Opens the customers, suppliers or products screen | No change |
| Check status | An Apply was not confirmed | Asks again with the same request. See When the answer is lost | No change |
| Undo this import | Admins only, on an applied import of new records that nothing uses | Asks you to confirm, then removes the records the file created | No accounting entries |
| Keep the import | In the undo box | Closes the box and changes nothing | No change |
| Check again | Undo is refused because records are in use | Asks again whether the import can be undone | No change |
| Back to history, Refresh | You opened a saved import, or you are in History | Returns to the list, or reads it again | No change |
The results you see on a file:
- Creating file: Ready to import, Imported, Not imported, Needs fixing.
- Updating file: Ready to update, Updated, Not updated, Needs fixing.
- An import an admin took back shows Undone.
- In the rows of an update, each row reads Will update, No change, Error, or Updated once applied.
The steps of an import
Section titled “The steps of an import”- Choose the kind. Pick Customers, Suppliers or Products. For products, choose Create new products or Update existing products.
- Download the template and fill it in. Keep the columns as they are.
- Choose your file and press Preview file. The app reads the file, checks every row, and saves the result in History. Nothing is created yet.
- Read the preview. Use the three tabs: Rows, Errors, and Attempts. A file with errors opens on Errors. For an update, the rows show each value before and after.
- Type the price reason, if the update changes a retail, wholesale or minimum price. Type it once for the whole file.
- Press Apply. The app checks the file again, then adds or changes every row in one step. If any row fails, none is saved.
- Read the result. The page says, for example, that 18 customers were imported and that no stock, opening balances or accounting entries were created.
Exporting
Section titled “Exporting”The reference export is a copy of your existing records to read in a spreadsheet. The screen says it plainly: it is not a database backup. It cannot restore your data or your history, and it is not an import template.
- A list longer than 1,000 records comes in several files. The card says which file comes next. Only Start again from file 1 goes back to the first file.
- The file adds two columns at the end, Active and Record ID.
- Purchase costs are left out of the products file for a person who may not see costs, and the card says so.
- To export a kind you need the right to export and the right to see that kind of record.
What gets posted
Section titled “What gets posted”Nothing is posted. A file that adds customers, suppliers or products creates the records and nothing else: no journal entry, no stock movement, no VAT. A file that updates products changes the product records and nothing else.
| What you do | What the app changes | What it does not change |
|---|---|---|
| Add customers or suppliers | Creates the customers or suppliers. Automatic supplier codes use the next numbers of the series | Balances. A customer or supplier starts at nothing owed |
| Add products | Creates the products and their categories if new. Each new product starts its price history (“Initial product price”) | Stock. A new product has no quantity. Default Purchase Cost is only a suggested cost |
| Update products | Changes the product records. Every price change is saved with your reason in the price history | Posted documents. Past invoices, purchases, stock movements and VAT keep what they were posted with |
| Undo an import | Removes the records the file created, and what the import wrote with them | Everything else. Categories the file created stay. The automatic number series is not wound back |
| Export | Nothing | Everything |
Because the screen posts nothing, it has no worked journal example. To bring in starting stock or balances, which do post to your accounts, use Opening data.
Where it shows up
Section titled “Where it shows up”| Place | What changes |
|---|---|
| Products | New and updated products. New products appear in the list. A product’s price history shows each price change and its reason |
| Customers | New customers |
| Suppliers | New suppliers |
| Categories (in Products) | New categories the file created |
| Data Exchange History | Every file you previewed, applied or not, newest first, 25 to a page |
History shows one kind of record at a time: the kind you chose at the top. It lists the File, Result, Rows, Errors, Prepared by, Prepared and Imported columns. The file name opens the saved result. History can never apply an import again.
What the app blocks
Section titled “What the app blocks”A file with any error cannot be applied. Fix the errors in your spreadsheet and choose the file again. Not even the good rows are created while a bad row remains. The Errors tab names the row, the field and what to fix. Row 1 is the header, so the first data row is row 2. A problem with the whole file is shown as Whole file.
Problems with the file itself
Section titled “Problems with the file itself”| Situation | What you see |
|---|---|
| A file over 5 MB | This file is larger than 5 MB. Split it into smaller files and import them one at a time. |
| A file not saved as UTF-8 | This file is not saved as UTF-8. In your spreadsheet, save it as “CSV UTF-8” and choose it again. |
| Columns changed, or a file from the wrong template | Use the products v2 template, or a file made from the earlier products v1 template, without changing its columns. Exports with status columns are reference files, not import templates. For customers and suppliers the first part reads “Use the customers v1 template without changing its columns.” |
| No data rows, or over 3,000 | Include between 1 and 3,000 data rows. |
Problems with a row that adds records
Section titled “Problems with a row that adds records”| Situation | What you see |
|---|---|
| A code that already exists, including archived records | This code already exists, including archived masters. Imports create new records only. |
| A code repeated in the file | This code is repeated in the file. |
| A barcode, SKU or PLU already used by a product or pack | Scan code ‹code› already identifies an existing product or pack. |
| The same scan code twice in the file | Scan code ‹code› is also used on row ‹row›. |
| The same code twice on one product | Use each primary barcode, alternate barcode or PLU only once on this row. |
| A tax code or unit that is not active | Enter an active tax code from Settings → Taxes. or Enter an active base-unit code from Settings → Units. |
| A blank category | Category is required. Enter an existing active category name. |
| An archived category | This category is archived. Reactivate it in Products, or use another category. |
| A new category, and you may not create categories | The category message above followed by You may not create categories. Use an existing active category. |
| A bundle row | Bundles are set up on the product screen. |
| A cost on a service | Leave Default Purchase Cost empty for a service item. |
| A cost of zero | Default purchase cost must be greater than zero. |
| A cost that is not a number | Enter Default Purchase Cost as a number above zero with up to 4 decimal places, for example 12.50. |
| A cost with a doubtful comma, such as 12,5 | Default Purchase Cost has a comma that could mean two things. Use a comma only before a group of three digits, as in 1,250.50, or leave commas out, as in 12.50. |
| A Brand or Model Number that is too long | Brand is longer than 120 characters. (or Model Number) |
| A code cell with a comma, semicolon or line break | Alternate Barcodes JSON has a comma, semicolon or line break, so it could be one code or several. Write one code on its own, or several in brackets, like [“00123”,“00456”]. (or Supplier Codes JSON) |
| A code cell that says NULL | Supplier Codes JSON says “NULL”. Leave the cell empty when there is no code. |
| A long number a spreadsheet shortened | Alternate Barcodes JSON has 6.29123E+12, which looks like a long number that a spreadsheet shortened. Format the column as Text in the spreadsheet and type the code again. |
| A customer with a shipping address but “Shipping Same As Billing” left as true | Set false to use the separate shipping address, or clear the shipping columns. |
| A supplier code that is too long or has other characters | Use 30 characters or fewer. or Use only letters, numbers, and hyphens. |
Other values, such as an invalid email, a payment term over 365 days or a credit hold without a reason, are judged by the same rules as the customer, supplier and product forms.
Problems with a row that updates products
Section titled “Problems with a row that updates products”| Situation | What you see |
|---|---|
| No item number on the row | Enter the item number of the product to update. |
| An item number no product has | No product has this item number. Check the code, or use Create new products to add it. |
| An archived product | This product is archived. Restore it first, then update it. |
| A code repeated in the file | This code is repeated in the file. |
| An unknown category | Enter the name of an existing active category, or leave the cell empty to keep the current one. followed by the reason when you may not create categories |
| A product with no category | This product has no category yet. Enter an existing active category name in the Category column. |
| An unknown tax code or unit | Enter an active tax code from Settings → Taxes, or leave the cell empty to keep the current one. or Enter an active base-unit code from Settings → Units, or leave the cell empty to keep the current one. |
| A change to Variant Group or Variant Label | Variant Group and Variant Label cannot be changed by an upload. Open the product and use Manage variants or Contains variants. |
| A new Unit for a product with history | Unit of measure cannot change after this product has transaction history, because its past stock and sales are counted in the old unit and the units would mix. Create a new product with the right unit and move the stock with a stock adjustment. |
| A new Item Type for a product with history | Item type cannot change after this product has inventory, purchase, or POS history, because its stock would have to be written off. Create a new product with the right type and move the stock with a stock adjustment. |
| A Minimum POS Price above the retail price | Minimum POS price cannot exceed the retail or wholesale price. |
| A barcode already taken | This barcode already exists. |
| A cost cell from someone who may not see costs | Default Purchase Cost can be changed only by someone who may see purchase costs. Leave the cell empty. |
| No price reason, or one under 3 characters | Explain the price change in at least 3 characters. beside the Reason box. If it reaches the server without one: Enter a reason for changing the retail, wholesale or minimum POS price. |
Problems when you press Apply
Section titled “Problems when you press Apply”| Situation | What you see |
|---|---|
| The preview has errors | Correct the CSV file and prepare a new preview. |
| The import moved on since the preview | Refresh the import before applying it. |
| 50 attempts used up | Prepare a new preview after 50 unsuccessful attempts. |
| A list changed after the preview, such as a tax code or category | A referenced master changed since preview. Prepare a new preview before importing. |
| A product changed after an update preview | This product changed after the preview. Prepare the preview again. |
| A row cannot be created at the last moment | This row could not be created. Check its code, barcode and referenced masters, then retry or prepare a corrected file. No rows were imported. |
| The automatic supplier codes ran out | The automatic supplier codes have reached their limit. Type a code on this row, or ask an administrator to change the supplier code settings. No rows were imported. |
| You may update products but the update right is missing | Updating products needs permission to update products. Ask an administrator to give it to you. |
When an Apply fails, the import shows Not imported (or Not updated), nothing was saved, and the Attempts tab lists the attempt with its errors. You can apply the same preview again after fixing the cause, up to 50 attempts.
Undo is refused
Section titled “Undo is refused”| Situation | What you see |
|---|---|
| Some records are already used | The box titled This import cannot be undone says ‹n› records from this file are already in use. Undo takes back the whole file or nothing, so nothing is removed. It lists each record and what uses it, for example “customer invoices (2)”, then Cancel or remove what uses them first, or archive the records you no longer want from their own screens. If you press Undo on a stale screen, the refusal reads This import cannot be undone: ‹n› records are already in use. followed by the first three records and Nothing was removed. |
| Already undone | This import was already undone. Refresh to see it. |
| Never applied | Only an import that was applied can be undone. |
| An update file | This file changed existing products, and Undo does not restore earlier values. Upload a corrected update file to put them right. |
When the answer is lost
Section titled “When the answer is lost”Before an Apply is sent, the request is saved on this computer. If the answer does not come back, the screen shows Import result not confirmed with We could not confirm whether ‹file› (‹n› customers) was imported. and Nothing else can be applied until this is confirmed. Check status asks again with the same request, so nothing is created twice. The notice stays on every Data Exchange screen, even after you reload the page or close and reopen it.
- Press Check status. The server answers with what really happened. An import that went through is shown, not repeated.
- You can still look at History and open saved results while the request is held.
- If the preview answer is lost, choose the same file and press Preview file again. It keeps the same reference, so it cannot be saved twice.
- If you try to leave while a request is being sent, the screen asks: An import is still being sent. Its result is kept on this computer, so you can check it when you come back. Leave this screen?
Notices that do not stop you
Section titled “Notices that do not stop you”- Missing right for updates. Without permission to update products, Update existing products is greyed out and the page says so.
- Costs left out. If you may not see purchase costs, the export leaves that column out and says so.
- Customers sharing a number. Customers added from a file are not checked for a shared mobile or tax number. Only the code must be new. See Customers.
Who can do it
Section titled “Who can do it”| Action | Admin | Manager | Cashier |
|---|---|---|---|
| Open Data Exchange, see History and saved imports | Yes | Yes | No |
| Download a template | Yes | Yes | No |
| Export customers, suppliers or products | Yes | Yes | No |
| Preview and apply a file that adds records | Yes | Yes | No |
| Update products from a file | Yes | Yes | No |
| Change or see Default Purchase Cost in a file | Yes | Yes | No |
| Have a file create new categories | Yes | Yes | No |
| Undo an import | Yes | No | No |
These are the standard roles. Your administrator can give a custom role each action separately in Settings › Users & access. Adding records also needs the right to create that kind of record. Updating products also needs the right to update products. Exporting also needs the right to see that kind of record. A custom role given the undo right can undo imports.
Fixing a mistake
Section titled “Fixing a mistake”- The preview shows errors. Nothing was created. Open the Errors tab, or press Download row errors, fix the file in your spreadsheet, then choose it again and press Preview file. The wrong file stays in History as Needs fixing.
- You see a wrong value in the preview. Do not apply. Correct the file, choose it again and preview again.
- You imported the wrong records and nothing uses them yet. An admin opens the import from History and presses Undo this import. All the records the file created are removed, and their codes are free again. Choose a corrected file and import it. Categories the file created stay.
- Some imported records are already used by a sale, purchase, opening balance or similar. Undo is refused and names them. Cancel or remove what uses them, or archive the records you no longer want, then press Check again.
- An update file changed products the wrong way. Undo does not restore earlier values. Prepare a corrected update file with the right values. A price change is saved with your reason, so the price history keeps both prices.
- One or two records are wrong. Edit them on their own screen: Products, Customers or Suppliers.
- The answer was lost. Press Check status. Do not choose the file again.
- You need starting stock or balances. They are not part of this screen. Use Opening data.