You can import your data into DEAR in one go at any time using a CSV or TXT file you’ve created or exported from another system.

The following data can be imported into DEAR:

  • Suppliers
  • Supplier Addresses
  • Customers
  • Customer Addresses
  • Inventory List
  • Stock on Hand
  • Discount Matrix
  • Bill of Materials
  • Product prices by Supplier (latest and fixed prices, supplier skus, names and URLs)


All this information can be entered directly from the DEAR user interface however using CSV file import is less time consuming.


If you already have the data in DEAR you can import additional data, make changes to existing items or export your data.

*Please note, Stock on Hand import does not update existing stock on hand but rather adds additional stock on hand. To reduce stock on hand for a product use Inventory Adjustments module instead.

 

Import Data
Use the navigation below to import data:


DATA TO IMPORT
NAVIGATION
Suppliers
Purchase-> Suppliers -> View All and click on 'Import' -> 'Suppliers'
Supplier Addresses
Purchase-> Suppliers -> View All and click on 'Import' -> 'Supplier Addresses'
Customers
Sale -> Customers -> View All and click on 'Import' -> 'Customers'
Customer Addresses
Sale -> Customers -> View All and click on 'Import' -> 'Customer Addresses'
Inventory List
Inventory -> View All Products and click on 'Import' -> 'Inventory List'
Stock on Hand
Inventory -> View All Products and click on 'Import' -> 'Stock on Hand'
Discount Matrix
Inventory -> View All Products and click on 'Import' -> 'Discount Matrix'
Bill of Materials
Inventory -> View All Products and click on 'Import' -> 'Bill of Materials'
Product Prices by Supplier
Inventory -> View All Products and click on 'Import' -> 'Product Prices by Supplier'
or
Purchase-> Suppliers -> View All and click on 'Import' -> 'Product Prices by Supplier'


Create and import your data file


Even if you are not ready yet to upload your data file – you need go to the Import Data screen in order to download CSV template file you need to use to create a data file readable by DEAR.


1. Download template

 

Blank template

  • From the Data Screen click ‘Download Template File”. This will allow you to save or open a blank template containing the headings under which you need to enter or your data. The CSV template provided by DEAR will most likely open in Excel.

Export first & update

  • If you already have some data in DEAR and want to add more, or make some changes to the data you already have, you can export your data from DEAR in the CSV format ready for you to make updates.

2. Enter data

Whether you started with the blank DEAR template or have got a file of your data exported from DEAR or another system, enter or change data in the columns provided in the CSV file.


* As DEAR is being improved all the time some updates might affect file templates. So please make sure you are working with a current one.


Blank template

  • If you have started the blank template, make sure you enter or copy information form another exported file into the right columns on the template, one data entry per row.



You can still use a text-editing program to edit the file (use straight line separated by commas for each item) but working with data in columns using Excel makes the job easier.

Data exported from DEAR


  • If you’re updating a file that you’ve exported from DEAR make sure you keep the updated information within the same original columns.


Tips and rules for entering data


Enter your data to the CSV file one entry per row.


  • Ensure you save in CSV format (like 'somefilename.csv'). If in doubt when using Excel, save the file again ensuring it is saved as one of the CSV file types, e.g. CSV (MS-DOS) or CSV (Comma delimited).


  • DEAR will also accept comma separated values in a text-based file (created by a program like Notepad or Text Edit) when saved will have a name like 'somefilename.txt'.


  • You must leave the first row in place as the header row and all columns must all be present and in the same order as the template file or the file you've exported from DEAR. If you're copying data from another system, make sure you use this exact format. If you delete the header row DEAR will not import your file.


  • DEAR understands the order of the columns or comma separated values to determine which fields in DEAR to put the information you've entered.


  • The CSV file is essentially 'mapped' column or group of columns to fields in DEAR so by completing contact information column by column you're essentially 'filling in' the fields in DEAR automatically.


E.g. Name column goes to Name field; EmailAddress column goes to Email Address field; POAddressLine1, POAddressLine2, POAddressLine3 and POAddressLine4 all go to Street Address or PO Box field; POCity column goes to Town/City field and so on.


  • Please check the table below for mandatory fields.

DATA TO IMPORT

MANDATORY FIELDS

Suppliers
  • Name - each Supplier must have unique name. If Supplier has more than one contact, supplier's name may repeat and all contacts will be saved under the same supplier.
  • Currency
  • PaymentTerm
  • TaxRule
Supplier Addresses
  • Name
  • AddressType
  • AddressLine1
Customers
  • Name - each customer must have unique name. If customer has more than one contact, customer's name may repeat and all contacts will be saved under the same customer.
  • Currency
  • PaymentTerm
  • TaxRule
  • PriceTier
Customer Addresses
  • Name
  • AddressType
  • AddressLine1
Inventory List
  • ProductCode
  • Name
  • Costing Method
Stock on Hand
  • ProductCode
  • Quantity
  • AverageCost
  • Location
  • ReceivedDate_YYYYMMDD
Discount Matrix
  • SKU
  • DiscountRuleName
* CustomerName and CustomerTag fields are mutually exclusive
* Specify Yes in DeleteThisEntry field to instruct DEAR to delete this rule if it exists in the system, otherwise leave this field empty



  • If you enter more than one entry with the same unique value, regardless of whether all the other information is different, DEAR will treat them as duplicates and will just import the first of the entries with the same unique value. You cannot enter 2 items with the same unique value in DEAR either.


  • If you need to enter multiple contacts for a supplier/customer - you can create additional records with a supplier/customer with the identical name but different contact. In such instance the system will create an additional contact record instead of adding a new customer/supplier.


  •  The columns that require data to be entered in a specific format are the:


  • Email Address - If you enter an email address make sure it's in an email format using @ and dots (.)


  • Phone number fields - Enter full phone numbers with spaces between the different components of the phone number i.e. between the country code and area code, and between the area code and phone number itself. These are individual fields in DEAR and the number will be added into these fields depending on the spacing you've used in any of the phone or fax number fields on your file


  • Tax Rule - Make sure you use exactly the same name used in the Name field in the Tax Rates screen
    (Settings > Settings & Reference Books > Taxation Rules (“Financial” section).


  • Payment Term – Make sure you use exactly the same name used in the Payment Term description field in the Manage Payment Terms screen
    (Settings > Settings & Reference Books > Payment Terms (“Financial” section).


  • Website - this must have http:// at the beginning of the address, for example, http://www.xero.com.


  • Discount - enter the number to 2 d.p. and without the % sign.


  • Do not use commas in values of Inventory related templates.


  • All other columns can have any letters and numbers entered into them.



  • The order in which you enter your entries on the CSV file doesn't matter.


  • If you are using Excel to create your CSV file make sure you do not enter figures and symbols that could be automatically reconfigured by Excel to read as dates or numbers or some other formula as this data will be imported as is into DEAR.


  • If the mandatory field is empty or there are any other errors with your CSV DEAR will tell you about these on the Import Summary screen.


  • If you're working with a CSV file from another source and have opened it in Excel, make sure the comma separated data is spread across the columns under each relevant heading rather than all data displaying in the first column of the spreadsheet. Sometimes this happens by default if you open the CSV using Excel. To ensure the data displays correctly you might need to open Excel first then use the import feature in Excel to import the CSV data. If all your data appears as comma separated in the first column of the spreadsheet it will fail to import into DEAR. Use “Text to Columns” Excel Function to split data into Columns.



Update existing data using the CSV import


If you've exported existing data from DEAR into a CSV file, you can edit it and then import back into DEAR.

As well as the above guidelines for putting new entries into a CSV file, please take note of the following:

  • DO NOT USE THIS FEATURE TO REDUCE STOCK ON HAND. DEAR adds stock on hand from the imported file. You may use Stock on Hand import to add additional quantity, for reducing quantity – use Inventory -> Adjustments and Valuations module instead.
  • DO NOT CHANGE UNIQUE VALUES. If you would like to change the customer or supplier name or product’s SKU – use DEAR user interface to change this information directly. If you change these values in CSV file and import the file back into DEAR – a new entry will be created and the old entry won’t be deleted.


3. Locate and select the file to import

Click the Browse button to locate the CSV or TXT file you want to import into DEAR.

Browse your computer’s file system to find the CSV file you’ve saved on your computer.

  • Make sure you have saved the final version of the file you want to import.
  • DEAR will only accept a .csv or .txt file.

Click ‘Upload’ button to start uploading the data. If you would like to cancel upload – you can just leave the Import data screen.