Import and export stock items

Find this screen

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

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

   • Stock Items import format (XLS): Details of the information you need to include in the file.
   • Stock Items example CSV file: Use this to import from CSV format.
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 XML Extensible Markup Language (XML) file format. Sage 200 can import and export data in the XML file format. file.

Note: You can use the BOM / CSV File Converter utility to convert CSV Comma Separated Value (CSV) file format. Sage 200 can import and export data in the CSV file format. files 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. For details on how to download and use the converter, see the article Sage 200 Professional - BOM CSV File Converter (opens in a new tab).

1. Download the import information and example file:

   • Stock Item Extensions example XML file (opens in a new tab): Use this to import from XML format.
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 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 stock items

Stock items can be imported using a CSV file.

Note: You must first import or create the product groups for the stock items that you will import.

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.

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.

   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.

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.

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

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 200, or from one company in Sage 200 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/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.

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.

  Note - information

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

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

  Note - information

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

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.

About exporting stock items

Stock items can be exported to a 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 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 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.