DEAR Sale 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 make changes to this line; leave it as per template (it can be loaded from the historical sales import page).


DEAR ignores empty lines in importing files.


Sale tasks CSV file content

One file can contain information about multiple sale tasks (up to 100 tasks per file). Each CSV file line (CSV line) contains information about one part (detail) of a sale task. The type and meaning of a CSV line are defined in the first column “RecordType”.


Supported task parts and corresponding RecordType column values


Sale Task part (detail)

RecordType column value

Invoice/Order

Invoice

Invoice/Order line

InvoiceLines

Invoice/Order additional charge line

InvoiceAdditionalCharges

Payment

Payment

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

Restock lines

Restock lines are created automatically.


CSV lines are grouped in sale tasks by two mandatory fields: Customer (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 sales 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 sale.
  • TaxRule column should contain a description of an active Taxation Rule allowed for use in sales.
  • 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 CustomerCurrency; 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 BillingAddressLine1 columns should be specified.
  • ShippingCity and BillingCity columns should be specified.
  • ShippingProvince and BillingProvince columns should be specified.
  • ShippingPostcode and BillingPostcode columns should be specified.
  • ShippingCountry and BillingCountry columns should be a known/valid country name.


Invoice/Order lines validations

  • DropShip column – this flag can be set only for products with the Drop ship mode “Always” and “Optional”. When this flag is set, the product should have the ‘Last supplied by’ column defined. This flag cannot be unset for products with the Drop ship mode “Always”.
  • Account column should contain a code or name of an active account allowed for use in sale 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.
  • DropShip 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 sale Invoice/CreditNote additional charge lines.
  • Total column can be negative, but cannot be zero and should be equal to R2( ‘Price/Amount’*(100 - Discount)/100).


Invoice validations

Invoice should contain at least one invoice line with a stock product.

  • In a group of CSV lines with the same combination of Customer/InvoiceNumber there should be 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 sale invoice lines.
  • Terms column should contain a name of an active payment term.
  • StockLocation column should contain a description of a tenant location (used as the order location).
  • PriceTier column should contain a known price tier name.
  • SalesRepresentative can be any arbitrary string but expects the use of one of the sale contacts.
  • InvoiceDate column should contain a past date.


Payment validations

Payment lines can only be processed, if the importing option “Invoice Status” is set to “Authorised”.

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


Credit note validations

Credit note has no special record type (like Invoice). Credit Note Number and Credit Note Date can be specified in CSV lines with the type “CreditLines” or “CreditAdditionalCharges”.

Credit note cannot be processed when the importing option “Invoice Status” is set to “DRAFT” or when the importing option “Auto Fulfilment mode” is not set to “Pick+Pack+Ship”.

  • 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 be in the past.


Credit note lines validations

Credit note lines cannot be defined for a product that was ordered with a drop ship flag.

  • 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 sale Invoice/CreditNote additional charge 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.


Refund lines cannot be processed when the importing option “Invoice Status” is set to “DRAFT” or when the importing option “Auto Fulfilment mode” is not set to “Pick+Pack+Ship”.

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


Customer/Address/Contact update

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


On customer creation the following fields are filled:

  • Account Receivable – first available account receivable for the user
  • Customer Currency – obtained from the Invoice CSV line, CustomerCurrency column
  • Discount – obtained from the Invoice CSV line, Discount column
  • Name - obtained from the Invoice CSV line, CustomerName column
  • Payment Terms - obtained from the Invoice CSV line, Terms column
  • Sale Price Tier – first price tier
  • Taxation Rule - obtained from the Invoice CSV line, TaxRule column
  • Sale account – obtained from the Invoice CSV line, Account column.


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


Customer addresses can be updated or created during sale tasks import. The Shipping* and Billing* columns from the Invoice CSV line are used.


If the customer has the address line 1 which is equal to the ShippingAddressLine1 column, then this address will be updated; otherwise, a new address will be created for the Customer. The created address will have the type “Shipping”; this address will be marked as “default for type” if the Customer was also created during import.


If the customer has the address line1 which is equal to the BillingAddressLine1 column, then this address will be updated; otherwise, a new address will be created for the Customer. The created address will have the type “Billing”; this address will be marked as “default for type” if the Customer was also created during import.


Customer contacts can be created during sale tasks import. When a Customer has no contact with the name contained in the Invoice CSV line, CustomerContact column, then a new Customer Contact will be created and filled from the Invoice CSV line, CustomerContact, CustomerPhone columns.


Import options

Invoice status

Specifies the status of the invoice for importing sale tasks.

 

Auto Fulfillment mode

The user can choose this option from:

  • No Picking
  • Auto Pick
  • Auto Pick + Pack
  • Auto Pick + Pack + Ship.


This option controls automatic generation of Pick, Pack or Ship lines from order lines.


Set order backordered on low stock level

This option allows creating sales even if the stock level for a product is low. In future implementation tasks will not be created if:

  • the stock level is insufficient and this option is set to false;
  • the sale task has a CreditNote/Refund. 

See the Implementation Restrictions section.


Export imported tasks to Xero/QuickBooks

This option allows disabling synchronisation for imported tasks with the accounting application.


Implementation Restrictions

Currently a sale task is created even if the stock level for the product is low or the auto-assembly failed. The sale task is set to a Backordered state and the CreditNote/Refund parts are skipped.

Did you find it helpful? Yes No

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