Import supplier transactions

Find this screen

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

How to

Create an import file for supplier transactions

You can import supplier transactions using either CSV Comma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. or XML Extensible Markup Language (XML) file format. Sage 200 can import and export data in the XML file format. file types.

  1. Download the import information and example files.

    For Sage 200 Professional version 2024 R2 onwards:

    For Sage 200 Professional up to version 2024 R1:

  2. Create your import file in the format of the 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 supplier 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 supplier 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 Purchase Ledger.

How are transactions imported?

Imported transactions are always posted to your Nominal Ledger and Purchase 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 supplier 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 supplier account and/or bank account:
      • The ChequeToBankExchangeRate should be 1 (one).
    • If the receipt or payment is in a different currency from the supplier account and/or bank account:
      • Specify the exchange rate in ChequeToBankExchangeRate.
      • Specify the amount of the receipt or payment in the currency of the supplier account in ChequeValueInChequeCurrency.

Reverse charge VAT

If you want to import purchase transactions that correctly handle reverse VAT charges, consider the example below.

You have the following data:

  • The transaction currency is euro.
  • The exchange rate is 1.637023.
  • The supplier is NAN001 from the Sage 200 Demo Data.
  • There is a gross amount of EUR 117.50 and a net amount of EUR 100.00.
  • There is a notional VAT element of EUR 17.50.

Within the CSV file before you import, you have to work out the Gross Control Value in GBP base currency.

In the example file provided, you will see this is GBP 71.78 (GBP 61.09 Net + GBP 10.69 VAT = GBP 71.78 Gross).

The Reverse VAT Charge Code is set up as 24 in the example, and is shown in the TaxAnalysisTaxRate/1.

When you import, this will update the VAT Return and Nominal Ledger with values of 61.09 net and 10.69 VAT.

The supplier account is updated with just EUR 100.00 which is correct and the notional VAT of 17.50 is behind the scenes.

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 character U for unauthorised transactions.

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/XLSX file) and add the mandatory field to your record.
    • If a field is incorrectly formatted: refer to the import file format (XLS/XLSX 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

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 supplier accounts

Reference

Import, export and update information