Contents:
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()
You can find useful the following options specific to CSV output:
/escapeChar=<char> /[output]Separator=<separator>|Tab /quoteChar=<char>
For example:
gsqlcmd convert http://www.nasdaq.com/symbol/aapl/dividend-history dividends.csv /quoteChar= /separator=,
gsqlcmd has no options specific for plain text output. Use the common options described above.
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.
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.
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 a 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}