Importing BOM from a CSV file
At Production planning -> BOMs -> Import from CSV it is possible to upload many bills of materials at once, e.g. a BOMs for separate products and/or multi-level BOMs. It's also possible to update existing BOMs via CSV upload.
Jump to:
- Demo video: Importing data.
- Importing new BOMs.
- Example import table and file.
- Importing a multi-level BOM.
- Updating existing BOMs.
- Important notes.
- Troubleshooting.
What is CSV? CSV (Comma-Separated Values) is a universal format for importing or exporting tabular data. A CSV file can be obtained by saving/exporting e.g. an Excel file as CSV (Comma delimited) (*.csv).
When uploading the CSV file:
- Select and match the names of the fields corresponding to the columns in the data.
- Check the "The first row is heading" option if the first row of the file contains headers. Then the first row will not be imported.
- Select "Do not import" as the column type if some column is not to be imported.
- Click Import to upload the data into the appropriate fields of the database.
- If importing some data failed, a skip file will be generated and downloaded to your computer, which indicates all errors line-by-line.
Importing new Bills of Materials
When uploading Bills of Materials, the fields that can be used are:
| Product number* |
The product's ID number (item code), as in Stock -> Items. This is the "Product" to which the "Part" belongs. Required. If a BOM number is absent, or a new BOM is created, the product's ID number is required on every row (this allows importing several BOMs for several products at once. E.g. a Sub-assembly "Product" code is the sub-assembly item code, etc.). |
| Part No.* |
The part's ID number (article code), as in Stock -> Items. This "Part" belongs to the BOM of the "Product". |
| Quantity* |
The quantity per unit of "Product". Required. If the product has parameters (Matrix BOM functionality) that change the quantity of raw materials, all quantity columns are filled with this number. |
| Unit of measurement |
The unit of measure for the material. This field must be used if the quantity is not specified in the primary unit of measure, but in a secondary unit. |
| BOM number |
The BOM's ID number. If present, lines with parts are grouped into bills of materials by this column. If the BOM number is absent, lines with parts are grouped by product. If a BOM with this number exists, it will be updated. If a BOM with this number does not exist, a new BOM is created. If present, the BOM's number is required on every row. |
| BOM name |
Name of a BOM. |
| Relation number |
The relation's ID number. A line can contain either by a part number or relation number, but not both. Available, if the Matrix BOM functionality is enabled. |
| Notes |
Any notes, e.g. handling instructions. |
| Fixed quantity |
A fixed (pre-defined) amount of item which is always consumed in a manufacturing order, not proportional to the order quantity. Available, if function Fixed quantity is enabled. |
For example, importing 2 Bills of Materials:
| Product | Part | Quantity | Notes |
| PROD-1 | PART-01 | 5 | BOM of PROD-1, line 1: raw material PART-01, quantity 5 |
| PROD-1 | PART-02 | 7 | BOM of PROD-1, line 2: raw material PART-02, quantity 7 |
| PROD-2 | PART-15 | 3 | BOM of PROD-2, line 1: raw material PART-15, quantity 3 |
| PROD-2 | PART-16 | 9 | BOM of PROD-2, line 2: raw material PART-16, quantity 9 |
| (Up to 3000 lines can be imported at once) | |||
Download a sample file: import-boms.csv
Importing a multi-level BOM
At Production planning -> BOMs -> Import from CSV, it is possible to import multi-level BOMs.
Importing a multi-level BOM works the same way as importing several different BOMs at the same time:
- Each sub-assembly is a separate item, with its own product code.
- Each sub-assembly has its own BOM.
- Sub-assemblies are used as parts in a higher level BOM, which connects the multi-level BOM structure.
For example, the file structure for a multi-level BOM with 3 levels, 4 BOMs:
| Product | Part | Quantity | Notes |
| PROD-1 | SUB-1 | 1 | BOM of PROD-01 (final assembly), line 1: sub-assembly SUB-1, quantity 1 |
| PROD-1 | SUB-2 | 1 | BOM of PROD-01 (final assembly), line 2: sub-assembly SUB-2, quantity 1 |
| SUB-1 | PART-15 | 2 | BOM of SUB-1 (sub-assembly), line 1: raw material PART-15, quantity 2 |
| SUB-1 | PART-16 | 3 | BOM of SUB-1 (sub-assembly), line 2: raw material PART-16, quantity 3 |
| SUB-2 | PART-17 | 4 | BOM of SUB-2 (sub-assembly), line 1: raw material PART-17, quantity 4 |
| SUB-2 | SUB-3 | 1 | BOM of SUB-2 (sub-assembly), line 2: sub-assembly SUB-3, quantity 1 |
| SUB-3 | PART-18 | 5 | BOM of SUB-3 (sub-assembly), line 1: raw material PART-18, quantity 5 |
| SUB-3 | PART-19 | 6 | BOM of SUB-3 (sub-assembly), line 2: raw material PART-19, quantity 6 |
| (Up to 3000 lines can be imported at once) | |||
Updating existing Bills of Materials via CSV upload
At Production planning -> BOMs -> Import from CSV it is possible to update existing Bills of Materials by uploading a CSV file containing the new BOMs.
Upload a CSV file with bills of materials as described in the help section "Import BOM from a CSV file". A BOM is updated if:
- the file contains a column with a BOM number, and a BOM with this number exists,
- or the file does not contain a column with a BOM number, a column with a product is selected, and the product has a BOM.
Alternatively, it is possible to bulk update one part in selected BOMs at Production planning -> BOMs -> Substitute a part.
After upload, to update the BOMs of planned Manufacturing Orders:
- Open a BOM.
- Press Save.
- Proceed to update the MOs.
Important!
- If the imported file contains non-Latin characters, the file must be in UTF-8 (preferred) or Unicode encoding. If Excel is used to save the file, select the "Unicode Text (*.txt)" option in the "Save as type" field. Even though the extension of the file is TXT, it can be imported as a CSV file.
- The limit is 3000 lines per upload. If the file is bigger, divide it into several smaller files.
- Since importing can be a question of trial-and-error, we strongly suggest making a back-up of the database before importing. In that case, if anything goes wrong, it is always possible to restore the previous session.
Issues when importing a BOM from CSV
If the importing of some data failed, a skip file will be generated and downloaded to your computer, which indicates all errors line-by-line.
Common issues:
- Part numbers in the import file and in MRPeasy do not match. Please use only part numbers to refer to stock items.
- If your part numbers are numerical, it may happen that your spreadsheet program thinks of these as integers or decimals. Then, it may perform unwanted rounding or removal of leading/trailing zeroes (e.g. part number 0123,4560 could be changed to 123,46). Check your CSV file for such issues. - Some parts (or the product) have not been created. All stock items must exist in the software before importing BOMs.
- Incorrect formatting of the import file. Please check that the file contains a column with a product's part number and that this number is set on every line of the BOM.
- Select the correct columns. You must manually select the correct data type for each column if these are not automatically correctly selected.