gsqlcmd Formatting Options
/add=<header>=<value | function>[;...]
Use this option to add new output columns with fixed values.
For example, AAPL.json contains the following columns:
Adjclose;Close;High;Low;Open;Timestamp;Volume
The output data.csv contains the following columns:
Symbol;Timestamp;Open;High;Low;Close;Volume;Adjclose
You can use the following command to add the Symbol column:
gsqlcmd convert AAPL.json data.csv /append /add=Symbol=AAPL
You can use a function to extract the symbol from a file name:
gsqlcmd convert AAPL.json data.csv /append /add=Symbol=FileNameWithoutExtension()
So, functions allow converting multiple input files in a single command like
gsqlcmd convert *.json data.csv /append /add=Symbol=FileNameWithoutExtension()
gsqlcmd includes a range of functions to solve typical tasks easily.
/addRowNum
Use this option to add the row number column to the output.
Specify the /hasRowNum option to use an existing first column.
Use /addRowNum=false to remove the row number column.
Use the /rowNumBase option to set the initial row number.
For example:
/addRowNum /rowNumBase=1
/dateFormat=<format>
Use this option to specify the format of date values.
For example:
/dateFormat=yyyy-MM-dd
/dateTimeFormat=<format>
Use this option to specify the format of datetime values.
See https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring?view=net-6.0 about the format string.
Use double quotes to specify formats with spaces. For example:
"/dateTimeFormat=yyyy-MM-dd hh:mm:ss"
/escapeChar=<char>
Use this option to set the character used to quote the quote characters inside string values.
For example:
/escapeChar="
The default value is a double quote. Specify an empty value to disable quoting.
See https://datatracker.ietf.org/doc/html/rfc4180 about the CSV format.
/lowerCase
Use this option to convert field names to lower case letters in the make-fmt and make-create modes.
/noBOM
Use this option to suppress adding the Unicode BOM header to the output.
/noHeaders
Use this option to suppress the output of column headers.
For example, you can use the following command to get a list of URLs from a sitemap file:
gsqlcmd select "SELECT loc FROM https://www.gsqlcmd.com/sitemap.xml" /noHeaders
/noRound
Use this option to output numbers with all decimal places as in the source.
By default, gsqlcmd rounds numbers to two or four decimal places unless this results in a loss of precision.
/outputCodePage=<codepage>
Use this option to define the output file code page.
For example:
/outputCodePage=65001
/outputCulture=<name>
Use this option to define the output culture that contains rules to format datetime and number values.
For example:
/outputCulture=en-GB
/[outputFormat=]asTxt | asText | asCsv | asHtm | asHtml | asXml | asJson
Use these options to specify the output format.
You can omit this option if you specify the output file name with the *.txt, *.csv, *.htm, *.html, *.xml, or *.json extension.
/[output]separator=<separator> | tab
Use this option to define the output CSV separator.
For example:
gsqlcmd select data.xml data.csv /separator=,
The default separator is a semicolon.
Use the Tab value to specify the tab character.
/quoteChar=<char>
Use this option to define a character used for quoting CSV string values.
For example:
/quoteChar="
The default value is a double quote. Specify an empty value to disable quoting.
See https://datatracker.ietf.org/doc/html/rfc4180 about the CSV format.
/relative
Use this option to remove absolute paths in the BULK queries generated in the make-bulk mode.
Microsoft SQL Server requires absolute paths.
However, you can use relative paths as gsqlcmd extends relative paths to absolute ones on the fly.
/rowNumBase=<integer>
Use this option to define a row number of the first output row.
Use the /addRowNum option to add the row number column to the output.
For example:
/addRowNum /rowNumBase=1
The default value is 0.
/rowValues
Use this option to transpose XML and JSON data into key-value pairs.
For example, an XML document has the following content:
<?xml version="1.0"?>
<root>
<row id="1">
<col1>Value11</col1>
<col2>Value12</col2>
</row>
<row id="2">
<col1>Value21</col1>
<col2>Value22</col2>
</row>
</root>
The regular command returns the following result:
gsqlcmd select test.xml id col1 col2 1 Value11 Value12 2 Value21 Value22
The command with the /rowValues option returns the following result:
gsqlcmd select test.xml /rowValues Key Value root.row.0.#id 1 root.row.0.col1 Value11 root.row.0.col2 Value12 root.row.1.#id 2 root.row.1.col1 Value21 root.row.1.col2 Value22
/timeFormat=<format>
Use this option to specify the format of time values.
For example:
/timeFormat=hh:mm:ss
See https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring?view=net-6.0 about the format string.
/upperCase
Use this option to convert field names to upper case letters in the make-fmt and make-create modes.
/xmlRoot=<name>
Use this option to change the root node name in the XML output.
/xmlRow=<name>
Use this option to change the row node name in the XML output.