Import data via CSV files

For many DEAR functions, you can import large quantities of data using a CSV or TXT file created or exported from another system. 


Prerequisites:


Table of Contents


How to import a CSV file

[back to top]


On many DEAR screens, you will see the option to import data via 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 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 straight line separated by commas for each item) but working with data in columns using Excel makes the job easier.



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. 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 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 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. 


Column Information

[back to top]


Click the + at the top of the import screen to expand the column information for this CSV template. 


This will give you an explanation for all the columns of the template, including which columns are mandatory for the import to function. 



Tips for entering data

[back to top]


  • 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 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 ('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 ('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.



How to edit in bulk using CSV files

[back to top]


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 which can be exported in CSV format. 



Clicking any of the options open 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. 


Purchase Module

[back to top]


The following data can be imported from the Purchase module. Follow the navigation guide to find where to import via CSV.


DATA TO IMPORTNAVIGATION GUIDE
SuppliersPurchase -> Suppliers -> Import -> Suppliers
Supplier AddressesPurchase -> Suppliers -> Import -> Supplier Addresses
Supplier ContactsPurchase -> Suppliers -> Import -> Supplier Contacts
Product Prices by SupplierPurchase -> Suppliers -> Import -> Product Prices by Supplier
Purchase TasksPurchase -> 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.


Sale Module

[back to top]


The following data can be imported from the Purchase module. Follow the navigation guide to find where to import via CSV.


DATA TO IMPORTNAVIGATION GUIDE
CustomersSale -> Customers -> Import -> Customers
Customer AddressesSale -> Customers -> Import -> Customer Addresses
Customer ContactsSale -> Customers -> Import -> Customer Contacts
Custom PricesSale -> Customers -> Import -> Custom Prices
Sale TasksSale -> 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.


Inventory Module

[back to top]


The following data can be imported from the Purchase module. Follow the navigation guide to find where to import via CSV.


DATA TO IMPORTNAVIGATION GUIDE
Inventory ListInventory -> Products -> Import -> Inventory List
Stock on HandInventory -> Products -> Import -> Stock on Hand
Discount MatrixInventory -> Products -> Import -> Discount Matrix
Bill of MaterialsInventory -> Products -> Import -> Bill of Materials
Units of MeasureInventory -> Products -> Import -> Units of Measure
Stock Reorder LocationsInventory -> Products -> Import -> Stock Reorder Locations
Product Prices by SupplierInventory -> Products -> Import -> Product Prices by Supplier
Smart Reordering ConfigurationInventory -> Products -> Import -> Smart Reordering Configuration
Images and AttachmentsInventory -> Products -> Import -> Images and Attachments
Inventory -> Product Families -> Import -> Images and Attachments
Markup PricesInventory -> Products -> Import -> Markup Prices
Custom PricesInventory -> Products -> Import -> Custom Prices
Bulk SKU ChangeInventory -> Products -> Import -> Bulk SKU Change
Stock AdjustmentInventory -> Stock Adjustment -> Import
StocktakeInventory -> 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 ListInventory -> Stock Transfer -> Import. NOTE: You must select a Location before you can import a stock transfer list.
Inventory Write-OffInventory -> 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. 



Reference Books

[back to top]


DATA TO IMPORTNAVIGATION GUIDE
Chart of AccountsSettings -> Reference Books -> Chart of Accounts -> More -> Import
NOTE: Not available for QBO/Xero users.
Opening Balances - Stock on HandSettings -> Reference Books -> Opening Balances -> Stock on Hand tab -> Import Inventory List via CSV/Import Stock on Hand via CSV
Opening Balances - Fixed AssetsSettings -> Reference Books -> Opening Balances -> Fixed Assets tab -> Import from CSV
Shipping ZonesSettings -> Reference Books -> Shipping Zones -> Import

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.