Import customer transactions

Find this screen

Open: Import Records > Customers > Import Transactions

How to

Create an import file for customer transactions

You can import customer transactions using a CSVComma 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:

  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 choose to validate the import file, which checks that the records are in the correct format before you import. Doing this doesn't import any information.

  1. Select Validate records only and click OK.

  2. Browse to the import file and click Open.

    Sage 200cloud checks the file and generates two reports; one lists the imported records, the other lists the invalid records. If there are invalid records, the reason for the failure is shown in the report.

    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 corrections required to the records in your import file.
  5. To re-validate your import file, repeat this process.

Import customer transactions

  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 200cloud 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.

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 200cloud will ignore the transaction in the file.

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.


FAQs

I have invalid records when I import

  1. Check the reason the import failed on the report and take the appropriate action:

    • If the record already exists: remove the record from your file.
    • If a mandatory field is missing: refer to the import file format (XLS file) and add the mandatory field to your record.
    • If a field is incorrectly formatted: refer to the import file format (XLS file) and amend the field so it adheres to the required format.
  2. Remove those records that were successfully imported from your import file, so they aren't imported again.
  3. After addressing all issues, import the file again.

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

What happens when I validate the file?

When you validate an import file Sage 200cloud checks for the following:

  • Whether any of the records already exist in Sage 200cloud.

  • Each record contains all mandatory fields.
  • All fields are correctly formatted.

Two reports are produced as part of the validation process:

  • One lists those records that passed the checks and can be imported.
  • The other lists records that failed validation and would not be imported into Sage 200cloud. For each record a single failure reason is provided.

What happens when I import the file?

  1. The import file is validated.
  2. All valid records are imported. Any invalid records are ignored.
  3. A report listing the imported records is produced.

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

Tip: Can't see the report? Check the spooler or your printer.