Import and export stock items

Find this screen

Open: Import Records > Stock and Prices > Import Stock Records

Open: Export Records > Stock and Prices > Export Stock Records

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.

Validate your import file

Open: Import Records > Stock and Prices > Import Stock Records

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 stock items

Open: Import Records > Stock and Prices > 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.
  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.

    Sage 200cloud checks the records, imports the valid ones and generates two reports. One lists the imported records, the other lists the invalid records.

  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.

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: Export Records > Stock and Prices > 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

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.

Stock analysis codes

For your stock items, you can use analysis codes as a way of classifying your stock items so that you distinguish between them.

For example, you could use analysis codes to record the commodity code of a stock item. Although the stock item record has a commodity code option, this is specifically designed to be used for the commodity code used on an Intrastat declarations. Analysis codes are a good way of adding other commodity codes such as the ones used for export documents.

Another example where you use the codes to distinguish how they are stored. If you keep some items in ambient storage and others in a cold store, you use analysis codes to distinguish between these items. You could then run reports to make sure that you cold store items are located in your cold store warehouse.

When you import stock items, you can include analysis code values for each stock item. If you're using a new values in your import file, make sure you choose Ignore Invalid analysis codes.

Tip: Be careful if you mistype a value in the import file, as that mistyped value will be added to the record.

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

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