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 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:

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

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 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 200 checks for the following:

  • Whether an item already exists.

  • All items contain the required mandatory fields.

  • All fields are correctly formatted.

The result of the validation is provided by reports.

  • A report lists the items that are valid and can be imported.

  • A separate report is produced to list any items that are invalid and can't be imported. The report will tell you why individual items are invalid.

Note: Depending on your selected output mode, the reports are displayed as a preview, sent to the spooler, or sent to the printer.

What happens when I import the file?

  1. The import file is validated.

  2. The import file is processed.

    All valid items are imported, and any invalid items are ignored.

  3. The result of the import is provided by reports.

    • A report lists the valid items that were successfully imported.

    • A separate report is produced to list any items that were invalid and could not be imported.

    Note: Depending on your selected output mode, the reports are displayed as a preview, sent to the spooler, or sent to the printer.

See also

Related information

Import, export and update information

Other useful information

Dealing with sales

Customer accounts