pacx data query

Commands to streamline data manipulation

Usage

Overview

This command allows you to execute queries against a Dataverse environment and retrieve data in various formats. It supports multiple query languages and output formats.

Supported Query Types

The command automatically detects the query type based on the query text:

• FetchXML: queries starting with '<' are interpreted as FetchXML
• SQL: queries starting with 'SELECT ' (case-insensitive) are interpreted as SQL
• OData: queries containing OData query options (e.g., $filter=, $select=, $top=) are interpreted as OData

Please note: If you're using PowerShell, remember to escape the $ sign using the ` character in OData queries.

Input Options

You can provide the query in two mutually exclusive ways:

• --query (-q): pass the query text directly as a command argument
• --query-file (-qf): specify a path to a file containing the query text

Output Formats

The command supports multiple output formats via the

--format option:- JSON (default): outputs the results as formatted JSON

• CSV: outputs the results as comma-separated values
• XML: outputs the results as XML
• Excel: outputs the results as an Excel file (.xlsx) - requires --output to be specified

Output Destination

By default, results are printed to the console. Use

--output (-o) to save results to a file. The file extension must match the selected format.Use

--auto-run to automatically open the output file after the query completes.### Internal Processing

For OData queries, the command performs special processing on the response:

• Properties in the form '_fieldname_value' are recognized as EntityReference (lookup) attributes
• Properties ending with '@OData.Community.Display.V1.FormattedValue' are extracted as FormattedValues
• Integer attributes with a corresponding FormattedValue are treated as OptionSetValue (choice fields)

Examples

# Execute a FetchXML query and display results in console
pacx data query -q "<fetch><entity name='account'><attribute name='name'/></entity></fetch>"

# Execute a SQL query
pacx data query -q "SELECT name, accountid FROM account WHERE statecode = 0"

# Execute an OData query
pacx data query -q "accounts?$select=name,accountid&$filter=statecode eq 0&$top=10"

# Load query from file and export to JSON
pacx data query --query-file ./queries/my-query.xml --output ./results/output.json

# Export to Excel and auto-open the file
pacx data query -q "<fetch><entity name='contact'/></fetch>" -f Excel -o ./contacts.xlsx --auto-run

# Export to CSV
pacx data query -q "SELECT fullname, emailaddress1 FROM contact" -f CSV -o ./contacts.csv

Arguments

Long Name	Short Name	Required?	Description	Default value	Valid values
auto-run	run	N	If set, the output file will be automatically opened after the query is executed. Only applicable when an output file is specified.	False	true, false
format	f	N	The format of the output.	JSON	JSON, CSV, XML, Excel
output	o	N	The path to the output file where to store the results of the query. If the format is Excel, this argument is mandatory.	-	String
query	q	N	The query to execute. Can be a FetchXML or SQL query. Mutually exclusive with --query-file.	-	String
query-file	qf	N	Path to a file containing the query to execute. Mutually exclusive with --query.	-	String