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
To add the Symbol column, use the following command:
gsqlcmd convert AAPL.json data.csv /append /add=Symbol=AAPL
You can also use a function to extract the symbol from the file name:
gsqlcmd convert AAPL.json data.csv /append /add=Symbol=FileNameWithoutExtension()
This allows you to convert multiple input files in a single command:
gsqlcmd convert *.json data.csv /append /add=Symbol=FileNameWithoutExtension()
gsqlcmd includes a range of functions to simplify common tasks.
/addRowNum
Use this option to add a row number column to the output.
Specify the /hasRowNum option to use an existing first column.
To remove the row number column, use /addRowNum=false.
You can set the initial row number with the /rowNumBase option. For example:
/addRowNum /rowNumBase=1
/dateFormat=<format>
Use this option to specify the format for date values. For example:
/dateFormat=yyyy-MM-dd
/dateTimeFormat=<format>
Use this option to specify the format for datetime values.
Refer to https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring?view=net-8.0 for format string details.
Use double quotes for formats with spaces. For example:
"/dateTimeFormat=yyyy-MM-dd hh:mm:ss"
/escapeChar=<char>
Use this option to set the character that quotes quote characters inside string values. For example:
/escapeChar="
The default is a double quote. Specify an empty value to disable quoting.
Refer to https://datatracker.ietf.org/doc/html/rfc4180 for details on the CSV format.
/lowerCase
Use this option to convert field names to lowercase in the make-fmt and make-create modes.
/noBOM
Use this option to suppress the addition of the Unicode BOM header in the output.
/noHeaders
Use this option to suppress the output of column headers.
For example, to get a list of URLs from a sitemap file, use the following command:
gsqlcmd select "SELECT loc FROM https://www.gsqlcmd.com/sitemap.xml" /noHeaders
/noRound
Use this option to output numbers with all decimal places as they appear in the source.
By default, gsqlcmd rounds numbers to two or four decimal places unless this would result in a loss of precision.
/outputCodePage=<codepage>
Use this option to define the output file's code page. For example:
/outputCodePage=65001
/outputCulture=<name>
Use this option to define the output culture, which contains rules for formatting 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 the output file name has a *.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 for quoting CSV string values. For example:
/quoteChar="
The default is a double quote. Specify an empty value to disable quoting.
Refer to https://datatracker.ietf.org/doc/html/rfc4180 for details on 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, but gsqlcmd can convert relative paths to absolute ones on the fly.
/rowNumBase=<integer>
Use this option to define the starting row number for the output.
Combine this with the /addRowNum option to add the row number column. 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, consider the following XML document:
<?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:
gsqlcmd select test.xml id col1 col2 1 Value11 Value12 2 Value21 Value22
Using the /rowValues option changes the output to:
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 for time values. For example:
/timeFormat=hh:mm:ss
Refer to https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring?view=net-8.0 for format string details.
/upperCase
Use this option to convert field names to uppercase 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.