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