Import customer transactions

Find this screen

Open: Sales Ledger > Utilities > Import > Import Transactions.

How to

Create an import file for customer transactions

You can import customer transactions using a CSV Comma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. file.

  1. Download the import information and example file.

    For Sage 200 Professional version 2024 R2 onwards:

    For Sage 200 Professional versions up to version 2024 R1:

  2. Create your import file in the format of the CSV example file.

  3. Read the information in the import format file (XLS), and make sure your information is correct and all mandatory fields are included.

    Note: If you are importing foreign currency transactions, make sure that you enter the exchange rate in the DocumentToBaseCurrencyRate.

  4. Save your import file.

Validate your import file

You can validate the file to make sure all the records are in the correct format and have the required mandatory fields. No records are imported

  1. Select Validate records only and click OK.

  2. Browse to your import file, select it and then click Open.

    The file is checked and two reports are generated:

    • The Update Report: Shows the valid records that would be successfully imported into Sage 200.
    • The Update Report (Invalid): Shows the invalid records that would not be imported into Sage 200. The reason for the failure is shown in the report, and there may be more than one reason for the failure.

    Note: The reports are displayed as a preview, printed, or sent to the spooler. This depends on the Output mode you have set.

    Note: If you get a message "Unable to process the specified file", make sure your import file is closed and isn't open in any applications.

  3. Check both reports to ensure that your records are complete and correct.

  4. Make any required corrections to the records in your import file.
  5. To revalidate your import file, repeat this process.

Import customer transactions

Note: Ensure that your import file is valid before you import it into Sage 200.

  1. Select Validate and import records.

  2. Select the Update Cash Book option only if:
    • There are transactions in the import file that you want to post to a bank account or other Cash Book account.
    • There are foreign currency transactions in the import file.
  3. Click OK.

  4. Browse to your import file, select it and click Open.

    Sage 200 validates the contents of your import file, imports valid records and generates two reports:

    • A list of the valid records that were successfully imported.
    • A list of records that failed the validation checks so were not imported.

    Note: The reports are displayed as a preview, printed, or sent to the spooler. This depends on the Output mode you have set.

    Note: If you get a message "Unable to process the specified file", make sure your import file is closed and isn't open in any applications.

  5. Make the required changes to the import file. We advise you remove the details of the transactions that were successfully imported before you attempt to import the file again.
  6. Repeat this process to import any corrected transactions.

Note: The import may take a long time to complete if you have a large number of items in the import file. For example, it might take 1 minute per 1,000 items in the import file. Therefore, it's a good idea to remove any unnecessary items from the import file. So if you're only updating 10 items in a file that has 10,000 items, then edit the import file so it only contains those 10 items.

Useful info

What can I import?

You can import the following customer transactions (in base and foreign currency):

  • Invoices (including opening balance invoices).
  • Credit Notes (including opening balance credit notes).
  • Payments.
  • Receipts.
Note:
  • You cannot add information to existing transactions.
  • No check is made to see whether the transactions already exist in the Sales Ledger.

How are transactions imported?

Imported transactions are always posted to your Nominal Ledger and Sales Ledger. You can choose to post the transactions to your bank account or other Cash Book account.

Note: If your import file contains foreign currency payments or receipts you must select the Update Cash Book option on the import screen or these transactions will not be imported.

Remember that you cannot update existing transactions with an import. If you attempt to import a transaction that is already present on the system, Sage 200 will ignore the transaction in the file.

Mandatory fields

Different identifiers are mandatory depending on the transaction type you are importing.

  • The AccountNumber, TransactionDate, SYSTraderTranType, DocumentToBaseCurrencyRate, DocumentToAccountCurrencyRate and GoodsValueInAccountCurrency identifiers are mandatory.

  • You can have an unlimited number of VAT amounts. The first is mandatory. If VAT is split over two VAT codes, the first should have the information for one VAT code and the second should have the information for the other VAT code.

  • Nominal Analysis.

    You can have an unlimited number of account numbers, per transaction, for invoices and credit notes. The first is mandatory, to ensure correct postings to the nominal ledger. The other is optional.

  • The AccountNumber, TransactionDate, SYSTraderTranType, GoodsValueInAccountCurrency,CBAccountNumber identifiers are mandatory.

    • Note: If you are importing foreign currency receipts or payments, enter the GoodsValueInAccountCurrency amount of the receipt or payment in the currency of the customer account.

  • VAT is not applicable when importing receipts or payments.
  • Nominal Analysis is not applicable when importing receipts or payments in base currency. The Nominal Analysis fields must not be populated in the CSV file.
  • If importing foreign currency payments or receipts, the following must be entered:
    • The currency of the foreign currency receipt or payment - ChequeCurrencyName must be the same as the currency Name in the Exchange rate table.
    • If the receipt or payment is in the same currency as the customer account and/or bank account:
      • The ChequeToBankExchangeRate should be 1 (one).
    • If the receipt or payment is in a different currency from the customer account and/or bank account:
      • Specify the exchange rate in ChequeToBankExchangeRate.
      • Specify the amount of the receipt or payment in the currency of the customer account in ChequeValueInChequeCurrency.

Importing transactions with query flags

Query flags are used to just a way of marking a transaction as under investigation and to prevent further actions, such as allocating it, until the query is resolved and the flag removed.

You can use any letter for the query flag apart from the reserved characters W for bad debt write off, and F for finance charges.

Questions

I get a 'Bank charges cannot be entered' error message

This occurs when you are importing base currency receipts or payments and have entered a value in the Nominal Analysis fields.

Remove any values in the following fields for these transaction types and import again:

  • NominalAnalysisTransactionValue/1
  • NominalAnalysisNominalAccountNumber/1
  • NominalAnalysisNominalCostCentre/1
  • NominalAnalysisNominalDepartment/1
  • NominalAnalysisNominalAnalysisNarrative/1
  • NominalAnalysisTransactionAnalysisCode/1

What happens when

The transactions are imported?

  1. The import file is validated.

  2. All valid records are imported and posted. Any invalid records are ignored.

    • If you have chosen to Update the Nominal Ledger immediately in Accounting System Manager System Settings Operational settings tab, current transactions are posted to the relevant nominal ledger accounts. All future transactions are posted to the Waiting postings file.

    • If you have not chosen to update the nominal ledger immediately, all transactions are posted to the Waiting postings file.

    • Each transaction is given a URN.

    Note: Import of transactions with Cost Centre and Department combinations that do not exist in Sage 200 are not validated. Postings for such transactions will go to the Suspense account.

  3. A report listing the imported records is produced.

  4. If any records haven't been imported, these are shown on a second report.

See also

What do you want to do?

Import and export customer accounts

Reference

Import, export and update information