Import, export and update information

To help you set up and create your records, you can:

  • Use an import file to create or update account records and transactions Sage 200.
  • Export accounts to a file, so you can update them in an external tool such as Excel, and then reimport back to Sage 200.

Using an import file is useful when you want to create or update a large number of records in one go, as it's much quicker than entering the records manually in Sage 200.

What can I import, export and update?

Area

Import

Export

Update

More information

Nominal

 

 

 

Nominal accounts

Yes

Yes 1

Yes 1

Import and export nominal account information

Nominal transactions

Yes

No

No

Import and export nominal account information

Nominal budget values

Yes

No

No

Import and export nominal account information

Cost centres

Yes

No

No

Import cost centres

Departments

Yes

No

No

Import department codes

Nominal report categories

Yes

No

No

Import nominal report categories

Fixed assets

Yes

No

No

Import fixed assets

Customers

 

 

 

Customer accounts

Yes

Yes 1

Yes 1

Import and export customer accounts

Customer transactions

Yes

No

No

Import customer transactions

Customer delivery addresses

Yes

No

No

Import customer delivery addresses

Suppliers

 

 

 

Supplier accounts

Yes

Yes 1

Yes 1

Import and export supplier accounts

Supplier transactions

Yes

No

No

Import supplier transactions

Stock and Prices

 

 

 

Stock items

Yes

Yes

Yes 1

Import and export stock items

Product groups

Yes

No

No

Import product groups

Stock opening balances

Yes

No

No

Import stock opening balances

Stock item locations

Yes 2

Yes 2

Yes 2

Import stock item locations (warehouses)

Add locations (warehouses) to stock items

Delete stock item locations (warehouses)

Export stock item locations (warehouses)

Cross-selling stock items

Yes 1

No

No

Import cross-selling stock items

Alternative stock items

Yes 1

No

No

Import alternative stock items

Stock item selling prices

Yes

Yes

Yes

Import and export selling prices

Stock item supplier prices

Yes

No

No

Import supplier prices

Bill of Materials

 

 

 

 

Bill of Materials

Yes

Yes

No

Import and export Bill of Materials

Project Accounting

 

 

 

 

Project timesheets

Yes

No

No

Import timesheets

Manufacturing

 

 

 

 

Employees, Expenses, Categories, Time, Make to Stock items

Yes

Yes

No

Import and export Manufacturing data

Drawings, Operations, Labour, Machine and Tool resources

Yes

Yes

No

Import and export Manufacturing data

1 *** Hidden old version references ***This feature is only available from Sage 200 Professional Spring 2019 version onwards.

2 This feature is only available from Sage 200 Professional 2023 R1 version onwards.

Importing information

All information is imported using either CSV or XML files. Example import files are provided for each type of import.

  • CSV files can be used to import most information, and these files can easily be created and edited in a text editor or Microsoft Excel.
  • XML files can be used to import some types of information, as an alternative to using CSV.
  • An import format spreadsheet (XLS/XSLX file) is provided with details about each import format. Read this before you create your import file, to help you create the file in the correct format.

Import rules

  • Data can only be imported once. If you try to import the same records again, the records will show as Invalid on the report.
  • The data must be in the correct format as outlined in the file structure.
  • Some imports have mandatory fields. These fields must be complete for all records.
  • Some imports rely on other records already existing in Sage 200. For example, if you're using cost centres and departments, these must be set up in Sage 200 before you can import nominal accounts. In the same way, product groups must exist before you can import stock records.
  • Valid records are imported even if the a file contains some invalid records.
  • Invalid records are shown on the invalid import report. You can then make the required changes and import again.

The correct order to import records

When importing records you need to make sure that you import in a specific order due to the dependencies between some of the records. You must import account and records before importing transactions, for example.

Validation reports

It's good idea when importing records to make sure all the records in your import file meet the rules. To do this you can choose to Validate your records before importing them. This checks the records in the files and produces two reports:

  • The Update Report details the records successfully validated or imported.
  • The Update Report (Invalid) details the records that failed and were not imported. For each failure a single failure reason is displayed.

Use them to identify and correct any records that were not imported.

Exporting and updating information

Some information can be exported to a CSV file, which can make updating the information easier if you need to make a lot of changes.

The information is exported to a CSV file in the same format used for an import. This means you can export information and make changes to it using an external tool (such as Excel), and then import your updated information back into Sage 200.

For details of which records you can export and update, see What can I import, export and update?

Updating information

  • An import file can include information for both new records and existing records that you want to update. You can choose whether or not to update existing records when you import.

  • Check the import format spreadsheet (XLS/XSLX file) for details about each import format.
  • Not all fields need to be included in the import file. You only need to include the mandatory fields.

    • If you do not want to include a field for a particular record, just leave that field blank.
    • If you do not want to include a field for any of the records in your import file, then you can remove the field altogether (including the heading).