search-icon

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

  • Draft sale invoice
  • Authorised sale invoice
  • Authorised pick 
  • Authorised pick and pack
  • Authorised pick, pack, and ship


Sale 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 sale tasks

[back to top]


Import your purchase tasks from the Sale → Sales screen, then clicking Import.


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


Select the Sale tasks tab and download the CSV template. DEAR allows you to import a lot of sale information, including invoice, payments, credit notes, refunds, and restock information. Pick/pack/ship information cannot be imported via CSV line, but can be created automatically from invoice lines during import. 


Each CSV file line (CSV line) contains information about one part of a sale task.  One file can contain information about multiple sale tasks, up to 100 tasks per file. 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 one payment line has been applied.


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 type, plus what information should be entered for each field. Mandatory fields are only mandatory for the specified record type.


At least two records per sale task (invoice, Invoice lines/invoice additional costs lines) are required to upload a task correctly. CSV lines are grouped in sale tasks by two mandatory fields: CustomerName (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:

  • Pick,Pack and Ship processing mode: Available options are No Picking (no pick lines will be created), Auto Pick (Pick lines copied from invoice and authorised), Auto Pick + Pack (Pick and pack lines copied from invoice and authorised), Auto Pick + Pack + Ship (Pick and pack lines copied from invoice and authorised, Pack auto-filled and authorised). Pick, pack and ship lines cannot be added to the CSV file to import, they can only be auto-created using this setting during import. 
  • Invoice status: This setting specifies the status of the invoice for importing sale tasks. Draft Invoice Status = DRAFT is only applicable for tasks that have no CreditNote/Restock/Refund steps. 
  • Set order backordered on low stock level: If this setting is enabled, sale will be created even if there is not enough stock to complete the sale order. The sale order will be created with backordered status. Any credit note/refund/restock steps will be skipped.  
  • 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: Sale orders can not be uploaded as drafts. The "minimum status" for an import is an authorised sale order with draft sale 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 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.


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

[back to top]


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 sale tasks via CSV to save time when uploading multiple sale orders at once. Each line in the CSV file corresponds to an sale invoice line or sale invoice additional charges line in the sale order. Imported sal orders will be imported with an authorised sale order, and optionally:

  • Draft sale invoice
  • Authorised sale invoice
  • Authorised pick 
  • Authorised pick and pack
  • Authorised pick, pack, and ship


Sale 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 sale tasks

[back to top]


Import your purchase tasks from the Sale → Sales screen, then clicking Import.


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


Select the Sale tasks tab and download the CSV template. DEAR allows you to import a lot of sale information, including invoice, payments, credit notes, refunds, and restock information. Pick/pack/ship information cannot be imported via CSV line, but can be created automatically from invoice lines during import. 


Each CSV file line (CSV line) contains information about one part of a sale task.  One file can contain information about multiple sale tasks, up to 100 tasks per file. 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 one payment line has been applied.


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 type, plus what information should be entered for each field. Mandatory fields are only mandatory for the specified record type.


At least two records per sale task (invoice, Invoice lines/invoice additional costs lines) are required to upload a task correctly. CSV lines are grouped in sale tasks by two mandatory fields: CustomerName (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:

  • Pick,Pack and Ship processing mode: Available options are No Picking (no pick lines will be created), Auto Pick (Pick lines copied from invoice and authorised), Auto Pick + Pack (Pick and pack lines copied from invoice and authorised), Auto Pick + Pack + Ship (Pick and pack lines copied from invoice and authorised, Pack auto-filled and authorised). Pick, pack and ship lines cannot be added to the CSV file to import, they can only be auto-created using this setting during import. 
  • Invoice status: This setting specifies the status of the invoice for importing sale tasks. Draft Invoice Status = DRAFT is only applicable for tasks that have no CreditNote/Restock/Refund steps. 
  • Set order backordered on low stock level: If this setting is enabled, sale will be created even if there is not enough stock to complete the sale order. The sale order will be created with backordered status. Any credit note/refund/restock steps will be skipped.  
  • 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: Sale orders can not be uploaded as drafts. The "minimum status" for an import is an authorised sale order with draft sale 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 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.


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

[back to top]


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.


Did you find it helpful? Yes No

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