Best Practices for Using Excel to Export and Import Product Data
The following best practices can aid your import or export process when using Microsoft Excel:
• If you encounter scripting errors, consider changing the ActiveX settings in your Microsoft Excel version as applicable:
 |
ActiveX settings are not applicable to Microsoft 365 Excel.
|
a. In Excel, select > .
b. In the Excel Options window, select Trust Center. Then click Trust Center Settings.
c. In the Trust Center window, select ActiveX Settings. Then select Disable all controls without notification.
d. Select Disable all controls without notification and click OK.
• Before importing parts and product structures, use the Export Importable Spreadsheet action and use the exported spreadsheet as a template for the import.
|
|
• When the Export Importable Spreadsheet window is displayed without the export options and the BOM Table checkbox selected, the default file type displayed is XLSX, and you can use the Excel files with either an XLSM or XLSX extension.
• If you select the Parts, BOM, Part Related Documents, or Replacements export options when exporting an importable spreadsheet without a BOM table, you can use the Excel files with only an XLSX extension.
• When exporting an importable spreadsheet with a BOM table, you can use the Excel files with only an XLSM extension. For more information, see Accessing the Importable Spreadsheet with a BOM Table.
|
◦ Before importing redline structures, use the Export Redline Importable Spreadsheet action and use the redline BOM exported spreadsheet as a template for the import.
|
|
When exporting a redline BOM importable spreadsheet, you can use the Excel files with only an XLSX extension.
|
• When you export, site-defined and classification attributes are included.
• Users authorized for Create and Modify may be restricted from using Import from Spreadsheet by establishing a policy access control rule that denies Full Control (All) permissions on the ImportJob object type across all life cycle states within the relevant domain.
• Before you import a spreadsheet, it may be helpful for you to review sample Excel spreadsheets. The following sample templates are available:
|
|
When importing an importable spreadsheet, you can use the Excel files with an XLS, XLSM, or XLSX extension.
|
◦ From ptc.com:
|
|
Additionally, the templates include examples of the new view version, as well as manufacturer and vendor parts.
|
◦ The same sample import files are provided with the installation in the following location:
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/part-import-template.xls
For example, the path may be: http://hostname/Windchill/examples/xls/…
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/bom-import-template.xls
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/axl-import-template.xls
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/document-import-template.xls
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/part-related-documents-import-template.xls
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xls/redline-bom-import-template.xls
|
|
The URL for sample spreadsheets may also begin with https://. The files can also be located on the server at <Windchill_loadpoint>/examples/xls/.
|
• When you open any of the Import from Spreadsheet and Import from Redline Spreadsheet windows, it is important to be mindful of any ongoing import jobs that are creating baselines, documents, or parts in the background. If you delete or update these items while the import is in progress, it could cause the import to fail.
• It is recommended to use text format for all cells in the spreadsheet. For example, if you are importing a number with leading zeros, Excel removes the zeros unless you are using text format.
To set the cell format, complete the following steps:
a. Select an entire column, row, or a specific cell.
b. Right-click and select Format Cells.
If a field contains a numeric value, a green triangular glyph appears at the upper-left corner of the cell. If the cell contains a combination of numeric and character values, for example 123Text, the green triangular glyph is not present.
When updating reference designators or line numbers, if a value in one row is changed to a value that is used in a subsequent row, the change fails. This failure occurs because the subsequent row still uses the old value. To avoid this issue, the best practice is to first load the product structure without any reference designators or line numbers. This action removes all existing reference designators and line numbers from the product structure. After that, you can load the product structure again with the new values.
• It is important to ensure that the values you enter in the spreadsheets adhere to the requirements and constraints outlined in the following topics: