Import and export stock items

Find this screen

Open: Stock Control > Utilities > Import and Export.

Open: Stock Control > Utilities > Import and Export > Export Stock Records.

Open: Stock Control > Utilities > Import and Export > Import Stock Extensions.

How to

Create an import file for stock items

You can import stock items 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.

    • The required mandatory fields will depend on whether you are creating a new account or updating an existing account.
    • Some fields can only be set when you create a new account, and can't be updated.
  4. Save your import file.

Create an import file for stock item extensions

If you import stock items from another system into Manufacturing, you can also import Manufacturing stock item details.

Note: You cannot import these unless you have imported the associated stock item records. Also, you cannot import data from Sage 50 Manufacturing.

Data is exported to and imported from an XMLExtensible Markup Language (XML) file format. Sage 200 can import and export data in the XML file format. file.

Note: Sage can provide a conversion utility to convert a CSVComma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. file into the required XML format. The conversion does not support the full range of values you can import in XML, but allows you to process key pieces of data. Ask your Business Partner for more information.

  1. Download the import information and example file:

  2. Create your import file in the format of the XML example file.
  3. Save your import file.

Validate your import file

Open: Stock Control > Utilities > Import and Export > Import Stock Records.

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 200cloud.
    • The Update Report (Invalid): Shows the invalid records that would not be imported into Sage 200cloud. 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 stock items

Open: Stock Control > Utilities > Import and Export > Import Stock Records.

  1. Select Validate and import records.
  2. Choose whether you want to update existing items:

    • If your import file contains existing items that you want to update, select Update stock record if it exists.
    • If you don't want to update existing accounts, don't select Update stock record if it exists.

    This feature is only available from Sage 200cloud Professional Spring 2019 version onwards.

  3. Choose what to do if items have new analysis codes or values that don't currently exist in Sage 200cloud.

    • To import items with new analysis codes or values, select Ignore Invalid Analysis Codes.

      If the code doesn't exist, the analysis code is not added to the stock item.

      If the code exists and the values are new, the new value is added to stock item.

    • If you don't select Ignore Invalid Analysis Codes and the analysis code or value doesn't exist in Sage 200cloud, the stock item isn't imported.
  4. Choose the warehouse(s) that new stock items will be associated with. The warehouse will be associated with new stock items only, it will not change the warehouses for existing stock items that you are updating.

    Note: You must select a warehouse, even if you are only updating stock items.

    Note: You can't import a stock item again for a different location (warehouse).

  5. Click OK.
  6. Browse to the import file you created, select it and then click Open.

    Sage 200cloud processes and imports the records. A report is produced that lists the imported valid records, and a second report is produced if there are any invalid records.

    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.

  7. Check the reports to ensure that your records have been successfully imported.

  8. Make any corrections required to the records in your import file.

    Tip: We recommend that you remove those records that were successfully imported. As they can't be imported a second time, this stops them cluttering up your report.

  9. Repeat until all records are successfully imported.

Import stock item extensions

You cannot import manufacturing stock extensions unless you have imported the associated stock item records.

Open: Stock Control > Utilities > Import and Export > Import Stock Extensions.

Note: You can also import manufacturing stock item extensions using Manufacturing System Manager > System Utilities.

  1. Select Validate and import records.
  2. Select Treat warnings as errors if you want to treat warning messages as error messages.

    If an error is found in a record, the record will not be imported. If you select this option, then a warning in a record will also prevent the record from being imported.

  3. Click Import.
  4. Select the import file and click Open.

    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.

Note: If failures have occurred, see the generated report, investigate further and re-enter the stock item or stock item extension details.

Export stock items

Use this to export stock items to a file.

You can then make changes to your items using an external tool (such as Excel), and then import your updated information back into Sage 200cloud.

Open: Stock Control > Utilities > Import and Export > Export Stock Records.

  1. Choose the stock items to export.

    • Single stock item: Export a particular stock item.

      Note: You can't select stock items that are inactive. To export inactive stock items, try exporting the product group that contains the item.

    • Single product group: Export all stock items that are in a particular product group.
    • All stock items: Export all stock items.
  2. Click OK.
  3. Specify a name for the export file and the required file type (CSV or XML), then click Save.

Useful info

About importing stock items

You can create stock items by importing data from another software package into Sage 200cloud, or from one company in Sage 200cloud to another.

You can validate the import file before committing the data to the Stock Control module or you can proceed with the import once the file has been prepared. To do this there are two options available during the import activity.

  • The Validate stock records only option checks the import file is correctly prepared. It checks the database identifiers are specified correctly and the data is compatible with existing data in your system's database, e.g. the cost centres and departments are valid. If invalid records are found, a report is generated detailing why the records have failed.
  • The Validate and import stock records option checks the import file is correctly prepared as previously described, and then creates the new stock item records from the valid data. Invalid records are removed during the validation process. A report is generated detailing successful records and those that have failed.

All stock item types (Stock, Miscellaneous, Service and Labour) can be created. The stock type is determined from the product group you specify for a stock item in the import file. Other settings that are not specified in the import file but are derived from the product group are the search categories, units of measure and costing method.

If you are using multiple locations, the stock item locations are not specified in the import file. During the import activity you are prompted to select the warehouse(s) you intend to store the items in.

If you intend to use batch/serial numbered items, you must ensure a product group enabled for numbered items is specified for the relevant stock items. You cannot enable batch/serial numbers for a stock item once it has been created.

Bill of material (BOM) details cannot be included with the stock item using this activity.

Successful records are immediately applied to the Stock Control module and marked as active. Where the import file specifies a valid supplier and/or alternative item for a stock item, those details are marked as preferred on the stock item record.

After completing the import activity, you may need to access the new stock item records and make minor changes to the settings. For example, assign further suppliers or alternative items to the stock record or activate the required search categories for individual items.

Note: You cannot add information to an existing account using import. The activity only creates new records for Sage 200cloud.

Importing items to multiple warehouses

When you import the stock items, you choose which warehouse(s) the items are stored in. You can choose multiple warehouses, but don't forget, that'll mean all the items in the CSV file will be stored at all the warehouses selected.

You can only import a stock item once, so once you've imported it to a warehouse location, you can't import that stock item again to add it to more warehouse locations.

So if you use multiple warehouse locations, we suggest that you organise the stock records into separate import CSV files for each warehouse location, or each group of warehouse locations. For example, you might have three import files with:

  • Stock items that are only in the Oxford warehouse location.
  • Stock items that are only in the Birmingham warehouse location.
  • Stock items that are in both the Oxford and Birmingham warehouse locations.

About exporting stock items

Stock items can be exported to a CSVComma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. or XMLExtensible Markup Language (XML) file format. Sage 200 can import and export data in the XML file format. file format. You cannot export stock transactions (movement history) or bill of material (BOM) details.

The stock items are exported in the same format used for an import.

Information not exported includes the stock type, search categories, units of measure, costing method, locations (warehouses and bins), attachments or the current status of the stock item (active or inactive).

Where a stock item has more than one supplier, the name of the preferred supplier is exported. Where the item has more than one alternative, the code of the preferred alternative item is exported.

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

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

Updating information

This feature is only available from Sage 200cloud Professional Spring 2019 version onwards.

  • 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, by selecting Update account if it exists 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).

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.