Exporting Query Results to Microsoft Excel
|
CLI EQUIVALENT
|
im exportissues
|
You can export the visible item fields and columns returned by a query to Microsoft Excel from an Items view, relationships field, Relationships view, and Document view (GUI only) for data analysis. From a historical view, you can also export items as of a historical date or label.
|
|
Exporting large query result sets may affect Windchill RV&S server performance.
When you export items, Windchill RV&S saves the output as a temporary Excel file (.xls) that you can rename and save.
• In the Windows GUI, the temporary Excel file automatically opens in Microsoft Excel.
• In the Linux GUI, a dialog box indicates the path and name of the temporary Excel file.
• In the Web, the temporary Excel file is downloaded by your browser.
After the temporary file is open in Excel, you can then manipulate the data to create charts and reports for further analysis.
After data is exported to Excel, you cannot import the data back into Windchill RV&S. Exporting query results to Excel is intended for data analysis only. To export and import data between Windchill RV&S and Excel, use the Microsoft Excel integration with Windchill RV&S.
|
Key Considerations
• Windchill RV&S supports exporting items to Microsoft Excel. For the most current supported versions of Microsoft Excel, see the
Supported Integrations document.
• Excel has the most current data as retrieved from the server but the data in the Windchill RV&S client may be out of synch with the data retrieved from the server. Refresh the client to ensure you are viewing the most current data.
• Sort order and filters applied to the view are maintained in the exported data.
• If parameter substitutions are enabled in Windchill RV&S, they will be visible in the Excel output.
• If you exit theWindchill RV&S client on Windows while the temp file is still open in Excel, you are still able to save the file.
• Before you export query results, note that Excel contains the following data limitations:
◦ maximum 32,767 characters per cell
◦ maximum 65,536 rows per sheet
◦ maximum 256 columns per sheet
When the row limit is exceeded, data is split across sheets.
Long text fields that exceed the character limitation per cell are truncated, with “[Truncated]" appended to the end of the cell.
For a complete list of Microsoft Excel specifications and limitations, see the Microsoft product documentation.
• Windchill RV&S’s date/time fields format (based on your current locale) is not supported in Excel. Instead, Excel uses MM/dd/yyyy for date fields and MM/dd/yyyy HH:mm:ss for date/time fields. For example, the date/time field Jul 22, 2009 5:15:14 AM in Windchill RV&S displays as 07/22/2009 05:15:14 in Excel.
• The following Windchill RV&S display patterns are supported in Excel:
◦ "#,###"
◦ "#,###.##"
◦ currency symbol + "#,###"
◦ currency symbol + "#,###.00"
◦ "#,###;(#,###)"
◦ "#,###.##;(#,###.##)"
◦ "0.###E0" (displays as "0.###E+0" in Excel)
◦ "#%"
Windchill RV&S display patterns that use exclusive text are not supported in Excel. Any unsupported display pattern is disregarded; however, a standard display pattern of "0" for integer and "0.00" for floating point fields is applied.
• In the GUI and the Web, column widths from the view specify column widths in Excel. In the CLI, default column widths specify column widths in Excel.
• If wrapping is enabled for table content in Windchill RV&S, cell content is wrapped in Excel.
• If no items are exported, Excel does not start, for example, if you attempt to export an item that you do not have permission to view.
• In the Relationships view, the Relationship Flags and Order fields are not exported.
• In the Document view, the Section field is exported when visible in the view. In other views, where the Section field is not applicable, if the column is visible, it will be exported as blank.
• The following describes the format of each Windchill RV&S field after exporting to Excel:
◦ ID fields display as a number (0 decimal places, no other formatting).
◦ Section fields display as a number (including decimal places where applicable and no other formatting).
◦ Floating-point and integer fields display as numbers with display patterns (see above for examples).
◦ State, type, project, phase, range, SI Project, short text, long text, user, logical, and FVA fields display as text.
|
|
Rich content formatting does not display on long text fields.
|
◦ Attachment fields display as a CSV list of file names.
◦ Relationship fields display as a CSV list of ID/relationship flags.
◦ Query Backed Relationship (QBR) fields display as a CSV list of IDs.
◦ IBPL fields display as a CSV list of the field(s) visible in a text format.
◦ Group and pick fields display as a CSV list of text.
◦ Date fields display as a date.
To export query results to Microsoft Excel from the GUI or the Web
1. From an Items view, relationships field, Relationships view, or Document view (GUI only), do one of the following:
◦ To export specific items to Excel, select the items and select Export Items to Excel.
◦ To export all items to Excel, select Export Items to Excel.
If you are exporting versioned items, you can select Display “!” for ambiguous field values. When this option is selected, Windchill RV&S displays an ambiguous field value icon (!) to indicate an ambiguous computed field value in a versioned item.
A dialog box prompts you to choose to either export the selected items, or all items to Excel. By default, Export all items in the view is selected.
2. Click OK.
In Excel, the column names display in the first row, and data displays in the following rows:
