Item Master import

Before importing your data, back up your database. The import tool can make major changes to your database, and can overwrite data on previously-imported items. You cannot undo the changes it makes; you can only restore your backup database.

The Batch Importer tool can make an import file ItemMaster.csv template. Select Item Masters from the dropdown list, click the Export button, and save the file to a convenient location. The file contains existing item data, to check earlier imports or use for updating existing data.

The ItemMaster.csv file lets you import new items with their pending iterations, and update the item-level attributes of existing items. Import items can include custom attributes.

Item master data must be imported to provide a foundation for the relational imports of BOMs, sources, references, materials and files.

General guidelines§

  • The header names are not case-sensitive.

  • Imported items that rely on collections (Part Types, Units of Measure, Make/Buy Categories, etc.) will only be matched to collection members that exist in the Collection Explorer. However, you can force the system to add new members within selected collections by marking the Create missing collection member(s) checkbox. The new member is given only the name, and the default settings are used for all other values. After importing your data, you should review all new collection members' attributes to ensure they're set correctly.

  • The first row of the import file (the "header" row) must contain the column names as specified in this reference, or must match a custom attribute name that's been specified on a Part Types or Document Types collection member. Custom attributes must be specified within PDXpert before importing the item file.

  • Do not include more than one column with the same header name.

  • Extra (unmatched) columns are ignored.

  • Every value must conform to the column's specified data type (collection member, string, number, etc.) or be empty. For example, an attribute that requires a numeric value cannot include non-numeric characters in its value.

  • Depending upon the custom attribute scheme, there may be one or two columns for each custom attribute. The first column contains the custom attribute value, and the second column contains the value's unit of measure (for UOM scheme) or currency code (for Money scheme). This second column isn't used for other custom attribute schemes. If the value of either column is empty, then the custom attribute's default value is applied.

  • Special characters: ERP/MRP and other software may export data that's incompatible with Windows. Microsoft Excel can modify data in unexpected ways. Data copied from Linux and Mac systems, and from some web pages, use incompatible line endings.

    • If you're importing Unicode characters, the import file must be encoded as UTF-8, rather than DOS/Windows ANSI.

    • You can import multi-line text in the larger text fields, such as item Description, RevisionNote, and Notes. The import file must use Windows CRLF newline pairs. Linux/Mac software may export LF only, and some text editors may remove newlines in the CSV file format.

    • Do not import newlines into fields that should not have them, such as item Number and Revision fields.

    Text editors such as Notepad++ can show Unicode and control characters, and enforce encoding. Before importing, inspect Unicode and non-printing control characters in the CSV file. After importing, check that PDXpert correctly displays the Unicode and multi-line text.

  • Import rate is about 50 to 250 items per minute, depending on hardware resources (primarily disk speed) and data complexity (especially custom attributes). Import from the client's local disk, not from network resources. For each imported data row, the system checks for duplicates, imports the new item using its type's default values, validates the imported values against the type's and system's rules, matches collection members, updates default values with the imported values, and adds the item to the indexing queue. In other words, there's a lot going on.

Importing new items§

  • If this import file specification doesn't define a column for a PDXpert data attribute, then the new item uses the default collection member. For example, the Item Master import template doesn't contain a column for BOM Type Code so each imported part uses the default member in the BOM Type Codes collection, usually Direct material. Before importing, ensure each of your collections has the correct default member.
  • All columns except Number are optional. When a specified column is missing, then the new item uses the default value (if any) for the column.
  • Attributes use their default values (if any) unless a different value is given in the column.
  • Blank values are treated according to the column's data type. If the value is blank and the column expects a:
    • collection member (say, the CostCurrency), then the collection's default member (for example, USD) is used.
    • number, then the default value for that attribute is used.
    • Boolean (Yes/No), then a No (False) value is used.
    • text string, then a blank zero-length string is used.
  • Leading and trailing spaces are removed ("trimmed") from each data element before it's imported.
  • Each imported item has exactly one iteration, which is imported at a Pending release state.

Be sure that your Add these product families to every new item that I create user preference list is empty, or contains product families that you want assigned to new items. Your preferred list of product families does not have any effect on items that have exist.

Updating items in the database§

  • Each imported item is matched against all items in the database according to the rules specified in Tools ➔ System Rules..., Item uniqueness specified by: Number, Organization, Class, Type. All columns except Number are optional. If a match occurs, then the item row is not imported, but is used to update the existing part or document record. This update occurs regardless of the iteration release state (pending, released or canceled) of the existing item.
  • Leading and trailing spaces are removed ("trimmed") from each data element before it's matched.
  • If an attribute column is omitted from the import file, then items won't have that column's attribute updated. When the attribute column exists in the import file, then the current attribute value is replaced. When the imported value is
    • Empty: the attribute's default value is used.
    • Not empty: the new value overwrites the previous attribute value.
  • An item's iteration-level (revision-level) attributes cannot be updated. These are shown as Updateable = No in the table below. If the item is released, these values are not included in an ItemMaster.csv export file.

ItemMaster.csv format column definitions§

If you use Excel as your CSV file editor, it may make undesired changes to values that it interprets as a number. For example, part number strings beginning with zero (e.g., 001234) will often be silently converted to a numeric value (1234). If you have any part numbers, document numbers or other data with leading zeroes, don't use Excel. Instead, use a CSV file editor, Windows Notepad, or other plain text editor (not Word) to edit your import file.

Column name Updateable Data type Value if empty Description
Class No string Part or Document Part If not specified, then the item's Class is a part. Part records have physical attributes like a unit of measure, cost, mass and package quantity; documents do not.
Owner No Organizations default member Identifies the organization that is primarily responsible for the item's specification and which issues the item Number. See note 1.
Type No Document Types or Part Types default member If not specified, then the appropriate type collection is given by the item's Class, and the record's Type selection uses the default member of the calculated type collection. See note 1.
Number No string: 1 ≤ length ≤ 70 characters row is skipped REQUIRED: This value is imported as the new part or document Number value. If the value is empty, the row is not imported.
Revision No string: length ≤ 10 characters no value  
Description Yes string: length ≤ 1000 characters no value This value is imported as the new Part name or Document title value. See note 6.
Lifecycle No Item Lifecycle Phases default member If the value is empty, then the item record Lifecycle phase is given the default lifecycle phase (typically Production) according to the specified Type value. See note 1.
Trustee Yes Persons default member If the value is empty, then the default person is assigned as the record's Trustee selection. See note 1.
DefaultUOM No Units of Measure default member This value applies only to items where Class=Part. If the value is empty, then the record's Default unit of measure selection is given the default Units of Measure collection member (typically each).
MakeBuy Yes Make/Buy Categories default member This value applies only to items where Class=Part. If the value is empty, then the record's Make/buy selection is given the default Make/Buy Categories collection member (typically Unspecified). See note 1.
GlobalNumber Yes string: length ≤ 50 characters no value This value applies only to items where Class=Part. This is imported into the Global number (GTIN, UPC, etc.) value.
Location Yes string: length ≤ 1000 characters no value This value applies only to items where Class=Part. This value is imported into the record's part Location value.
RevisionNote No string: length ≤ 1000 characters no value This value is imported into the record's Release description value. See note 6.
CostPerUnit Yes double ≥ 0.0 0.0 This value applies only to items where Class=Part, and is used by the record's Unit cost per default unit value.
CostCurrencyShort Yes Currencies default member This value applies only to items where Class=Part. Use the Currencies member's Currency code value in this column. If the value is empty, then the record's cost Currency selection uses the default Currencies collection member (typically USD).
PackageQty Yes double > 0.0 1.0 This value applies only to items where Class=Part. This is imported into the Standard packaging quantity value.
PackageUOM Yes Units of Measure part's default UoM This value applies only to items where Class=Part. You must ensure that it's within the same UOM Category as the part's DefaultUOM value. If the value is empty, then the part's Default unit of measure (typically each) is assigned as the Standard packaging quantity unit of measure selection.
Certify Yes Boolean False This value applies only to items where Class=Part. This value is imported into the record's Part requires: Certified checkbox.
Serialize Yes Boolean False This value applies only to items where Class=Part. This value is imported into the record's Part requires: Serialized checkbox.
Complies Yes Boolean False This value applies only to items where Class=Part. This value is imported into the record's compliance checkbox (by default, labeled Meets regulatory requirements).
Notes Yes string: length ≤ 32000 characters no value This value is assigned to the record's Notes page. See note 6.
EffectiveDate No date no value This is used for an assembly's Effectivity or a document's Effective date value.
Person1 No Persons empty value This value may not be visible in item record unless the additional person #1 is enabled in the Part Types or Document Types collection member. See note 2.
Person2 No Persons empty value This value may not be visible in item record unless the additional person #2 is enabled in the Part Types or Document Types collection member. See note 2.
Mass Yes double ≥ 0.0 0.0 This value applies only to items where Class=Part. This is assigned to the Part mass (weight) value.
MassUOM Yes Units of Measure: Mass / weight default member This value applies only to items where Class=Part. Use the Mass / weight member's Name value in this column. If the value is empty, then the record's Part mass (weight) unit of measure selection uses the default Mass / weight collection member (typically g).
custom (note 5) Yes string no value Custom attribute value that must be compatible with the specified scheme (numeric, Boolean, collection, etc.). If the custom attribute's scheme is a collection (e.g., Countries or Persons), then the value must exist within the collection.
custom_Unit
(note 5)
Yes collection member default unit

Where custom attribute value's scheme is

  • UOM: unit of measure name (such as each), or
  • Money: 3-character currency code (such as USD)
Add _Unit to show the column's relation to the custom attribute; for example, a custom attribute Length will have its unit of measure in the Length_Unit column.

Notes:

  1. The cell must (a) contain an existing collection member Name value; or (b) be a blank value, which will cause the default member to be assigned; or (c) contain a new member that will be added if the related Create missing collection member(s) checkbox is marked. If the value fails these tests, the row is not imported.
  2. The cell must (a) contain an existing collection member Name value; or (b) be a blank value, which will cause a blank (null) value to be assigned; or (c) contain a new member that will be added if the related Create missing collection member(s) checkbox is marked. If the value fails these tests, the row is not imported.
  3. Matching of collection member names is not case-sensitive.
  4. String length limits are provided for guidance, but these limits are not enforced during import. Lengths longer than those specified may be successfully imported but could be difficult to use or may be affected in future releases.
  5. Before importing custom attributes, see the Custom attributes help topic for naming and use guidelines.
  6. You can import multi-line text in these and other text fields. The import file must be UTF-8 encoded, and use Windows-compatible CRLF newline pairs. Other software (such as ERP/MRP and Excel) may export LF only, may use other file encoding, or may not allow newlines in the CSV file format. Before importing, inspect text in the CSV file. Check that you do not import newlines in fields that should not have them, such as item Number and Revision fields.

Definitions§

Boolean§
A logical True/False or Yes/No value. If the value is True or Yes (not case-sensitive), then the related checkbox is marked, otherwise the checkbox is cleared.
date§
A date value in the local system format, which is derived from the importing computer's Regional and Language settings in the Windows Control Panel.
double§
A floating-point number, such as 2.5 and 92.5418, formatted using the importing computer's Regional and Language settings.
string§
A series of displayable letter, number and symbol Unicode characters, such as "Bolt". The length of the string "Steel Bolt" is 10 characters.
value§
The complete contents contained between two CSV delimiters.

1024

Help Guide Contents [as PDF]