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:
• It is recommended to export the product data to be imported using the Export Importable Spreadsheet action or the Export Redline Importable Spreadsheet action as a starting point before making modifications.
• When you export, site-defined and classification attributes are included.
• The template file available for export may be configured by an administrator. It may be an Excel file with the XLSX or XLSM extension.
 |
When importing an importable spreadsheet, you can use the Excel files with either an XLS, XLSM, or XLSX extension.
|
• Before you import a spreadsheet, it may be helpful for you to review sample Excel spreadsheets. The following sample templates are available:
◦ From ptc.com:
|
|
Additionally, the templates include examples of the new view version and also of the manufacturer and vendor parts.
|
◦ The same sample import files are provided with the installation in the following location:
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/part-import-template.xlsx
For example, the path may be: http://hostname/Windchill/examples/xlsx/…
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/bom-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/part-related-documents-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/replacements-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/axl-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/document-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/redline-part-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/redline-bom-import-template.xlsx
▪ http://<hostname>:<port>/<WindchillAppl>/examples/xlsx/redline-replacements-import-template.xlsx
|
|
The URL for sample spreadsheets may also begin with https://. The files can also be located on the server at <Windchill_loadpoint>/examples/xlsx/.
|
• 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 resulting in a uniqueness conflict. To avoid this issue, the best practice is to first consider if the Replace action is more appropriate. If Replace is not the appropriate action, ensure the conflicting values are removed or are updated to avoid the conflict and then load the product structure again with the new values.
• 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.
• Users authorized to create or modify product data 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.
• 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.
• It is important to ensure that the values you enter in the spreadsheets adhere to the requirements and constraints outlined in the following topics: