search-icon

Import purchase tasks via CSV to save time when uploading multiple purchase orders at once. Each line in the CSV file corresponds to an purchase invoice line or purchase invoice additional charges line in the purchase order. Imported purchase orders will be imported with an authorised purchase order, and optionally:

  • Draft purchase invoice
  • Authorised purchase invoice
  • Optional draft stock received lines.


Users will need the Sale: Purchase/Sale CSV Import permission to use this feature. This permission is not required to import purchase/sale order lines via CSV.


Purchase orders can not be uploaded as drafts. 


Table of Contents


CSV formatting information

[back to top]


CSV is a simple text format where each line corresponds to a data row and every data column is separated by a comma. If a data column contains a comma, all column content should be escaped with double quotes (example: 34, ”cell text, with comma”, 23042014). Values can be set individually to necessary columns, to do this you need to skip unnecessary columns by specifying the appropriate number of commas (example: ,,34,,,,,abc,,8,,). Last commas can be omitted (example: ,,34,,,,,abc,,8).


This format can be directly edited by table processors like MS Excel. 


DEAR requires that the first line of this file contains column headers. A header line is used to check that the CSV file is of an appropriate type. Do not amend the structure of the first line; leave it as per template (it can be loaded from the historical purchases import page).


DEAR ignores empty lines in importing files.


Upload purchase tasks

[back to top]


Import your purchase tasks from the Purchase → Purchases screen, then clicking Import.


NOTE: This action imports new purchase task lines only. This means new purchase tasks can be imported, or new lines (invoice lines, stock received lines, payment lines, credit note lines, refund lines and unstock lines) can be added to an existing purchase tasks. It is not possible to use this feature to edit existing information for the purchase order or purchase lines by exporting purchase task data as a CSV file, editing the data, and re-importing.


Select the Purchase tasks tab and download the CSV template. DEAR allows you to import a lot of purchase information, including (if applicable) stock received lines, payments, credit notes, refunds, and unstock information. 


Each CSV file line (CSV line) contains information about one part (detail) of a purchase task. The type and meaning of a CSV line is defined in the first column “RecordType”. For example, consider the following task. This task has an  authorised invoice with one invoice line, and authorised stock received with one stock received line, but no payments have been applied yet. One file can contain information about multiple purchase tasks (up to 100 tasks per file).


In CSV format, the task will look like this:


You can expand the Field Specification for the template to see which fields are mandatory or option for each record typel, plus what information should be entered for each field. Mandatory fields are only mandatory for the specified record type. 


At least two records per purchase task (invoice, Invoice lines/invoice additional costs lines) are required to upload a task correctly.  CSV lines are grouped in purchase tasks by two mandatory fields: Supplier (2nd column) + InvoiceNumber (3rd column). Lines can be present in a file in any desired order. The 4th and later columns may have different meaning and applicability for each CSV line type. 


When you have finished entering the task information, save the edited file and drag it to the upload section of the import page. The following import options are available:

  • Invoice status: This setting specifies the status of the invoice for importing purchase tasks. Draft Invoice Status = DRAFT is only applicable for tasks that have no CreditNote/Unstock/Refund steps. When there are stock received lines or the “Generate stock received from Invoice lines” option is chosen, the task will be created with “Stock first” mode, in all other cases the task will be created with “Invoice first” mode.
  • Generate stock received from Invoice lines: This option allows generating stock received lines from invoice lines. This option can be used only when the task has no Stock Received records in the CSV file. Stock received lines cannot be generated for the FEFO products. 
  • Export imported tasks to Xero/QuickBooks: This option disables synchronisation of imported tasks with the integrated accounting application. Only available for accounts with Xero/QBO connections. 


NOTE: Purchase orders can not be uploaded as drafts. The "minimum status" for an import is an authorised purchase order with draft purchase invoice. 

Validations

[back to top]


General validations

  • Product column for all lines except Invoice Additional Charge and Credit Note Additional Charge should contain an SKU of a non-deprecated Stock product.
  • Quantity column should be a non-zero positive and should be an integer if the line belongs to a product with the S/N costing method.
  • Tax column should have the same sign as the Total column and should be less than total (by absolute) in case of a tax inclusive purchase.
  • TaxRule column should contain a description of an active Taxation Rule allowed for use in purchases.
  • Price column should be rounded to 7 decimals.
  • Quantity column should be rounded to 4 decimals and should be in the range 0-10000000.
  • Discount column should be rounded to 2 decimals and should be in the range 0-100;
  • Total column should be rounded to 2 decimals.
  • CurrencyConversionRate column should be rounded to 5 decimals.
  • CurrencyConversionRate column should be skipped or should be 1 in case YourBaseCurrency is equal to SupplierCurrency; otherwise, it should be a non-zero positive.


Addresses validations

Every sale task should contain two addresses: Vendor address and Shipping address. These addresses should be specified in the CSV line with RecordType = “Invoice”.


  • ShippingAddressLine1 and VendorAddressLine1columns should be specified.
  • ShippingCity and VendorCity columns should be specified.
  • ShippingProvince and VendorProvince columns should be specified.
  • ShippingPostcode and VendorPostcode columns should be specified.
  • ShippingCountry and VendorCountry columns should be a known/valid country name.

Invoice lines validations

  • Account column should contain a code or name of an active account allowed for use in purchase invoice lines.
  • TaxRule column should be the same for lines with the same product.
  • Account column should be the same for lines with the same product.
  • Total column should be positive or zero and equal to R2( R7(‘Price/Amount’*(100 -Discount)/100))* Quantity).

Invoice Additional charges validations

  • Product column can be a description of a non-deprecated service product or just a text, but cannot be a description of a stock product.
  • Account column should contain a code or name of an active account allowed for use in purchase invoice lines.
  • Total column can be negative, but cannot be zero and should be equal to R2( ‘Price/Amount’*(100 - Discount)/100).


Invoice validations

  • In a group of CSV lines with the same combination of Supplier/InvoiceNumber should exist one and only one CSV line with the type RecordType=”Invoice”.
  • Account column should contain a code or name of an active account allowed for use in purchase invoice.
  • Terms column should contain a name of an active payment term.
  • StockLocation column should contain a description of a user location (used as the order location).
  • If the import mode InvoiceStatus = AUTHORISED then:
    • Invoice cannot be empty (an additional charge line or stock line should exist);
    • Additional charge line with an ASSET account should have at least one corresponding invoice line with the same ASSET account. This validation is performed only when at least one invoice line exists with the ASSET account.
  • ‘InvoiceDate/ExpireDate’ column should be in the past.


Payment validations

  • Account column should contain a code or name of an active account allowed for use in purchase payments.
  • DatePaid/DateReceived column should have a past date. 


Stock received lines validations

Stock received CSV lines with the same combination of Product, Batch SN, Location/bin, Expiry date, Receive date are merged into one Stock received line with quantity summed.

  • StockLocation column should be a location description or a location description and bin description in the format “location: bin”.
  • InvoiceDate/ExpiryDate column is mandatory for a product with the FEFO costing method.
  • Reference/Serial/Note and InvoiceDate/ExpiryDate columns should be empty for a product with the FIFO costing method.
  • Reference/Serial/Note column can be empty for a non-FIFO product, but in this case a Batch S/N will be auto-generated.
  • Stock Received lines should have the same set of products with invoice lines and should have the same total quantity per product.


Credit note validations

Credit notes do not have a special record type (like Invoice). Credit Note Number and Credit Note Date can be specified in CSV lines with type “CreditLines” or “CreditAdditionalCharges”.

  • CreditNoteDate column should be specified at least on one CSV line per task, and if it is specified multiple times, values should be the same.
  • CreditNoteNumber column should be specified at least on one CSV line per task, and if it is specified multiple times, values should be the same.
  • CreditNoteDate column should contain a past date.


Credit note lines validations

  • Product column. Credit note lines should contain only products that exist in invoice lines. Account and Taxation Rule for Credit Note Lines are obtained from corresponding invoice lines. 
  • Quantity column. The Total per product quantity in Credit Note lines should be less than or equal to the Total quantity of the same product in Invoice lines.
  • Total column should be positive or zero and equal to R2( R7(‘Price/Amount’*(100 - Discount)/100))* Quantity).


Credit note additional charges validations

  • Account column should contain a code or name of an active account allowed for use in purchase invoice lines.
  • Total column can be negative, but cannot be zero and should be equal to R2(‘Price/Amount’*(100 - Discount)/100).


Refund lines validations

Refund lines cannot be defined without credit note lines or credit note additional charge lines.

  • Account column should contain a code or name of an active account allowed for use for purchase payments.
  • DatePaid/DateReceived column should be in the past.


Unstock lines validations

All unstock lines with products that have a non-ASSET account in the invoice lines are ignored. 

Unstock lines cannot be defined without credit note lines or credit note additional charge lines.

  • StockLocation column should be a location description or a location description and bin description in the format “location: bin”.
  • InvoiceDate/ExpiryDate column is mandatory for a product with the FEFO costing method.
  • Reference/Serial/Note and InvoiceDate/ExpiryDate columns should be empty for a product with the FIFO costing method.
  • Reference/Serial/Note column is mandatory for a product with a non-FIFO costing method.
  • The same combination of Unstock line columns (Product, StockLocation, Reference/Serial/Note, InvoiceDate/ExpiryDate) should exist in Stock Received lines, and the total combination of unstock quantity should be less than or equal to the total combination of quantity in Stock Received lines.

Unstock lines are bound to the Stock Received lines with the same combination of {Product, Location, Batch S/N, Expiry Date} in StockReceived lines with the lowest (earliest) Date bound first.


Create a supplier, supplier address, or supplier contact during import

[back to top]


A supplier can be created or updated during purchase tasks import. First, the supplier is searched by Name (the name is obtained from the Invoice CSV line, SupplierName column). If the supplier is found, then only addresses will be created/updated, otherwise, a new supplier and addresses will be created. 


On supplier creation the following fields are filled:

  • Account Payable – first available account payable for the user
  • Supplier Currency – obtained from the Invoice CSV line, SupplierCurrency column
  • Name - obtained from the Invoice CSV line, SupplierName column
  • Payment Terms - obtained from the Invoice CSV line, Terms column
  • Taxation Rule - obtained from the Invoice CSV line, TaxRule column.


A Purchase Task cannot be imported if the Supplier already exists in a “DEPRECATED” state.


Supplier addresses can be updated or created during purchase tasks import. The Vendor* columns from the Invoice CSV line are used. If supplier address line1 is equal to the VendorAddressLine1 column, then this address will be updated; otherwise, a new address will be created for the Supplier. The created address will have the type “Billing”; this address will be marked as “default for type” if the Supplier was also created during import.


Supplier contacts can be created during purchase tasks import. When a Supplier has no contact with the name contained in the Invoice CSV line, SupplierContact column, then a new Supplier Contact will be created and filled from the Invoice CSV line, SupplierContact, SupplierPhone columns


Did you find it helpful? Yes No

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