Configuring Output with gsqlcmd

Configuring Output with gsqlcmd

Contents:

Common Options

gsqlcmd allows converting the output of the exec and convert modes to plain text, CSV, HTML, XML, and JSON.

The output file extension defines the default output format:

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

Use the /asText, /asCsv, /asHtml, /asXml and /asJson options to specify the output format explicitly.

Use the following options to define output culture settings:

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

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

Use the /noHeaders option to suppress adding output column names.

For example:

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

Use the /addRowNum and /rowNumBase options to add a row number column to the output.

For example:

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

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

You can find useful the following options specific to CSV output:

/escapeChar=<char>
/[[output]Separator](formatting-options.htm#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

gsqlcmd has no options specific for plain text output. Use the common options described above.

Configuring HTML Output

gsqlcmd supports additional template options specific 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"

Do define a placeholder, use the {placeholder} insertion in the HTML template like {year_sales}.

{title} is a built-in placeholder that contains the /title option value.

Use the /noTemplate option to skip using the template.

Configuring XML Output

You can use a simple command to get an XML document.

For example:

gsqlcmd convert test.csv test.xml

For example, test.csv has the following contents:

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

The command produces the following 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 use the /xmlRoot and /xmlRow options to change the root and row node names.

For example:

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

<?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 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

<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>

Also, you can use the /template and /placeholder options to customize the template and the output position.

Here is a default template with the default {table} placeholder:

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

You can change output encoding using the /outputCodepage option.

Configuring JSON Output

gsqlcmd generates JSON output as an array.

For example, test.csv contains the following data:

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

The basic command produces the following output:

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 change the output document, create a template and use the /template and /placeholder options.

For example, create template.json:

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

and use the command:

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

The output file will have the following content:

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