Configuring Output Formats with gsqlcmd

Contents:

• Common Options
• Configuring CSV Output
• Configuring Plain Text Output
• Configuring HTML Output
• Configuring XML Output
• Configuring JSON Output

Common Options

gsqlcmd lets you convert the output of the exec and convert modes into plain text, CSV, HTML, XML, and JSON formats.

The output file extension determines the default output format:

*.txt  - text
*.csv  - CSV
*.htm  - HTML
*.html - HTML
*.xml  - XML
*.json - JSON

You can explicitly specify the output format using the following options:

/asText
/asCsv
/asHtml
/asXml
/asJson

To define output culture settings, use these options:

/dateFormat=<format>
/dateTimeFormat=<format>
/timeFormat=<format>

/outputCodepage=<codepage>
/outputCulture=<name>
/noBOM

To suppress output column names, use the /noHeaders option. For example:

gsqlcmd convert "SELECT loc FROM sitemap.xml" sitemap.txt /noHeaders

To add a row number column to the output, use the /addRowNum and /rowNumBase options. For example:

gsqlcmd exec db dbo.data /AddRowNum /RowNumBase=1

You can also use the /add option with fixed values and functions to add new columns to the output. For example:

gsqlcmd convert test.csv output.csv /add=Symbol=AAPL;Date=Date()

Configuring CSV Output

Here are some useful options specific to CSV output:

/escapeChar=<char>
/[[output]Separator](formatting-options.md#option-outputSeparator)=<separator> | Tab
/quoteChar=<char>

For example:

gsqlcmd convert http://www.nasdaq.com/symbol/aapl/dividend-history dividends.csv /quoteChar= /separator=,

Configuring Plain Text Output

There are no options specific to plain text output in gsqlcmd. Use the common options described above.

Configuring HTML Output

gsqlcmd supports additional template options for HTML output:

/noTemplate
/placeholder=<placeholder>
/template=<file name>
/title=<title>

The basic command creates an HTML document using the default template. For example:

gsqlcmd convert data.csv data.htm

You can specify the template, output placeholder, and title like this:

gsqlcmd convert data.csv data.htm /template=sales_template.htm /placeholder=year_sales "/title=Sales Report"

To define a placeholder, use the {placeholder} insertion in the HTML template, such as {year_sales}. The {title} placeholder automatically contains the value from the /title option.

Use the /noTemplate option to skip the template.

Configuring XML Output

You can generate an XML document with a simple command. For example:

gsqlcmd convert test.csv test.xml

If test.csv contains the following data:

id;f1;f2
1;data11;data12
2;data21;data22
3;data31;data32

The command produces this output:

<?xml version="1.0" encoding="UTF-8"?>
<table>
<row><id>1</id><f1>data11</f1><f2>data12</f2></row>
<row><id>2</id><f1>data21</f1><f2>data22</f2></row>
<row><id>3</id><f1>data31</f1><f2>data32</f2></row>
</table>

You can customize the root and row node names using the /xmlRoot and /xmlRow options. For example:

gsqlcmd convert test.csv test.xml /xmlroot=data /xmlrow=element

This command produces:

<?xml version="1.0" encoding="UTF-8"?>
<data>
<element><id>1</id><f1>data11</f1><f2>data12</f2></element>
<element><id>2</id><f1>data21</f1><f2>data22</f2></element>
<element><id>3</id><f1>data31</f1><f2>data32</f2></element>
</data>

You can also use empty values with the options and disable the default template using the /noTemplate option. For example:

gsqlcmd convert test.csv test.xml /xmlroot= /xmlrow= /notemplate

This results in:

<id>1</id><f1>data11</f1><f2>data12</f2>
<id>2</id><f1>data21</f1><f2>data22</f2>
<id>3</id><f1>data31</f1><f2>data32</f2>

You can also use the /template and /placeholder options to customize the template and output position. The default template with the default {table} placeholder looks like this:

<?xml version="1.0" encoding="UTF-8"?>
{table}

To change the output encoding, use the /outputCodepage option.

Configuring JSON Output

gsqlcmd generates JSON output as an array. For example, if test.csv contains:

id;f1;f2
1;data11;data12
2;data21;data22
3;data31;data32

The basic command produces:

gsqlcmd convert test.csv test.json

[{"id":1,"f1":"data11","f2":"data12"}
,{"id":2,"f1":"data21","f2":"data22"}
,{"id":3,"f1":"data31","f2":"data32"}
]

To customize the output document, create a template and use the /template and /placeholder options. For example, create template.json:

{"result":{table},"error":null}

Then run:

gsqlcmd convert test.csv test.json /template=template.json

The output file will contain:

{"result":[{"id":1,"f1":"data11","f2":"data12"}
,{"id":2,"f1":"data21","f2":"data22"}
,{"id":3,"f1":"data31","f2":"data32"}
],"error":null}