im exportissues
exports query results to Microsoft Excel
Synopsis
im exportissues[--[no]exportHeadings] [--[no]openOutputFile] [--outputFile=value] [--[no|confirm]overwriteOutputFile] [--[no|confirm]trimExcessColumns] [--fieldFilter=value] [--[no]sortAscending] [--sortField=field[:asc|desc]] [--[no]substituteParams] [--asOf=[<date> |label:<label >] [--fields=field[:width[:rich|plain],field[:width[:rich|plain],...] [--height=value] [--[no]showTallRows] [--structureFieldDisplayFormat=value] [--[no]showDecorators] [--width=value] [-x value] [-y value] [query=[user:]query...] [--queryDefinition=query] [--hostname=value] [--port=value] [--password=value] [--user=value] [(-?|--usage)] [(-F value|--selectionFile=value )] [(-N|--no)][(-Y|--yes)][--[no]batch] [--cwd=value] [--forceConfirm=[yes|no]] [(-g|--gui)] [--quiet] [--settingsUI=[gui|default]] [--status=[none|gui|default]] item id...
Description
im exportissues exports the visible item fields and columns returned by a query to Microsoft Excel for further analysis. You can also export items as of a historical date or label.
When you export items, Windchill RV&S saves the output as a temporary Excel file (.xls) that you can rename and save.
Once the temporary file open in Excel, you can then manipulate the data to create charts and reports for further analysis.
Important:
Once you export data to Excel, you cannot import the data 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:
• In Excel, the column names display as the first row. The data displays in the following rows.
• Sort order and filters applied to items are maintained in the exported data.
• If you exit Windchill RV&S without saving the Excel file, the temporary file is deleted.
• 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 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 format of "0" for integer and "0.00" for floating-point fields is applied.
• In the GUI, 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.
• In the GUI, Excel starts automatically. From the CLI, you can specify whether to start 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.
• For relationships, the Relationship Flags and Order fields are not exported. For documents, the Section field is not exported.
• 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).
• Floating-point and integer fields display as numbers with display patterns.
• State, type, project, phase, range, SI Project, short text, long text, user, logical, and FVA fields display as text.
|
|
For long text fields, rich content formatting does not display.
|
• Attachment fields display as a CSV list of file names.
• Relationship fields display as a CSV list of ID/relationship flag rules.
• IBPL and QBR fields display as a CSV list of IDs.
• Group and pick fields display as a CSV list of text.
• Date fields display as a date.
Options
This command takes the universal options available to
im commands, as well as some general options. See the
options reference page for descriptions.
• --[no]exportHeadings
specifies whether to export column names. By default, column names are exported.
• --[no]openOutputFile
specifies whether to open the output file in Excel. By default, the file opens in Excel. If you specify --noopenOutputFile or are using a Linux client, a message indicates the path and name of the temporary Excel file.
• --outputFile=value
specifies the path and name of the Excel (.xls) output file.
• --[no|confirm]overwriteOutputFile
specifies whether to confirm overwriting the existing Excel output file.
• --[no|confirm]trimExcessColumns
specifies whether to confirm skipping specified columns that exceed Excel limitations. If you do not agree to skip columns that exceed Excel limitations and columns are detected, the command terminates, and no data is exported.
• --fieldFilter=field=[value,value...]
specifies any field filters to apply to the query results. The first component of the value is the field name. The second component specifies the project(s) that you want to filter the issues by. For example, --fieldFilter="Project=/Project1" filters for issues that have a value of Project1 in the Project field. If you do not specify a value, Windchill RV&S filters for issues with a value of Unspecified in the Project field.
|
|
Any project filtering applied to the query results in the GUI or Web interface does not apply in the CLI.
|
• --[no]sortAscending
specifies whether to sort the specified field in ascending or descending order.
• --sortField=field[:asc|desc]
specifies the primary field that issues sort by and the sort direction. This overrides the current default sort field and sort direction. You can sort fields in ascending (asc) or descending (desc) order, for example, --sortField=State:asc. To specify additional fields to sort items by, specify --sortField=field:[asc|desc] for each field. The sort order of fields is indicated by the order that the fields display in the command. For example, --sortField=Type:asc--sortField=State:desc sorts items by Type in ascending order first, and then by State in descending order. Specifying the sort direction is optional; however, if specified it overrides the sort direction specified in the --[no]sortAscending option. If the sort direction is not specified, the --[no]sortAscending option is used. If the --[no]sortAscending option is not specified, the default sort direction is used.
• --[no]substituteParams
specifies whether to replace parameter references in text fields with a parameter value. For more information on how parameter values are determined, see the Windchill RV&S Hilfe-Center.
• --asOf=<date |label:<label
allows you to export items as of a historical date or label. Use in conjunction with the --fields option. For example, to export items containing the Description field as of a specific date, type:
im exportissues --fields=Description --asOf="January 8, 2009 10:00:00 AM EST" 123 124 125
Note the following:
• If you do not specify the --asOf option, the current field values for the specified items are exported.
• If you specify a WEST or IST time zone, the time does not display correctly. Instead, use the time zone GMT+/-hours:minutes.
• This option can only be specified when items are explicitly provided as a selection; it does not work when the --query, --queryDefinition, or --queryDefinitionFile option is used.
• This option is intended only for use with the CLI or API; it does not work when used with the -g option.
• --fields=field[:width[:rich|plain]],field[:width[:rich|plain]],...
specifies the issue fields, and their respective widths, to display. Your administrator defines the fields in an issue type. Fields can include ID, Type, Assigned User, Assigned Group, Summary, and others. Use commas to specify more than one field. The default fields that display in the CLI are independent of the default fields (columns) specified in the GUI and Web interface.
• --[no]showTallRows
specifies whether to display variable height rows.
• --structureFieldDisplayFormat=value
specifies the fields and style to display for tree nodes. The default formatting is suitable for interpretation by most users; the various formatting options are provided for programmatic control. Uses the same values as --fields, but similar to a Java MessageFormat string (that is, it requires { } to enclose each field). For example:
im exportissues --query=jhoyt:Defects --structureFieldDisplayFormat="{ID},{Summary}"
• --[no]showDecorators
specifies whether to display the ! decorator to indicate a computed field in a versioned item contains an ambiguous computation. By default, the --noshowDecorators option is specified.
• --query=[user:]query...
specifies the name of the user who the query belongs to and the query name, for example, jhoyt:Cosmos Critical Defects. For queries that have the same name, but different users, you must specify [user:]query. By default, the fields specified in the query are exported. To export specific fields in the query, specify the --fields=field[:width[:rich|plain]],field[:width[:rich|plain]],... option. To export specific items, specify the item id... option.
|
|
Windchill RV&S initially assumes that text before the colon (:) is a user name and text after it is a query name. If Windchill RV&S fails to find a matching user name and query name, it searches for a query name matching the exact text. For example, if you type jhoyt:CosmosDefects, Windchill RV&S searches for the CosmosDefects query created by jhoyt. If Windchill RV&S cannot find the query and/or user, it searches for the jhoyt:CosmosDefects query created by any user.
|
• --queryDefinition=query
specifies a string to define the query constraints. For details of the query format, see the
im createquery reference page.
|
|
To reference the original query in the new query so that any changes to the original query are reflected in the new query, define <subquery>as subquery[OriginalQuery].
|
• item id...
specifies the ID of the item you want to export. Use a space-separated list to specify more than one item ID, for example, 240 241 242. If you specify this option, you do not need to specify the --query=[user:]query... option, which exports all items returned by the specified query.
If document versioning is enabled, you can also specify versioned items. To type the ID of a versioned item, use the format Live Item ID-major.minor, for example,184-1.2.
See Also
