gsqlcmd Parser Options

/asIs

Use this option to disable any special processing of the input data, like converting Yahoo Finance timestamps to datetime values.

/attributedColumns=<col>.<attr>[;...]

Use this option to create output columns from XML attribute values.

For example, Zoho CRM exports data like

<FL val="FirstName">John</FL>
<FL val="LastName">Doe</FL>

To extract values into the FirstName and LastName columns use the following option:

/attributedColumns=FL.val

/collapsedNodes=<node>[;...]

Use this option to include XML child node values into the parent node.

For example, an XML node has the following structure:

<passages>
<passage><hlword>Yandex</hlword> - the search engine that can...</passage>
</passages>

To have a single passages column, use:

/collapsedNodes=passages

/firstRow=<first row>

Use this option to define the first data row in the input file.

/firstRowHasNames

Use this option to point out that the input data has headers if gsqlcmd does not detect them.

This option is opposite to the /noSourceHeaders option.

You can use this option with the false value like

/firstRowHasNames=false

/hasRowNum

Use this option to point out that the first column contains row numbers.

You can omit this option if the first column has the _RowNum name.

Use this option to avoid adding an additional column with the /addRowNum option like:

/hasRowNum /addRowNum

Also, you can use this option in the combination with the /addRowNum option to remove the first column:

/hasRowNum /addRowNum=false

/ignoredTags=<tag>[;...]

Use this option to disable creating columns for the specified XML tags.

For example, an XML node has the following structure:

<passages>
<passage><hlword>Yandex</hlword> - the search engine that can...</passage>
</passages>

To ignore the hlword tag, use:

/ignoredTags=hlword

/inputCodePage=<codepage>

Use this option to define the input file code page.

For example:

/inputcodepage=65001

/inputDateFormat=DMY | MDY

Use this option to define the date-month order of input data.

gsqlcmd tries to detect this order automatically.

However, you need to specify if a column contains dates like 1/12/2019 or 12/1/2019 that do not allow detecting the right order.

/inputSeparator=<separator> | Tab

Use this option to define the input CSV separator.

gsqlcmd detects it automatically in most cases.

/keptNodes=<node>[;...]

Use this option to specify XML and JSON nodes added to output even if they have no values.

This option allows keeping the same output columns for files with small datasets when some columns can have no values.

/noSourceHeaders

Use this option when the first row of CSV and text files or HTML table headers does not contain column names.

In this case, gsqlcmd creates columns with names like F1, F2, and so on.

This option is extremely useful with the financial statements that use years or quarters as column headers.

This option is opposite to the /firstRowHasNames option.

/numberFields=<field>[<separator>...]

Use this option to force converting column values to numbers.

For example:

/numberColumns=Open;High;Low;Close;Volume

Without this option, gsqlcmd skips converting a column if any column cell contains a value that cannot be converted.

/requiredColumns=<name>[;...]

Use this option to define the columns required for output.

gsqlcmd does not process the file if it does not contain the specified columns.

For example, you want to load dividends for a list of symbols.

If a company does not pay dividends, its page is empty. So, you can specify the required output columns to skip such pages.

For example:

gsqlcmd convert https://www.nasdaq.com/symbol/aapl/dividend-history aapl.csv /requiredColumns=ExEffDate;Type

/rootPath=<path>[;...]

Use this option to define the roots of the output columns explicitly.

Here are several examples for XML, JSON, and HTML sources:

/rootPath=.InvoiceTransmission.Invoice
/rootPath=calls,puts
/rootpath=*.*.timestamp,*.*.*.quote,*.*.*.adjclose
/rootPath=1

Without this option, gsqlcmd detects output columns automatically.

Use the show-roots mode to get roots for XML and JSON files.

Use the show-html-tables mode to get table numbers used as roots with HTML files.

gsqlcmd supports multiple roots with JSON and HTML and a single root with XML.

/skippedNodes=<node>[;...]

Use this option to exclude XML or JSON nodes from the output.

You can specify column paths like parent1.parent2.column or suffixes like .column.

For example, to exclude columns with data types, use:

/skippedNodes=.type

/stringFields=<field>[<separator>...]

Use this option to skip converting column values to numbers.

For example, use this option to keep values like "2.10" as strings.

Without this option, the tool converts the value to a number value like 2.1.

For example, to keep versions and zip codes as strings, use the option:

/stringColumns=Version;ZipCode