For many DEAR functions, you can import large quantities of data using a CSV or TXT file created or exported from another system.
Table of Contents
- How to import a CSV file
- How to edit in bulk using CSV files
- Inventory List
- Stock on Hand
- Fixed Assets
- Discount Matrix
- Assembly BOM
- Production BOM
- Units of Measure
- Stock Reorder Locations
- Product Prices by Supplier
- Smart Reordering Configuration
- Images & Attachments
- Markup Prices
- Custom Prices
- Bulk SKU Change
- Can you undo an import?
- Can I use a CSV file to update information?
- Is it possible to export a partial inventory list?
How to import a CSV file
On many DEAR screens, you will see the option to import data via a CSV file, e.g. Suppliers from the Suppliers screen, Sale order lines from the Sale Order screen. See the module sections below to see what you can import for the different modules and where to import it from. Usually, you will see an Import button at the top of the screen – click it to reveal available options.
Clicking any of the options takes you to the import screen.
1. Download the template
The first step is to download the CSV file template for this data by clicking the template file button. This will prompt 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. You can still use a text-editing program to edit the file (use a straight line separated by commas for each item) but working with data in columns using Excel makes the job easier.
2. Enter the 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. The names and order of these columns must stay the same for DEAR to correctly import the file.
NOTE: As DEAR is being improved all the time, some updates might affect the file templates. Always make sure you are working with the current version of the template.
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. Save and close the file when all of the data has been entered.
3. Import the updated file
Click to upload or drop the updated CSV or TXT file into the upload field. DEAR will attempt to upload the new information. Any lines with errors will be flagged so they can be revised.
You may find during import that some lines are skipped or deemed invalid by DEAR. You can view the reason each line was skipped by clicking the error icon on each line. Alternatively, you can use Download invalid lines to export a CSV file of the skipped lines. Each skipped line may have multiple error reasons.
The invalid lines CSV file will show:
- the original columns of the imported template, e.g. Number, Balance.
- an Error column for common general errors that may affect this template.
- an error column for each original column, e.g. NumberError, BalanceError
Click Fields Specification to expand the column information or for this CSV template. This will give you an explanation for all the field of the template. Mandatory fields are displayed by default, but you can click Optional Fields to expand the view to these fields as well.
Tips 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.
- If you enter more than one entry with the same unique value (e.g. SKU), regardless of whether all the other information is different, DEAR will treat them as duplicates and will 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 a 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 > Reference Books > Taxation Rules in the '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 > Reference Books > Payment Terms in the '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 the 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.
How to edit in bulk using CSV files
Many places in DEAR allow you to edit data in bulk using CSV files. The process is very similar to importing data via CSV. Look for the Export button at the top of a screen – clicking it will bring up a list of data that can be exported in CSV format.
Clicking any of the options opens a download window for that option.
The existing data will be downloaded in a CSV template. Open the file, make your changes and save the file.
Next, click Import and choose the same option to be taken to the import screen where you can upload your edited CSV file.
The following data can be imported from the Purchase module. Follow the navigation guide to find where to import via CSV.
|DATA TO IMPORT
|Purchase → Suppliers → Import → Suppliers
|Purchase → Suppliers → Import → Supplier Addresses
|Purchase → Suppliers → Import → Supplier Contacts
|Product Prices by Supplier
|Purchase → Suppliers → Import → Product Prices by Supplier
|Purchase → Purchases → Import → Purchase Tasks
|Purchase Order lines (only for Simple Purchase and Advanced Purchase)
|Purchase → New → Simple Purchase/Advanced Purchase → Order tab. Click Import under the Add Products section.
Purchase → Purchases → [PO-XXXX] → Order tab. Click Import under the Add Products section.
|Purchase Invoice lines (only for Simple Purchase and Advanced Purchase)
|Purchase → New → Simple Purchase/Advanced Purchase → Invoice tab. Click Import under the Add Products section.
Purchase → Purchases → [PO-XXXX] → Invoice tab. Click Import under the Add Products section.
|Stock Received lines (only for Simple Purchase and Advanced Purchase)
|Purchase → New → Simple Purchase/Advanced Purchase → Stock Received tab. Click Import under the Add Products section.
Purchase → Purchases → [PO-XXXX] → Stock Received tab. Click Import under the Add Products section.
The following data can be imported from the Sale module. Follow the navigation guide to find where to import via CSV.
|DATA TO IMPORT
|Sale → Customers →Import → Customers
|Sale → Customers → Import → Customer Addresses
|Sale → Customers → Import → Customer Contacts
|Sale → Customers → Import → Custom Prices
|Sale → Sales → Import → Sale Tasks
|Sale Quote lines (only for Simple Sale and Advanced Sale)
|Sale → New → Simple Sale/Advanced Sale → Quote tab. Click Import under the Add Products section.
Sale → Sales → [SO-XXXX] → Quote tab. Click Import under the Add Products section.
|Sale Order lines (only for Simple Sale and Advanced Sale)
|Sale → New → Simple Sale/Advanced Sale → Order tab. Click Import under the Add Products section.
Sale → Sales → [SO-XXXX] → Order tab. Click Import under the Add Products section.
The following data can be imported from the Inventory module. Follow the navigation guide to find where to import via CSV.
|DATA TO IMPORT
|Inventory → Products → Import → Inventory List
|Stock on Hand
|Inventory → Products → Import → Stock on Hand
|Inventory → Products → Import → Discount Matrix
|Inventory → Products → Import → Assembly BOM
|Inventory → Products → Import → Production BOM
|Units of Measure
|Inventory → Products → Import → Units of Measure
|Stock Reorder Locations
|Inventory → Products → Import → Stock Reorder Locations
|Product Prices by Supplier
|Inventory → Products → Import → Product Prices by Supplier
|Smart Reordering Configuration
|Inventory → Products → Import → Smart Reordering Configuration
|Images and Attachments
|Inventory → Products → Import → Images and Attachments
Inventory → Product Families → Import → Images and Attachments
|Inventory → Products → Import → Markup Prices
|Inventory → Products → Import → Custom Prices
|Bulk SKU Change
|Inventory → Products → Import → Bulk SKU Change
|Inventory → Stock Adjustment → Import
|Inventory → Stocktake. NOTE: There is an import option for both the Zero stock and Non-zero stock tabs. The Stocktake must be started to make these visible.
|Stock Transfer List
|Inventory → Stock Transfer → Import. NOTE: You must select a Location before you can import a stock transfer list.
|Inventory → Inventory Write-Off. Select import from the inventory write-off tab. NOTE: You must select a Location before you can import an inventory write-off list.
|Inventory → Gift Cards → Import