Formatting Options

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 may use the following command to add the Symbol column:

gsqlcmd convert AAPL.json data.csv /append /add=Symbol=AAPL

You may 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://docs.microsoft.com/en-us/dotnet/api/system.datetime.tostring 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://tools.ietf.org/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 output of column headers.

For example, you may 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

/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 may 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://tools.ietf.org/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 may use relative paths with gsqlcmd that 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://docs.microsoft.com/en-us/dotnet/api/system.datetime.tostring 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.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.