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:
- Supplier Addresses
- Supplier Contacts
- Customer Addresses
- Customer Contacts
- Inventory List
- Stock on Hand
- Discount Matrix
- Bill of Materials
- Product prices by Supplier (latest and fixed prices, supplier SKUs, names and URLs)
- Custom Prices
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 adds additional stock on hand. To reduce stock on hand for a product use the Inventory Adjustments module instead.
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'|
|Supplier Contacts||Purchase-> Suppliers -> View All and click on 'Import' -> 'Supplier Contacts'|
|Customers||Sale -> Customers -> View All and click on 'Import' -> 'Customers'|
|Customer Addresses||Sale -> Customers -> View All and click on 'Import' -> 'Customer Addresses'|
|Customer Contacts||Sale -> Customers -> View All and click on 'Import' -> 'Customer Contacts'|
|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'|
Purchase-> Suppliers -> View All and click on 'Import' -> 'Product Prices by Supplier'
|Custom Prices||Inventory -> View All Products and click on 'Import' -> 'Custom Prices'|
Create and import your data file
Even if you are not ready yet to upload your data file – you need to go to the Import Data screen in order to download the CSV template file you need to use to create a data file readable by DEAR.
1. Download 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 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.
- If you have started the blank template, make sure you enter or copy information from 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) which 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 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 in which fields in DEAR to put the information you've entered.
- The CSV file is basically 'mapped' column by column to fields in DEAR so by filling in contact information, column by column, you're actually '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|
|Stock on Hand|
* CustomerName and CustomerTag fields are mutually exclusive
* Enter 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 an 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 following:
- 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 payment term 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 a mandatory field is empty or there are any other errors in 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 a 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 the '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 the DEAR user interface to change this information directly. If you change these values in a 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 the ‘Upload’ button to start uploading the data. If you would like to cancel upload – you can just leave the Import data screen.