Import/Export Customers and Products

Updated 26/02/2024 Import/Export Settings Menu

[Work in Progress]

In the Settings menu, the Import/Export option enables you to:

  • Import new Customers and Products, or update existing ones from a CSV file
  • Export your existing Customers and Products in CSV file format

Both importing and exporting are done via comma-separated values (CSV) files, where the first line is a header line containing valid field names. In each import/export pair, the same field names are used for both the import and the export. This means:

  • If you currently have no customers and/or no products, you can use Export to create a CSV file that contains only the valid field names
    • This may be useful for ensuring the correct field names are used in the header line when you are importing
  • If you currently have customers or products, you can do a bulk update by exporting your current data, updating the exported file, and then re-importing the updated file
    • For example, if you have a price change for many products on the same date, you could export your current products, update the Price Starting Date and prices for each product with a price change, and re-import the products to create a new price list.

Overview Importing Customers Importing Products Using Export/Import for Bulk UpdatesTop

 

Overview

  • Select Import/Export from the Settings menu
  • Use the tabs on the left of the screen to switch between importing/exporting Customers and importing/exporting Products

  • Importing
    • Create or update the CSV file to be imported: 
      • Use spreadsheet software such as Excel to create a new CSV file, or to edit a CSV file you have exported from EasyVend or other software
      • The first row must be a header line containing valid field names spelt exactly as detailed in Importing Customers or Importing Products below
        • Field names that are misspelt or contain spaces, or are otherwise not one of the valid field names listed, will be ignored when importing
        • Some fields are mandatory, but most fields are optional
          • When adding new customers or products, missing fields will result in blank or default values in EasyVend
          • When updating existing customer or products, missing fields will leave the value of that field unchanged in EasyVend
      • Each row after the header row will contain the data for one customer or product that is to be added or updated
        • Existing customers in EasyVend will be matched by the Customer Code in the CustomerCode field of the file being imported
        • Existing products in EasyVend will be matched by the Product Code in the ProductCode field of the file being imported
        • If a match is not found, then a new customer or product will be created. Otherwise, the existing customer or product will be updated
      • Save the file in comma-separated values (CSV) format
    • Make sure that any prerequisites in EasyVend, as detailed in Importing Customers and Importing Products below, have been fulfilled
    • In EasyVend, select the file to be imported
      • Click Choose File
      • Select the .csv file you have just created or updated
      • Once a file has been selected, the Import Data button will become active
    • Click Import Data

    • A status window is displayed once the import is finished:
      • The window displays:
        • Number of records inserted
        • Number of records updated
        • Number of records that failed to import
          • The failed records and the reason(s) for each failure are listed in the Records Not Imported section
        • Total number of records in the file

    • If records have failed to import, you can re-import the file after:
      • Adding any information that is missing in EasyVend (such as a missing Round or Price List),
        and/or
      • Re-editing the file you are importing to correct or add data

  • Exporting
    • Select the Customers or Products tab on the left, as appropriate
    • Click the Export Data button on the top right of the screen
    • Select the location where you want the file to be saved
    • Click Save

    • All your current Customers or Products will be exported to a CSV file
    • The first row of the exported file will be a header record containing all the field names listed in Importing Customers or Importing Products
    • Each row after the header row will contain the data for one customer or product in EasyVend

 

Importing Customers

Prerequisites in EasyVend

Before importing customers, you must first ensure the following prerequisites are met in EasyVend:

  • Rounds  - at least one Round must exist in EasyVend and Round Codes must match
    • You must have at least one Round in EasyVend General Settings before customers can be imported
    • When present in the file being imported, a Round Code in the the Rounds field must match exactly a Round Code that is already present in EasyVend
      • If the import file contains a Round Code that is not present in EasyVend, the record will be rejected and that customer will not be added/updated
    • If the Rounds field in the file being imported is missing or blank, the first Round in EasyVend will be assigned to the customer by default
  • Customer Groups - if importing customer records that have Customer Group codes, Customer Group Codes must match
    • If you are importing customers records that contain a Customer Group Code, that Customer Group Code must already exist in EasyVend
  • Price Levels - at least one Price Level must exist in EasyVend and Price Level Descriptions must match
    • You must have at least one Price Level in EasyVend General Settings before customers can be imported
    • When present in the file being imported, a Price Level description in the PriceLevel field must match exactly a Price Level Description that is already present in EasyVend 
      • If the import file contains a Price Level Description that is not present in EasyVend, the record will be rejected and that customer will not be added/updated
    • If the PriceLevel field in the file being imported is missing or blank, the first Price Level in EasyVend will be assigned to the customer by default
  • Suppliers - if importing Supplier customers, Supplier Codes must match
    • If you are importing Supplier customers (customers  with a Billing Method of "Central Billing" or "Fee for Service"):
      • The Supplier customer record being imported must also contain a Supplier Code in the Supplier field
      • That Supplier Code must already exist in EasyVend Suppliers

Matching existing customer records

Customer Import can be used to both add new customers and update existing customers. Existing customers in EasyVend will be matched by the CustomerCode field in the file being imported:

  • If the CustomerCode value in the import file matches a Customer Code that already exists in EasyVend, that customer will be updated
  • Otherwise, if no matching Customer Code is found, a new customer will be created

CSV Import File Requirements

  • A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values when viewed in a text editor.
  • For files imported into EasyVend, the first line must be a header line containing valid field names separated by commas
    • Field names in the header line must be spelt exactly as listed below
    • Field names that are misspelt or contain spaces, or are otherwise not one of the valid field names listed, will be ignored when importing
    • This means, for example:
      • If you use a field name of "Post Code" instead of "Postcode", no postcodes will be imported
      • On the other hand, if you have exported data from other software and the exported file contains field names that are not recognised by EasyVend, those unrecognised extra fields do not need to be deleted, as they will simply be ignored when importing the file into EasyVend
  • Each line of the file after the header line is a data record. Each record consists of one or more fields, separated by commas

Mandatory Fields:

  • The CustomerCode and Name fields must exist in the file being imported
  • If you are importing customers  with a Billing Method of "Central Billing" or "Fee for Service", both the BillingMethod and Supplier fields must also exist

Valid Field Names

  • CustomerCode (Maximum Length: 20)
    • Mandatory
      • If this column is missing or empty, the file will not be imported
    • Existing records will be matched by the CustomerCode field
      • If a match is NOT found, a new customer will be created
      • Otherwise, the existing customer will be updated
  • Name (Max Length: 100)
    • Mandatory
      • If this column is missing or empty, the file will not be imported
  • Email (Max Length: 100)
  • BillingEmail (Max Length: 100)
  • Phone (Max Length: 20)
  • Fax (Max Length: 20)
  • Contact (Max Length: 50)
  • Mobile (Max Length: 20)
  • Mobile2 (Max Length: 20)
  • ContactBySMS
    • Allowed values: TRUE | FALSE
    • Default value when adding a customer: FALSE
  • DeliverInvByEmail
    • Allowed values: TRUE | FALSE
    • Default value when adding a customer: FALSE
  • DeliverInvByPrint
    • Allowed values: TRUE | FALSE
    • Default value when adding a customer: FALSE
  • Rounds
    • When present, must contain a single Round Code that matches an existing Round Code in EasyVend
      • If the Round Code is not found in EasyVend, the customer record will not be added/updated
    • When adding new customers, if the Rounds field is missing or blank, the first Round in EasyVend will be assigned to the customer by default
  • CustomerGroup
    • When present, must contain one or more Group Codes that match existing Group Codes in EasyVend
      • Multiple Group Codes can be assigned to the customer separating the codes with a comma (",")
  • PriceLevel
    • For the PriceLevel field, please enter the Price Level Description. The value needs to match with an existing Price Level in EasyVend Web.
    • In the case of a new customer, if the PriceLevel value is missing, Price Level 01 will be assigned to the customer.
  • ABN_ACN (Max Length: 20)
  • Address (Max Length: 100)
  • City (Max Length: 50)
  • State (Max Length: 50)
  • Postcode (Max Length: 10)
  • BillingAddress (Max Length: 100)
  • BillingCity (Max Length: 50)
  • BillingState (Max Length: 50)
  • BillingPostcode (Max Length: 10)
  • BillingMethod
    • For the BillingMethod field, valid values are: "Trade", "Central Billing" or "Fee for Service"
    • Only one value is accepted. If an invalid value is provided, the customer will not be created/updated
    • In the case of a new customer, if the value is missing, the Billing Method will default to "Trade"
    • BillingMethod will also set the Customer Type
      • If BillingMethod is "Trade", Customer Type will be 'Vendor Customer'
      • If BillingMethod is "Central Billing" or "Fee for Service", Customer Type will be 'Supplier Customer'
    • For existing Supplier customers, Central Billing can be changed to Fee for Service only and vice-versa
    • For existing Vendor customers, Billing Method will always be Trade, any other method provided will prevent record from being updated
  • PaymentMethod
    • For the PaymentMethod field, valid values are: "Cash", "Debit Credit Card", "Debit Bank Account", "Cheque", "Electronic" or "Other"
      • If the value is invalid, the record will not be imported
    • In the case of a new customer, if the value is missing, the Payment Method will default to "Cash".
  • TaxExempt
    • Allowed values: TRUE | FALSE
      • If the value is invalid, the record will not be imported
    • Default value when adding a customer: FALSE
  • DaysBtnInvoice
    • Valid values for Days Between Invoices are:
      • "1" - Daily
      • "7" - Weekly
      • "14" - Fortnightly
      • "28" - Four-weekly
      • "99" - Monthly
      • "100" - End of Month
    • In the case of a new customer, if the value is missing it will default to 7
  • NextInvoiceDate
  • CreditTerm
    • in the case of a new customer, if the value is missing it will default to 7
  • CreditLimit
    • in the case of a new customer, if the value is missing it will default to 0
  • Note (Max Length: 4000)
  • RunSheetNotes (Max Length: 1000)
  • Supplier
    • Please enter the Supplier Code
      • This only needs to be populated when the BillingMethod is "Central Billing" or "Fee for Service".
      • The Supplier field will NOT be updated for existing customers - any value provided will be ignored
  • SupplierAccountNumber (Max Length: 20)
  • InActive
    • Allowed values: TRUE | FALSE
    • Default value when adding a customer: FALSE 

 

Importing Products

Prerequisites in EasyVend

Before importing products, you must first ensure the following prerequisites are met in EasyVend:

  • Suppliers  - at least one Supplier must exist in EasyVend and Supplier Codes must match
  • Product Categories - at least on Product Category must exist in EasyVend and Product Category Descriptions must match
    • You must have at least one Product Category in EasyVend General Settings before products can be imported
    • When present in the file being imported, a Product Category Description in the the ProductCategory field must match exactly a Product Category Description that is already present in EasyVend
      • If the import file contains a Product Category Description that is not present in EasyVend, the record will be rejected and that product will not be added/updated
    • If the ProductCategory field in the file being imported is missing or blank, the first Product Category Descro[topm in EasyVend will be assigned to the product by default

Matching existing product records

Product Import can be used to both add new products and update existing products. Existing products in EasyVend will be matched by the ProductCode field in the file being imported:

  • If the ProductCode value in the import file matches a Product Code that already exists in EasyVend, that product will be updated
  • Otherwise, if no matching Product Code is found, a new product will be created

CSV Import File Requirements

  • A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values when viewed in a text editor.
  • For files imported into EasyVend, the first line must be a header line containing valid field names separated by commas
    • Field names in the header line must be spelt exactly as listed below
    • Field names that are misspelt or contain spaces, or are otherwise not one of the valid field names listed, will be ignored when importing
      • If you have exported data from other software and the exported file contains field names that are not recognised by EasyVend, those unrecognised extra fields do not need to be deleted, as they will simply be ignored when importing the file into EasyVend
  • Each line of the file after the header line is a data record. Each record consists of one or more fields, separated by commas

Mandatory Fields:

  • The ProductCode, ProductName and Supplier fields must exist in the file being imported

Valid Field Names

  • ProductCode (Maximum Length: 20)
    • Mandatory
      • If this column is missing or empty, the file will not be imported
    • Existing records will be matched by the ProductCode field
      • If a match is NOT found, a new product will be created
      • Otherwise, the existing product will be updated
  • ProductName (Max Length: 80)
    • Mandatory
      • If this column is missing or empty, the file will not be imported
  • SupplierProductCode (Max Length: 20)
    • For products from a Standard Supplier, the SupplierProductCode must be entered correctly to match with the Supplier Product
  • Supplier
    • Mandatory
      • If this column is missing, the file will not be imported
    • Please enter the Supplier Code
    • The value must match an existing Supplier Code in EasyVend
      • If the value is missing or the Supplier is not found, the record will not be imported
    • This value will NOT be updated for existing Products
  • ProductCategory
    • Please enter the Product Category Description
    • When present, the value must match an existing Product Category Description in EasyVend
      • If a match for the ProductCategory is not found, the product will not be created/updated
    • If no ProductCategory is entered, it will default to the first Product Category in EasyVend
  • BarCode (Max Length: 20)
  • UnitsPerCrate (Allowed Values: 0 to 32767)
  • DisplaySequence (Allowed Values: 0 to 99999999)
  • Literage (Allowed Values: 0 to 99999999)
  • Reserve (Allowed Values: 0 to 32767)
  • UOM (Max Length: 20)
  • Size (Max Length: 20)
  • Picking Location (Max Length: 20)
  • GrossWeight (Allowed Values: 0 to 99999999)
  • TaxExempt
    • Allowed values: TRUE | FALSE
    • Default value when adding a product: FALSE
  • StartingDate
    • When one of StartingDate, CostPriceEx and ListPriceEx is present, all three must be present
      • If at least one of the fields has a value then all of the fields are required, otherwise the record will not be created/updated
    • These fields are required for adding a price change, or for updating the product's existing price
    • StartingDate is the date on which the prices become effective in "DD/MM/YYY" format
      • The date from which a product's prices are effective is matched based on the StartingDate value provided
        • If a matching price starting date is found, the existing prices will be updated
        • Otherwise, a new price record will be created for the product
    • If none of the fields have a value, then:
      • A new product will be created without a price record, or
      • An existing product will be updated without a change in pricing
  • CostPriceEx (see StartingDate)
  • ListPriceEx (see StartingDate)
  • Price01, Price02 ... through to Price24
    • Where present, must contain a valid number with up to 4 decimal places
    • Where Price Levels are left empty, they will be calculated automatically
  • SubjectToCDS
    • Allowed values: TRUE | FALSE
    • Default value when adding a product: FALSE
  • InActive
    • Allowed values: TRUE | FALSE
    • Default value when adding a product: FALSE

 

Using Export/Import for Bulk Updates

If you currently have customers or products, you can do a bulk update by exporting your current data, updating the exported file, and then re-importing the updated file.

For example, if you have a price change for many products on the same date:

  • Export your current products from EasyVend
  • Open the exported file in your spreadsheet software
  • Optionally, you could delete the rows for products that do NOT have a price change, to make the editing process easier
  • For each product with a price change:
    • Update the StartingDate field with the Starting Date of the new price list
    • Update the CostPriceEx and ListPriceEx to the new price
  • Save the updated CSV file
  • Re-import the updated products to create a new price list effective from the starting date you entered