gsqlcmd Formatting Options

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.

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.