Importing Items and Related Objects Using a Spreadsheet

You can use a Microsoft Excel spreadsheet to import new and updated items and other, related objects, into the system. Before doing so, you must first have defined import formats for the particular item catalog category you wish to use, and you must also have the appropriate version of the Excel spreadsheet installed. The objects available for import using a Microsoft Excel spreadsheet include:

You can use the same spreadsheet for multiple uploads, and also across different sessions for the same user. To upload the same spreadsheet during another session, open the spreadsheet you wish to upload, and select Upload from the Oracle menu in the spreadsheet menu bar. After you log in and choose the appropriate responsibility, you can immediately begin to upload this spreadsheet again.

Whenever you enter new or existing data in the spreadsheet, the Upl column displays a flag, making it easier to scan the spreadsheet for rows that have been changed.

Tip: You can rename columns, wrap the column name text, or widen the columns so the column names are easier to read and understand. You must unprotect the spreadsheet before reformatting the columns.

Prerequisites

arrow icon   To import items using a spreadsheet:

You can include multi-row attribute groups in your item import formats. To do so, make sure that the rows in the spreadsheet that you are using for updating multi-row attributes do not contain any attribute values other then the org code, item number, item revision (if applicable), and the user-defined multi-row attribute values. When importing sequence generated item numbers with multi-row attribute groups, use dummy strings for the item numbers, such as $$SG$$1, $$SG$$2, and so on, in the WebADI Excel field for the item number. The import program uses this dummy string to determine which row belongs to which item.

Caution: If you include values for other primary (for example, item status, or lifecycle) or single-row user-defined attributes in the same rows as the multi-row attributes, then the updates will fail.

Additional Information: You can import items with multi-row attributes for multiple organizations during a single concurrent request.

Important: You cannot update transaction attributes using the Item Open Interface, the Microsoft® Office Excel spreadsheet, or the Import Workbench. You must update them directly. See: Defining Transaction Attributes.

Items uploaded to the system from a spreadsheet become, by default, Engineering items. If you wish to upload a non-Engineering item, then include an available primary attribute column called "Engineering Item Flag" in your import format. When uploading the spreadsheet, identify such items in this column by specifying the value "Yes" or "No" in this column. If you leave the column blank, the item once again defaults to an Engineering item.

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. On the Import page, select the Items object and click Continue.

    Note: An import format should already be defined for items or item revisions. Item categories do not have a defined import format that is associated with an item catalog category. Instead, a predefined format is used for all item category imports. The columns in this format are identical to the interface table. Predefined formats matching the interface tables are used for item people, item organization assignment, and manufacturing part number imports, too.

  3. On the Import: Item page, select an item catalog category and then click Go. The item catalog category determines which import formats are available for use.

    Note: If you do not select an item catalog category, the default System Item Import Format is available for use. If an import format has not yet been defined for an item catalog category that you select, then the default System Item Import Format is available for use. The System Item Import Format does not appear if you select an item catalog category that already has import formats defined. Neither users nor the system administrator can modify the System Item Import Format.

  4. When the Import: Item page refreshes with the import formats available for the item catalog category, select the import format you wish to use and click Import.

    Note: If a default import format has been specified for the item catalog category, it will already be selected.

  5. On the Select Viewer page, select the version of Excel that you are using and click Next. You cannot enable or disable Reporting. Also, Language has only one choice, which is already selected.

  6. A Download window presents processing messages. Click Close when the transfer is complete. The Excel spreadsheet also opens.

  7. In the spreadsheet, primary attributes are listed first (on the left), and user-defined attributes appear after (on the right). When entering values into the spreadsheet, you must use one of the following Transaction Types:

    Sync

    If the item already exists, the spreadsheet values are used to update it. If the item does not already exist, a new one is created using the spreadsheet values. If the Sync creates a new item, then the mandatory attributes for Create are also mandatory for the Sync. If the Sync updates an existing item, then the mandatory attributes for Update are also mandatory for the Sync.

    Additional Information: To delete an existing value in a record, enter the following data in the appropriate column of the spreadsheet:

    For field type: Enter value:
    Date 31-DEC-9999
    Character !
    Numeric 9.99E125

    If you want to retain an existing value in a record, leave it blank in the spreadsheet.

    Exception: In the user defined attributes interface table, when you want to delete an existing value in any of the following fields, use the value !. If you want to retain an existing value in a record, no entry is necessary.

    • ATTR_DISP_VALUE

    • ATTR_VALUE_STR

    • ATTR_NUM_VALUE

    • ATTR_VALUE_DATE

    Create

    A new item is created using the values entered in the spreadsheet. The following attributes are mandatory when using this transaction type:

    Note: Description is a primary attribute, but appears on the right side of the spreadsheet among the user-defined attributes. It is placed here so that long descriptions will not disrupt the flow of the spreadsheet. If the description is function-generated, the system generates the description during the item creation process.

    Some attribute columns also utilize poplists and LOVs, depending on the attribute definition. You can also make updates to multi-row attributes.

    Make a note of the Result Format Usage ID at the top of the spreadsheet. You will need this ID later when you upload the spreadsheet in the system.

    • If the import format contains attributes from attribute groups that are associated with the supplier business entity, then you must enter a value in the key column Supplier Name. If the import format contains attributes from attribute groups that are associated with the supplier site or supplier site organization business entities, then you must enter a value in the key column Supplier Site as well as Supplier Name. If you need to enter a retail site, enter the retail site organization code in the existing Organization Code column.

    • When importing style and SKU item information:

      • Use the Style column to indicate if the item is a style item (Yes or No).

      • Use the Style Item column to identify the style item for an SKU item row.

      • You must define a style item before you define its child SKU items. You can define the style and SKU items within the same spreadsheet, but the new style item must be in a row above the new SKU items.

      • Variant attribute columns do not have a list of values in the spreadsheet to choose from. For style items, enter the child value set name in the variant attribute column. For SKU items, enter the variant attribute value in this column.

        For example, assign the variant attribute group Color to a style item. Color has multiple value sets: fall, winter, and spring colors. Assign the value set Fall to the style item in the variant attribute column. For an associated SKU, enter a fall color attribute value, such as brown or orange, in the same column.

  8. After data entry is complete, select Upload from the Oracle menu in the toolbar.

  9. In the Upload Parameters window, enter the Result Format Usage ID and check the Automatically Submit Concurrent Process box, and then click Proceed to Upload.

  10. In the Oracle Web ADI window, click Upload. After the upload process completes, a message appears with the Concurrent Request number. Make a note of this number, as you will need it later when you access the view log for the concurrent request. Click Close when the process messages complete.

  11. Return to the Applications tree menu and click the View Concurrent Requests link.

    Note: It is recommended that you review the concurrent process view log to ensure that the upload process was successful.

  12. On the View Concurrent Requests page, click the Details icon.

  13. On the Request Details page, click View Log.

  14. From the View Log, paste the URL string in a web browser to view any errors. If no errors appear, the upload was successful.

arrow icon   To import item revisions using an Excel spreadsheet:

Item revisions uploaded to the system from a spreadsheet become, by default, Engineering items. If you wish to upload a non-Engineering item, then include an available primary attribute column called "Engineering Item Flag" in your import format. When uploading the spreadsheet, identify such items in this column by specifying the value "Yes" or "No" in this column. If you leave the column blank, the item once again defaults to an Engineering item.

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. On the Import page, select Item Revisions and click Continue.

    An import format should already be defined for items or item revisions. Item categories do not have a defined import format that is associated with an item catalog category. Instead, a predefined format is used for all item category imports. The columns in this format are identical to the interface table.

  3. On the Import: Item Revision page, select an item catalog category and then click Go. The item catalog category determines which import formats are available for use.

    Note: If you do not select an item catalog category, the default System Item Revision Import Format is available for use. If an import format has not yet been defined for an item catalog category that you select, then the default System Item Revision Import Format is available for use. Neither users nor the system administrator can modify the System Item Revision Import Format.

  4. When the Import: Item Revision page refreshes with the import formats available for the item catalog category, select the import format you wish to use and click Import. If a default import format has been specified for the item catalog category, it will already be selected.

  5. On the Select Viewer page, select the version of Excel that you are using and click Next. You cannot enable or disable Reporting.

  6. A Download window presents processing messages. Click Close when the transfer is complete. The Excel spreadsheet also opens.

  7. In the spreadsheet, primary attributes are listed first (on the left), and user-defined attributes appear after (on the right). When entering values into the spreadsheet, you must use one of the following Transaction Types:

    Sync

    If the item revision already exists, the spreadsheet values are used to update it. If the item revision does not already exist, it is created using the spreadsheet values. If the Sync creates a new item revision, then the mandatory attributes for Create are also mandatory for the Sync. If the Sync updates an existing item revision, then the mandatory attributes for Update are also mandatory for the Sync

    Create

    A new item revision is created using the values entered in the spreadsheet. The following attributes are mandatory when using this transaction type:

    Note: Description is a primary attribute, but appears on the right side of the spreadsheet among the user-defined attributes. It is placed here so that long descriptions will not disrupt the flow of the spreadsheet.

    Update

    Existing item revisions are updated using the values entered in the spreadsheet. The following attributes are mandatory when using this transaction type:

    Additional Information: To delete an existing value in a record, enter the following data in the appropriate column of the spreadsheet:

    For field type: Enter value:
    Date 31-DEC-9999
    Character !
    Numeric 9.99E125

    If you want to retain an existing value in a record, enter NULL in the appropriate column of the spreadsheet.

    Exception: In the user defined attributes interface table, when you want to delete an existing value in any of the following fields, use the value !. If you want to retain an existing value in a record, no entry is necessary.

    • ATTR_DISP_VALUE

    • ATTR_VALUE_STR

    • ATTR_NUM_VALUE

    • ATTR_VALUE_DATE

    Make a note of the Result Format Usage ID at the top of the spreadsheet. You will need this ID later when you upload the spreadsheet in the system.

    You can also make updates to multi-row attributes.

  8. After data entry is complete, select Upload from the Oracle menu in the toolbar.

  9. In the Upload Parameters window, enter the following and then click Upload.

  10. Click Upload. After the upload process completes, a message appears with the Concurrent Request number. Make a note of this number, as you will need it later when you access the view log for the concurrent request. Click Close when the process messages complete.

  11. Return to the Applications tree menu and click the View Concurrent Requests link.

    Note: It is recommended that you check the concurrent process view log to ensure that the upload process was successful.

  12. On the View Concurrent Requests page, click the Details icon.

  13. On the Request Details page, click View Log.

  14. From the View Log, paste the URL string in a web browser to view any errors. If no errors appear, the upload was successful.

Additional Information: If you created a new revision containing user defined attributes defined at the item revision level and enabling the change order option, then the new revision contains the item revision user defined attributes copied from the current revision. Any changed item revision user defined attributes appear in the new revision's change order attribute change section.

arrow icon   To import item categories using an Excel spreadsheet:

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. On the Import page, select Item Categories and click Continue.

    Item categories do not have a defined import format that is associated with an item catalog category. Instead, a predefined format is used for all item category imports. The columns in this format are identical to the interface table.

  3. On the Import: Item Catalog Categories Assignment page, click Import.

    When your spreadsheet opens, it displays the same item category columns that appear on this page, plus an old category column. Only use the old category column when updating existing item categories.

  4. On the Select Settings page, select the version of Excel that you are using and click Next.

    You cannot enable or disable Reporting.

  5. A Download window presents processing messages. Click Close when the transfer is complete. The Excel spreadsheet also opens.

    Note that the columns that appeared on the Import: Item Category Assignment page have been exported to the spreadsheet.

  6. When entering values into the spreadsheet, use the one of the following Transaction Types:

    The following attributes are mandatory when using these Transaction Types:

    Make a note of the BatchID at the top of the spreadsheet. You will need this ID later when you upload the spreadsheet to the system.

  7. After data entry is complete, select Upload from the Oracle menu in the toolbar.

  8. In the Upload Parameters window, enter the Batch ID.

    In the Upload Processed Records menu, select Yes. If you select No, your data is sent to the user interface table only and is not processed; Consequently, debugging is not possible.

    In the Delete Processed Records menu, select No. If you select Yes, all records corresponding to the current Record Set ID are cleared from the interface table.

    Click Proceed to Upload.

  9. In the Oracle Web ADI window, wait for the Ready message and click Upload.

    Show Trace Messages is, by default, not selected. Do not change this setting unless you wish to have trace information provided via your parameters.

    Clicking Parameters enables you to modify your parameters.

    Important: When the Upload completes, a Concurrent Request Number appears in this window. Make note of this number, as you can later use it to view the results of the upload process (for example, to view whether or not the upload was successful).

  10. If you do not wish to confirm that the upload was successful, you are finished. If you wish to view the results of the upload process, return to the Applications tree menu and click the View Concurrent Requests link.

    It is recommended that you check the concurrent process view log to ensure that the upload process was successful.

  11. On the Requests page, select Completed and click Go. Look for your Concurrent Request Number to locate your process. If it does not appear, wait and repeat this step. You can also select Pending and click Go to view pending requests. Locate your process in the Results table and click the Details icon.

  12. On the Request Details page, click View Log.

  13. From the View Log, paste the URL string in a web browser to view any errors. If no errors appear, the upload was successful.

arrow icon   To import item organization assignments using a spreadsheet:

Important: Users can only assign items to those organizations to which they have permission. See: Overview of Roles and Role Based Security

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. In the Import page, select Item Organization Assignment and click Continue.

  3. In the Import: Item Organization Assignment page, click Import.

  4. In the Select: Viewer page, select the appropriate version of Excel and click Next.

    The Processing: Creating Document page appears. Wait until the Excel application is ready.

    The Confirmation page appears. An Excel spreadsheet opens and the Confirmation page returns to the Navigator page. Alternately, click Return to return to the Navigator page.

To enter data in the spreadsheet

  1. Enter the organization assignments for each item in the spreadsheet.

    Organization Code - Select your organization from the drop down list.

    Item - Enter the item number. For instance, M1000.

    Primary Unit of Measure - Enter the display name of the unit of measure. For instance, Each.

    Note the Set Process ID at the top of the spreadsheet. You will need it to upload the spreadsheet in the system.

  2. Select Upload from the Oracle menu in the tool bar.

  3. In the Upload Parameters window, enter the Set Process ID and click Upload.

  4. After upload, a message appears with the Concurrent Request number. Note this number. You will need it to verify the view log for the concurrent request. Click Close when the process messages complete.

    Tip: It is recommended that you review the concurrent process view log to ensure that the upload was successful.

arrow icon   To import item people assignments using a spreadsheet:

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. In the Import page, select Item People Assignment and click Continue.

  3. In the Import: Item People Assignment page, click Import.

  4. In the Select: Viewer page, select the appropriate version of Excel and click Next.

    The Processing: Creating Document page appears. Wait until the Excel application is ready.

    The Confirmation page appears. An Excel spreadsheet opens and the Confirmation page returns to the Navigator page. Alternately, click Return to return to the Navigator page.

To enter data in the spreadsheet

  1. Use one of the following Transaction Types when entering values into the spreadsheet:

    Note the Data Set ID at the top of the spreadsheet. You will need it to upload the spreadsheet in the system.

  2. Enter values for the attributes.

    Transaction Type - Select the appropriate transaction type from the drop down list.

    Organization Code - Select your organization from the drop down list.

    Item - Enter the item number. For instance, M1000.

    Role - Select the display name of the role from the drop down list. For instance, Design Engineer.

    Type - Select the type of user from the drop down list. For instance, Group.

    Name - Enter the name of the person, group, or company. Leave the cell empty for type 'All Users.'

    Start Date - Enter the start date of the grants. Date format - mm/dd/yyyy.

    End Date - Enter the end date of the grant. Date format - mm/dd/yyyy.

  3. Select Upload from the Oracle menu in the tool bar.

  4. In the Upload Parameters window, enter the Data Set ID and click Upload.

  5. After upload, a message appears with the Concurrent Request number. Note this number. You will need it to verify the view log for the concurrent request. Click Close when the process messages complete.

    Tip: It is recommended that you review the concurrent process view log to ensure that the upload was successful.

arrow icon   To import manufacturer part numbers using a spreadsheet:

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. In the Import page, select Manufacturer Part Numbers and click Continue.

  3. In the Import: Manufacturer Part Number Association page, click Import.

  4. In the Select: Viewer page, select the appropriate version of Excel and click Next.

    The Processing: Creating Document page appears. Wait until the Excel application is ready.

    The Confirmation page appears. An Excel spreadsheet opens and the Confirmation page returns to the Navigator page. Alternately, click Return to return to the Navigator page.

To enter data in the spreadsheet

  1. Use one of the following Transaction Types when entering values into the spreadsheet:

    Note the Batch ID at the top of the spreadsheet. You will need it to upload the spreadsheet in the system.

  2. Enter values for the attributes.

    Transaction Type - Select the appropriate transaction type from the drop down list.

    Item - Enter the item number. For instance, M1000.

    Organization Code - Select your organization from the drop down list.

    Manufacturer - Select the name of the manufacturer from the drop down list.

    Manufacturer Part Number - Enter the manufacturer part number.

    First Article Status - Optionally, select the first article status from the drop down list. The first article status indicates whether or not a manufacturer has built and/or provided the first article.

    Approval Status - Optionally, select the approval status of the manufacturer and manufacturer part number from the drop down list.

    Effective From - Enter the start of the effective date range for the manufacturer part number. Date format - mm/dd/yyyy.

    Effective To - Enter the end of the effective date range for the manufacturer part number. Date format - mm/dd/yyyy.

  3. Select Upload from the Oracle menu in the tool bar.

  4. In the Upload Parameters window, enter the Batch ID and click Upload.

  5. After upload, a message appears with the Concurrent Request number. Note this number. You will need it to verify the view log for the concurrent request. Click Close when the process messages complete.

    Tip: It is recommended that you review the concurrent process view log to ensure that the upload was successful.

arrow icon   To import structures using a spreadsheet:

You can import a new or update an existing structure using a spreadsheet. Each row in the spreadsheet represents a component item in the structure. You can add as many rows as needed to the spreadsheet.

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. In the Import page, select Structures and click Continue.

  3. In the Add to Batch: Import Structure page, select values for the following fields from the drop down lists, then click Import.

  4. In the Select: Viewer page, select the appropriate version of Excel and click Next.

    The Processing: Creating Document page appears. Wait until the Excel application is ready.

    The Confirmation page appears. An Excel spreadsheet opens and the Confirmation page returns to the Navigator page. Alternately, click Return to return to the Navigator page.

To enter data in the spreadsheet

  1. Enter values in the following fields for the new or existing structure.

    Tip: Note the Batch ID at the top of the spreadsheet. You will need it to upload the spreadsheet in the system.

  2. Use the following Transaction Types when entering components into the spreadsheet:

  3. Enter the following field values for each component.

    Additional columns may exist, depending on the import/display format selected earlier.

  4. Select Upload from the Oracle menu in the tool bar.

  5. In the Upload Parameters window, enter the Batch ID and click Upload.

  6. After upload, a message appears with the Concurrent Request number. Note this number. You will need it to verify the view log for the concurrent request. Click Close when the process messages complete.

    Tip: It is recommended that you review the concurrent process view log to ensure that the upload was successful.

arrow icon   To import item relationships using a spreadsheet:

  1. In the Applications tree menu, Development Manager responsibility, navigate to Batch Import > Import Items. Click the Import from Excel link.

  2. In the Import page, select Item Relationships and click Continue.

  3. In the Import: Item Relationships page, click Import.

  4. In the Select: Viewer page, select the appropriate version of Excel and click Next.

    The Processing: Creating Document page appears. Wait until the Excel application is ready.

    The Confirmation page appears. An Excel spreadsheet opens and the Confirmation page returns to the Navigator page. Alternately, click Return to return to the Navigator page.

To enter data in the spreadsheet

  1. Use one of the following Transaction Types when entering values into the spreadsheet:

    Note the Batch ID at the top of the spreadsheet. You will need it to upload the spreadsheet in the system.

  2. Enter values for the attributes.

    Transaction Type - Select the appropriate transaction type from the drop down list.

    Organization Code - Enter your organization code.

    From Item - Enter the item number for which you are defining the item relationship. For instance, M1000.

    To Item - Use this field when creating a new item relationship as well as updating or deleting a previously defined item relationship. When updating or deleting, this mandatory field identifies the existing item relationship. Enter the related item number.

    New To Item - Use this field when updating an item relationship. Enter the new related item number.

    Current Relationship Type - Use this field when creating a new item relationship as well as updating or deleting a previously defined item relationship. When updating or deleting, this mandatory field is used to identify the existing item relationship. Select the type of relationship from the drop down list.

    New Relationship Type- Use this field when updating an item relationship. Select the new type of relationship from the drop down list.

    Reciprocal - Enter Yes if the items are both related to each other (each item defines the other as a related item). Otherwise, enter No.

    Start Date - Enter the start of the effective date range for the item relationship. Date format - mm/dd/yyyy.

    End Date - Enter the end of the effective date range for the item relationship. Date format - mm/dd/yyyy.

  3. Select Upload from the Oracle menu in the tool bar.

  4. In the Upload Parameters window, enter the Batch Name and click Upload.

  5. After upload, a message appears with the Concurrent Request number. Note this number. You will need it to verify the view log for the concurrent request. Click Close when the process messages complete.

    Tip: It is recommended that you review the concurrent process view log to ensure that the upload was successful.

Other Sources

Related Topics