DEAR Purchase tasks CSV import

CSV File format

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.


Purchase tasks CSV file content

One file can contain information about multiple purchase tasks (up to 100 tasks per file). 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”.


Supported task parts and corresponding RecordType column values


Purchase Task part (detail)
RecordType column value
Invoice
Invoice
Invoice line
InvoiceLines
Invoice additional charge line
InvoiceAdditionalCharges
Payment
Payment
Stock Receive line
Received
Credit Note
N/A
Credit Note Number/Date are defined in Credit Note lines.
Credit Note line
CreditLines
Credit Note additional charge line
CreditAdditionalCharges
Refund
Refund
Unstock
Unstock


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. You can read detailed metadata information about all columns for each CSV line type in help tabs on the historical purchases CSV import page.


Validations

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.


Supplier/Address/Contact update

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


Import options

Invoice status

This setting specifies the status of the invoice for importing purchase tasks. 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.


Did you find it helpful? Yes No

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