Update parts and supplies using bulk import

Parts and supplies records (stock records) can be updated by using the bulk update feature of the CMMS. To bulk update records, download a spreadsheet of your current stock list, then edit that spreadsheet with the changes you need, then upload the spreadsheet to the CMMS.

If you need to update the amount of stock at locations (or move stock from one location to another), see Transfer stock between locations.

In this article:

• Update parts and supplies using bulk import
  • Save your current stock list with a stock list report
  • Update the spreadsheet with the changes you need
  • Import the updated spreadsheet
• Troubleshooting stock record imports
  • Special characters
  • Mandatory fields and blank fields
  • Aisle, row, and bin information 

Update parts and supplies using bulk imports

To update your parts and supplies records, you can download a report with your current records, then update that spreadsheet and upload it to the CMMS using bulk imports. 

Save your current stock list with a stock list report

Run a stock list report to create a spreadsheet of your current stock in the correct format. Stock is your list of parts and supplies, but also includes quantities and locations of these items. 

To generate the stock list report:

1. Go to Reports.
2. Search for Stock List (Unformatted).
3. Click the report in the list:
   
4. Click Run:
   
5. In the Render in Format drop-down list, select Excel Spreadsheet:
   
6. Choose a facility from the Location: (link) list to limit your results to a single facility, or leave the results as All:
   
7. Click Run.
8. The file is automatically downloaded to your default downloads folder. 

Update the spreadsheet

Now that you've generated the report, you can open and edit it in Excel or another spreadsheet-editing software. 

In the example below, we updated the quantity on hand (from "0" to "3") for part A10 at the Toronto location:

Once imported, the existing record for part A10 will be updated with the new quantity.

The following columns are required and can't be removed from the import file:

• Stock item
• Part code
• Location name
• Aisle
• Row
• Bin
• Min Qty (if already populated)
• On Hand (if already populated)

When you're finished editing, save your spreadsheet as a CSV file.

Import the updated CSV file

To import the updated CSV file:

1. Go to Supplies > Parts and Supplies.
2. Click Import:
   
3. Click Upload.
4. Select the CSV file you updated.
5. Click Continue:
   
6. Use the drop-down lists to map the columns in your file to the fields in the CMMS. For example, "Stock Item" would map to "Parts & Supplies Name":
   
7. Click Continue.
8. Select the Use this answer for all future questions checkbox.
9. Click Update:
   
   The application will analyze your file.
   Note: If your file contains multiple stock locations for the same part, the application will list warnings for these rows. These specific warnings will not affect the import.
10. Review the results of the analysis and do one of the following:
    • To proceed, click Import.
    • To make changes (for example, to map columns differently), click Back.

Once the import is complete, you can check your parts records to make sure that fields were updated as expected. Continuing the example above, we can confirm that the quantity on hand  at the Toronto location was updated as expected:

Troubleshooting imports

Imports can be tricky. Here are some of the common issues users face with importing parts and supplies from a spreadsheet:

Special characters

Records that contain special characters (commas, quotation marks, etc.) can result in corrupted data, and can even cause imports to fail. To learn more about special characters in import files, see Troubleshooting Imports.

Mandatory fields and blank fields

When importing, any columns that you leave blank will result in blank records, as they are not auto-populated by the CMMS. To prevent unexpected issues, we suggest removing any optional columns you don’t need to update. 

With parts and supplies, the following columns are required and can't be removed from the import file:

• Stock item
• Part code
• Location name
• Aisle
• Row
• Bin
• Min qty*
• On hand (Qty on hand)*

*Min Qty and On Hand columns are removable, but removing these fields from the spreadsheet and uploading the spreadsheet will delete this information from your records. We suggest keeping these fields in the spreadsheet if your records have this field filled in. 

Aisle, row, and bin information

Aisle, row, or bin information can’t be updated in existing records because the information is used to uniquely identify a stock location (there can be multiple stock areas at a single location). To learn more about the fields that can be imported, see Data import overview.