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

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

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

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.

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

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

About importing stock items

You can create stock items by importing data from a CSV file.

All stock item types (Stock, Miscellaneous, Service/Labour) can be created. The stock type is determined from the product group you specify for the stock item in the import file.

The imported stock items are marked as active and available to use straight away. 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, you may need to access the new stock item records and make minor changes to the settings, for example to assign additional suppliers or alternative items.

Importing items to multiple warehouses

When you initially import the stock items using Import Stock Records, 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 same CSV import file will be stored at all of the warehouses that you select.

So if you use multiple warehouse locations, there are different ways you could set the warehouses for your stock items.

  • When you initially import the stock items using Import Stock Records, you could organise the stock items into separate import CSV files for each warehouse location, or each group of warehouse locations.

    For example, you might have three import files containing stock items that are in the Oxford warehouse, the Birmingham warehouse, and are in both the Oxford and Birmingham warehouse.

  • After you have imported the stock items, you could use Add Locations to Stock Records to add additional warehouses to a group of stock items. You would organise the stock items into separate import CSV files for each warehouse location, or each group of warehouse locations.

    See Add locations (warehouses) to stock items.

  • After you have imported the stock items, you could use Import Stock Record Locations to add additional warehouses to stock items on an individual basis.

    See Import stock item locations (warehouses).

You can also use a combination of these methods. For example, you might initially set the warehouses for large groups of your stock items using Import Stock Records and Add Locations to Stock Records. Next, you could export your stock locations using Export Stock Record Locations, then use Excel to update the warehouses for stock items on a more individual basis. Once you're finished, you would use Import Stock Record Locations to import your changes.

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

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

Stock items

Using analysis codes to customise and analyse your business