gsqlcmd Command-Line Modes

gsqlcmd Command-Line Modes

help

Run gsqlcmd without parameters to get a brief help message.

Use the help mode for detailed console help.

Use the help mode with the online parameter to access online help.

Use the help mode with the chm parameter to launch the HTML help file:

gsqlcmd help chm

exec

Use this mode to execute SQL queries and scripts.

For example:

gsqlcmd exec db application-install.sql

In this case, the utility runs the application-install.sql script against the specified database connection.

You can execute a group of files using a wildcard or a zip archive of SQL scripts. For example:

gsqlcmd exec db application-install-*.sql

gsqlcmd exec db application-install.zip

gsqlcmd processes these files in alphabetical order.

You can create a list of files to execute in a task file and use it like this:

gsqlcmd exec db @task.txt

You can also run an inline SQL query:

gsqlcmd exec db "SELECT * FROM dbo.cashbook" cashbook.csv

In this example, the utility executes the inline query and outputs the data into cashbook.csv.

Use the options /asText, /asCsv, /asHtml, /asXml, and /asJson to convert the output to your desired format.

For example:

gsqlcmd exec db "SELECT * FROM dbo.cashbook" /asjson > cashbook.json

You can use a simpler form to select data from tables and views:

gsqlcmd exec db dbo.cashbook

gsqlcmd automatically appends "SELECT * FROM " for you.

Use the /limit and /offset options to filter output rows:

gsqlcmd exec db dbo.cashbook /limit=10

You can use either the exec or execute keyword, or omit the keyword since exec is the default mode. For example:

gsqlcmd db "SELECT * FROM dbo.cashbook"

However, using the mode with the keyword is slightly faster.

Use the edit-connections mode to create named connections.

You can also specify a complete connection string instead of a named connection.

Here are several examples for .NET, OLE DB, and ODBC connection strings:

gsqlcmd exec "System.Data.SqlClient;Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=SSPI" application-install.sql

gsqlcmd exec "Provider=MSOLEDBSQL;Data Source=.\SQLEXPRESS;Initial Catalog=Test;Integrated Security=SSPI" application-install.sql

gsqlcmd exec "Driver=ODBC Driver 13 for SQL Server;Server=.\SQLEXPRESS;Database=Test;Trusted_Connection=Yes" application-install.sql

gsqlcmd detects OLE DB strings using the "Provider=" prefix and ODBC strings using the "Driver=" prefix.

You can use filenames instead of named connections for queries to file databases and files.

Here are examples for SQLite, DBF, Excel, CSV, and text:

gsqlcmd exec test.db   "SELECT * FROM cashbook"

gsqlcmd exec test.dbf  "SELECT * FROM test.dbf"

gsqlcmd exec test.xlsx "SELECT * FROM [sheet1$]"

gsqlcmd exec test.csv  "SELECT * FROM test.csv"

gsqlcmd exec test.txt  "SELECT * FROM test.txt"

In all these cases, gsqlcmd automatically creates a suitable .NET, OLE DB, or ODBC connection.

You can check the actual connection string using the show-connection mode. For example:

gsqlcmd show-connection test.xlsx ConnectionString

Use the /commandTimeout and /connectionTimeout options to set timeouts.

Use the /noTransaction option to execute scripts in a non-transactional mode.

Use the /trace option to trace executed commands.

select

Use this mode as a synonym for the exec or convert modes.

For example, the select command with a named connection is equivalent to the exec mode:

gsqlcmd select db dbo.cashbook

In this case, gsqlcmd translates the command to:

gsqlcmd exec db "SELECT * FROM dbo.cashbook"

The select mode without a named connection is equivalent to the convert mode.

Here are several examples:

gsqlcmd select https://www.nasdaq.com/symbol/AAPL/dividend-history

gsqlcmd select aapl.json

gsqlcmd select "SELECT loc FROM https://www.gsqlcmd.com/sitemap.xml" /noHeaders

gsqlcmd includes this synonym because the select keyword better describes select operations than exec or convert.

download

Use this mode to download or upload files or execute web requests.

For example:

gsqlcmd download https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.htm

You can use a wildcard for output files if the URL contains a detected file name. For example:

gsqlcmd download https://www.gsqlcmd.com/sitemap.xml .

You can use a simple or headered task file to download a list of URLs.

For example, place URLs to download in a file like sitemap.txt and use:

gsqlcmd download @sitemap.txt .

Here’s a simple batch file that downloads sitemap.xml and its website pages:

@echo off

gsqlcmd download https://www.gsqlcmd.com/sitemap.xml .

gsqlcmd convert "SELECT loc FROM sitemap.xml" sitemap.txt /noHeaders

gsqlcmd download @sitemap.txt .

You can simplify the batch using a pipe:

gsqlcmd convert "SELECT loc FROM https://www.gsqlcmd.com/sitemap.xml" /noHeaders | gsqlcmd download @ .

Use the /check option to check response status codes only.

For example, to get response status codes for all pages in sitemap.xml, use:

gsqlcmd convert "SELECT loc FROM https://www.gsqlcmd.com/sitemap.xml" /noHeaders | gsqlcmd download @ /check

gsqlcmd detects subsequent page URLs and allows downloading multiple pages from the specified initial URLs.

For example, to download all option chain pages from Yahoo Finance, use the following batch file:

@echo off

set url="https://query1.finance.yahoo.com/v7/finance/options/{symbol}"

gsqlcmd download %url% src*.JSON /taskfile=symbols.txt /echoOutputFileName /pages=20

In this example, symbols.txt contains stock symbols like:

Symbol
AAPL
FB
GOOGL

The download mode supports a range of web request options.

For example, you can specify a username and a password for protected resources.

You can also use the /echoOutputFileName and /echoUrl options to track download progress.

convert

Use this mode to download and convert web data or local files.

gsqlcmd supports the following input and output formats: XML, JSON, HTML, CSV, and plain text.

For example:

gsqlcmd convert https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.csv

In this case, the tool downloads the HTML page, extracts data, and saves it to aapl.csv.

To save the downloaded file, use the form with three positional parameters:

gsqlcmd convert https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.htm aapl.csv

gsqlcmd supports many parser options to customize the input data parser and numerous formatting options to customize the output.

You can find many samples in the Downloading and converting to CSV folder of the gsqlcmd download package.

Here’s a batch file that converts downloaded option chains from Yahoo Finance:

@echo off

gsqlcmd convert src*.JSON csv*.CSV ^
        /rootPath=*.*.*.calls,*.*.*.puts ^
        /add=Symbol=FileNameWithoutPage();LoadDate=FileDateTime() ^
        /quoteChar= /DateFormat=yyyy-MM-dd /echoInputFileName

Here’s another example that downloads and converts historical stock prices from MSN Money:

@echo off

set url="https://finance.services.appex.bing.com/Market.svc/ChartAndQuotes?symbols={symbol}&chartType=1y&isEOD=True&isCS=true&isVol=true"

gsqlcmd convert %url% src\{symbol}.JSON csv*.CSV /taskfile=symbols.txt ^
        /rootPath=Chart.Series ^
        /skippedNodes=IsStitched ^
        /add=Symbol={Symbol} ^
        /quoteChar= /DateTimeFormat=yyyy-MM-dd /echoOutputFileName

The symbols.txt file contains stock symbols like:

Symbol
AAPL
FB
GOOGL

You can use task files to specify command line parameters and options.

import

Use this mode to make and execute commands to import data in a single command.

For example, the following command inserts data from data.csv into the dbo.data table:

gsqlcmd import db data.csv /table=dbo.data

You can define generated commands using the /insert, /update, /delete, and /merge options like this:

gsqlcmd import db data.csv /table=dbo.data /insert
gsqlcmd import db data.csv /table=dbo.data /update
gsqlcmd import db data.csv /table=dbo.data /delete
gsqlcmd import db data.csv /table=dbo.data /merge

To execute a stored procedure for every row, specify the stored procedure name in the /table option:

gsqlcmd import db data.csv /table=dbo.usp_insert_data

To execute a custom command for every row, use an inline query or a filename:

gsqlcmd import db data.csv /table=insert-template.sql

You can use any local or web data source in any supported format: plain text, CSV, XML, JSON, or HTML.

For example:

gsqlcmd import db https://www.nasdaq.com/symbol/AAPL/dividend-history /table=dbo.dividends /set=symbol=AAPL

Use the /add option to add fixed values for target columns that are absent in the data source.

Use the /insertIdentity option to insert values into identity columns.

Use the /formatFile option to substitute source column names with names from the format file.

Use the /keys option to define columns for the WHERE clause if primary key columns are absent in the data source.

For example:

gsqlcmd import db data.csv /table=dbo.data /insert
gsqlcmd import db data.csv /table=dbo.data /update /keys=firstName,LastName
gsqlcmd import db data.csv /table=dbo.data /delete /keys=firstName,LastName
gsqlcmd import db data.csv /table=dbo.data /merge  /keys=firstName,LastName

gsqlcmd Enterprise supports importing data from another database in a single command using <connection>::<query> data source.

For example:

gsqlcmd import db db2::select-data.sql /table=dbo.data

You can import data this way from any supported database, for example, from MySQL to SQL Server and vice versa.

You can also get initial parameters for select queries using the /taskfile option. For example:

gsqlcmd import db db2::select-data.sql /table=dbo.data /taskfile=db::get-parameters.sql

In this example, gsqlcmd performs the following steps:

  1. Executes get-parameters.sql against the db connection.
  2. Prepares select-data.sql with its values.
  3. Executes select-data.sql against the db2 connection.
  4. Generates and executes INSERT commands against the db connection.

Note that gsqlcmd executes the command for each row in the task results. Typically, tasks like get-parameters.sql return a single row with columns for each select query parameter.

You can achieve similar results in gsqlcmd Standard, but it requires separate batch, task, and data files.

make

Use this mode to generate INSERT, UPDATE, DELETE, MERGE, EXECUTE, or custom commands for every data source row.

For example, the following commands generate INSERT, UPDATE, DELETE, and MERGE commands based on data.csv to update the dbo.data table:

gsqlcmd make db data.csv data-insert.sql /table=dbo.data /insert
gsqlcmd make db data.csv data-update.sql /table=dbo.data /update
gsqlcmd make db data.csv data-delete.sql /table=dbo.data /delete
gsqlcmd make db data.csv data-merge.sql  /table=dbo.data /merge

To generate EXECUTE commands, use the name of the stored procedure instead of the table:

gsqlcmd make db data.csv data-execute.sql /table=dbo.usp_insert_data

To generate custom commands for every row, use an inline query or a filename:

gsqlcmd make-insert db data.csv data-custom.sql /table=insert-template.sql

You can use any local or web data source in any supported format: text, CSV, XML, JSON, or HTML.

Use the /add option to add fixed values for target columns that are absent in the data source.

Use the /insertIdentity option to insert values into identity columns.

Use the /insertNulls option to retain NULL values in the INSERT commands.

Use the /formatFile option to substitute source column names with names from the format file.

Use the /keys option to define columns for the WHERE clause if primary key columns are absent in the data source.

gsqlcmd Enterprise supports fetching data from another database in a single command using <connection>::<query> data source.

For example:

gsqlcmd make db db2::select-data.sql data-insert.sql /table=dbo.data

You can also get initial parameters for select queries using the /taskfile option. For example:

gsqlcmd make db db2::select-data.sql data-insert.sql /table=dbo.data /taskfile=db::get-parameters.sql

In this example, gsqlcmd performs the following steps:

  1. Executes get-parameters.sql against the db connection.
  2. Prepares select-data.sql with its values.
  3. Executes select-data.sql against the db2 connection.
  4. Generates INSERT commands against the db connection.

You can achieve similar results in gsqlcmd Standard, but it requires separate batch, task, and data files.

make-fmt+

Use this mode to generate a set of files in the following modes: make-fmt, make-create, make-ace, make-bulk, and make-ini.

For example:

gsqlcmd make-fmt+ data.csv

If the format file exists, gsqlcmd uses it as a source for column names. Otherwise, it creates a new format file.

Typically, run gsqlcmd in this mode twice: the first time to generate all files as is, and the second time after editing the format file.

make-fmt

Use this mode to generate format files based on actual data in a CSV file.

For example:

gsqlcmd make-fmt data.csv data.fmt

If data.csv contains the following data:

FirstName;LastName
John;Doe
Jane;Doe

gsqlcmd creates the following data.fmt:

9.0
2
1   SQLCHAR   0  255  ";"          1   "FirstName"                    ""
2   SQLCHAR   0  255  "\r\n"       2   "LastName"                     ""

gsqlcmd generates SQL Server non-XML format files.

You can use such files with the SQL Server OPENROWSET function to import CSV data into SQL Server databases.

Use the make-bulk mode to generate ready-to-use SELECT commands.

You can change column names in the output format file and use the /formatFile option in several gsqlcmd modes.

This is an easy way to substitute source column names with target table column names.

make-create

Use this mode to generate a CREATE TABLE statement based on actual data in a CSV file.

For example:

gsqlcmd make-create data.csv create-table.sql /table=dbo.data /mssql

For the sample data used above, gsqlcmd generates the following code:

CREATE TABLE [dbo].[data] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [FirstName] nvarchar(255) NULL,
    [LastName] nvarchar(255) NULL,
  CONSTRAINT [PK_data_dbo] PRIMARY KEY ([ID])
);
GO
print N'Table [dbo].[data] created';
GO

Use the /table option to specify the target table.

Use the following options to specify the target database platform: /mssql, /mysql, /oracle, /pgsql, /snowflake, and /sqlite.

Use the /formatFile option to substitute source column names with names from the format file.

Use the /keys option to specify primary key fields. For example:

gsqlcmd make-create data.csv create-table.sql /table=dbo.data /mssql /keys=FirstName,LastName

This command generates the following code:

CREATE TABLE [dbo].[data] (
    [FirstName] nvarchar(255) NOT NULL,
    [LastName] nvarchar(255) NOT NULL,
  CONSTRAINT [PK_data_dbo] PRIMARY KEY (
    [FirstName],
    [LastName]
  )
);
GO
print N'Table [dbo].[data] created';
GO

gsqlcmd generates text columns with a default length of 255. Edit the lengths before executing the command.

make-ace

Use this mode to generate an INSERT command with the SQL Server OPENROWSET function to import CSV data into SQL Server databases using the MICROSOFT.ACE.OLEDB.12.0 provider.

For example:

gsqlcmd make-ace data.csv insert-ace.sql /table=dbo.data

The command produces the following result for the test data used above:

INSERT INTO dbo.data
    ( [FirstName]
    , [LastName]
    )
SELECT
    t.[FirstName]
    , t.[LastName]
FROM
    (
    SELECT
        [FirstName] AS [FirstName]
        , [LastName] AS [LastName]
    FROM
        OPENROWSET('MICROSOFT.ACE.OLEDB.16.0',
            'Text;Database=D:\Help\gsqlcmd;HDR=YES;Format=Delimited(;)',
            'SELECT * FROM [data.csv]') t
    ) t

You can manually change the actual data path.

Use the /table option to specify the target table.

Use the /formatFile option to substitute source column names with names from the format file.

Download the ACE.OLEDB provider using the following link:

Install the provider with the same bitness as your installed version of Microsoft Office.

If you have 32-bit Microsoft Office installed on a 64-bit Windows, use gsqlcmd32.exe to execute generated statements.

make-bulk

Use this mode to generate an INSERT command with the SQL Server OPENROWSET function to import CSV data into SQL Server databases.

For example:

gsqlcmd make-bulk data.csv insert-bulk.sql /table=dbo.data /formatFile=data.fmt /inputcodepage=65001

The command produces the following result for the test data used above:

INSERT INTO dbo.data
    ( [FirstName]
    , [LastName]
    )
SELECT
    t.[FirstName]
    , t.[LastName]
FROM
    (
    SELECT
        [FirstName] AS [FirstName]
        , [LastName] AS [LastName]
    FROM
        OPENROWSET(
            BULK 'D:\Help\gsqlcmd\data.csv',
            FORMATFILE = 'D:\Help\gsqlcmd\data.fmt',
            CODEPAGE = '65001',
            FIRSTROW = 2) t
    ) t

Use the /table option to specify the target table.

Use the /formatFile option to substitute source column names with names from the format file.

Use the /inputCodepage option to specify the codepage.

You can use the /relative option to remove absolute paths. gsqlcmd expands paths in the exec mode on the fly.

make-ini

Use this mode to generate and update a declaration for the schema.ini file, which supports importing CSV data into Microsoft Excel and databases using OLE DB providers and ODBC drivers.

For example:

gsqlcmd make-ini data.csv schema.ini

Use the /formatFile option to specify the format file with actual column names.

For example:

gsqlcmd make-ini data.csv schema.ini /formatFile=data.fmt

OLE DB providers and ODBC drivers require the schema.ini file if the executed command contains column names.

For example, the first command does not require schema.ini, while the second does:

gsqlcmd select data.csv "SELECT * FROM data.csv"

gsqlcmd select data.csv "SELECT FirstName, LastName FROM data.csv"

gsqlcmd analyzes executed queries and updates the schema.ini file automatically.

make-select

Use this mode to generate an SQL SELECT statement based on actual data in a CSV file.

For example:

gsqlcmd make-select data.csv select.sql /table=dbo.data /mssql

For the sample data used above, gsqlcmd generates the following code:

SELECT
    t.FirstName
    , t.LastName
FROM
    dbo.data t

You can generate an SQL select command for a CSV file (omit the /table option):

gsqlcmd make-select data.csv select.sql

The select.sql file contains the following code:

SELECT
    t.FirstName
    , t.LastName
FROM
    [data.csv] t

You can edit the file and use the select mode to select the required data from the file:

gsqlcmd select select.sql

get-token

Use this mode to acquire an OAuth2 token interactively.

For example:

gsqlcmd get-token /clientJson=client_secret.json /tokenJson=access_token.json ^
                  /grantType=authorization_code ^
                  /scope=https://www.googleapis.com/auth/webmasters.readonly

In this example, client_secret.json contains the required options except for the grant type and scope.

You can usually export such files from the registered application pages.

For example, a Google Search Console client secret file looks like this:

{"installed":{
    "client_id":"123456789012-abcdefghijklmnopqrstuvwxyz123456.apps.googleusercontent.com",
    "project_id":"searchconsoleapi-123456",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://oauth2.googleapis.com/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"Abcdefghijklmnopqrstuvwx",
    "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
}}

Instead of using a single file, you can specify OAuth options directly.

The access_token.json file specified in the /tokenJson option is the output file for the acquired token.

For example, a Google Search Console token file looks like this:

{
  "access_token": "ya29.abcdefghijklmnopqrstuv-1234567890123456789-abcdefghijklmnopqrstu_123456789012345678-abc-abcdefghijklmnopqrstuvwx-1234567890123",
  "expires_in": 3600,
  "refresh_token": "1/ABCDEFGHIJKLMNOPQRST-abcdefghijklmnopqrstuv",
  "scope": "https://www.googleapis.com/auth/webmasters.readonly",
  "token_type": "Bearer"
}

You can use the same /tokenJson option and file in other operations as an input file.

The default value for the /grantType option is authorization_code.

The values for the /scope option are specific to each API. Refer to the documentation for the API you are using.

refresh-token

Use this mode to refresh an OAuth2 token explicitly.

For example:

gsqlcmd refresh-token /clientJson=client_secret.json /tokenJson=access_token.json

Refer to the comments about the /clientJson and /tokenJson options in the get-token mode discussed above.

Note that gsqlcmd attempts to refresh an access token automatically in other modes if you specify the required options for this operation.

revoke-token

Use this mode to revoke an OAuth2 token.

For example:

gsqlcmd revoke-token /clientJson=client_secret.json /tokenJson=access_token.json

Refer to the comments about the /clientJson and /tokenJson options in the get-token mode discussed above.

get-authcode

Use this mode to acquire an OAuth1 or OAuth2 authorization code for use in third-party applications.

gsqlcmd acquires such codes in the first phase of the get-token mode if required.

For example:

gsqlcmd get-authcode /clientJson=client_secret.json /tokenJson=access_token.json

Refer to the comments about the /clientJson and /tokenJson options in the get-token mode discussed above.

get-schema

Use this mode to query GetSchema of the specified connection and collection.

For example:

gsqlcmd get-schema db tables

You can use a SELECT form like:

gsqlcmd get-schema test.xlsx "SELECT TABLE_NAME FROM tables"

Both examples select data from the tables collection.

Each .NET, OLE DB, or ODBC provider supports a specific set of collections.

Here’s a list of common collections:

  • Tables
  • Views
  • Procedures
  • DataTypes
  • Columns
  • Indexes
  • DataSourceInformation
  • ReservedWords
  • Restrictions

Here’s a list of well-documented collections:

Collections can support or require restrictions.

For example, you can restrict selected columns by a table name.

Add the required restrictions in the necessary positions using a comma as a separator.

For example:

gsqlcmd get-schema test.xlsx columns,,,Sheet1$

Here’s a complete example with a SELECT clause and a named range on a worksheet name with spaces:

gsqlcmd get-schema test.xlsx "SELECT COLUMN_NAME FROM columns,,,'sales data$'Data"

create-database

Use this mode to create SQLite databases.

For example:

gsqlcmd create-database test.db

gsqlcmd detects database types by extensions: SQLite for *.db.

You can specify a named connection instead of the file name.

For example:

gsqlcmd create-database db

You can also create databases using a connection and the CreateDatabase command.

For example:

gsqlcmd exec db CreateDatabase

gsqlcmd includes .NET providers for SQLite.

You can download complete database packages using the following links:

edit-connections

Use this mode to start gConnectionManager to edit named connections in the gsqlcmd.exe.config configuration file.

Use the /local option to edit a configuration file in the current directory. This is the default value.

Named connections from such files are available only in the current directory.

Use the /user option to edit a configuration file in the %LocalAppData%\Gartle\gsqlcmd directory.

Named connections from this file are available only for the current user.

Use the /app option to edit the configuration file in the gsqlcmd installation folder.

Named connections from this file are typically available for any user on the machine.

You can also use the edit keyword as a synonym. For example:

gsqlcmd edit /user

show-connections

Use this mode to list configured named connections.

For example:

gsqlcmd show-connections

You can use the /local, /user, and /app options to show connections from the specified configuration file.

For example:

gsqlcmd show-connections /app

You can also filter connections by the specified platform using the following options:

/mssql, /mysql, /oracle, /pgsql, /snowflake, /sqlite, /dbf, /foxpro, /csv, /excel.

For example:

gsqlcmd show-connections /oracle

show-connection

Use this mode to show connection properties.

For example:

gsqlcmd show-connection db

You can use a database file name or a text file name as a connection.

Use the following values as an additional parameter to get a specified connection string property value:

  • provider
  • defaultConnectionString
  • connectionString
  • serverType
  • server
  • database
  • filename
  • authentication
  • username
  • password

For example:

gsqlcmd show data.csv defaultConnectionString

This command shows the following:

Provider=Microsoft.ACE.OLEDB.16.0;Extended Properties="Text;HDR=Yes;FMT=Delimited"

The example also uses a synonym of the show-connection mode.

show-providers

Use this mode to list available providers.

For example:

gsqlcmd show-providers

You can filter providers by the specified platform using the following options:

/mssql, /mysql, /oracle, /pgsql, /snowflake, /sqlite, /dbf, /foxpro, /csv, /excel.

For example:

gsqlcmd show-providers /excel

This command might produce the following output:

Provider=Microsoft.ACE.OLEDB.16.0;Extended Properties=Excel 12.0
Driver=Microsoft Excel Driver (*.xls, .xlsx, .xlsm, *.xlsb)
DSN=Excel Files

Use gsqlcmd32.exe to list 32-bit providers on 64-bit Windows. For example:

gsqlcmd32 show-providers /excel

This command might produce the following output:

Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 12.0
Driver=Microsoft Excel Driver (*.xls)
DSN=Excel Files

show-roots

Use this mode to find values for the /rootPath option of the JSON and XML parsers.

For example, you can use the following command to get possible roots of the resulting JSON for historical prices from Yahoo Finance:

gsqlcmd show-roots AAPL.JSON

The command produces the following output:

chart
chart.result
chart.result.meta
chart.result.meta.currency
chart.result.meta.symbol
chart.result.meta.exchangeName
...
chart.result.timestamp <--
chart.result.indicators
chart.result.indicators.quote <--
chart.result.indicators.quote.close
chart.result.indicators.quote.high
chart.result.indicators.quote.open
chart.result.indicators.quote.low
chart.result.indicators.quote.volume
chart.result.indicators.adjclose
chart.result.indicators.adjclose.adjclose <--
chart.error

To extract historical prices, you can use the following command, replacing common paths with an asterisk:

gsqlcmd convert AAPL.JSON AAPL.CSV /rootpath=*.*.*.timestamp,*.*.*.quote,*.*.*.*.adjclose

In most cases, gsqlcmd detects the desired columns automatically.

Thus, the following command yields the same results as the previous one:

gsqlcmd convert AAPL.JSON AAPL.CSV

However, specifying root paths with other options like /collapsedNodes, /keptNodes, /requiredColumns, or /skippedNodes allows you to get the result you need.

gsqlcmd outputs columns in source order.

You can change the order, add, remove, or rename columns using the SELECT command like this:

gsqlcmd convert "SELECT 'AAPL' AS symbol, timestamp AS time, open, high, low, close, volume, adjclose FROM AAPL.JSON" AAPL.CSV

show-html-tables

Use this mode to find table numbers for the /rootPath option of the HTML parser.

For example, you can download dividend data using the following command:

gsqlcmd download https://www.nasdaq.com/symbol/AAPL/dividend-history aapl.htm

The aapl.htm file contains the complete web page content.

You can use the following command to clean it and enumerate data tables:

gsqlcmd show-html-tables aapl.htm aapl-tables.htm /openOutput

You will see that the dividend history is in table 3. So, you can use the following command to extract data:

gsqlcmd convert aapl.htm aapl.csv /rootPath=3

In most cases, gsqlcmd detects the desired data tables automatically. Use the /rootPath option to extract data from a specific table.

clean-html

Use this mode to remove scripts, CSS class declarations, attributes, comments, or nodes from HTML files.

For example:

gsqlcmd clean-html aapl.htm aapl-clean.htm /cutComments

You can customize cleaning rules using the configuration file and the following output options: autoCorrectedTags, cutAttributes, cutComments, cutIDs, cutTags.

Note that you can clean files in the download and convert modes using the /cleanHtml option that has the same settings.

del-if-html

Use this mode to delete HTML files returned instead of expected XML or JSON data to skip further processing.

For example:

gsqlcmd del-if-html input*.htm

del-if-empty

Use this mode to delete empty files.

For example:

gsqlcmd del-if-empty output*.csv

This option allows you to delete files that have no expected data so you can download and convert them again.

sleep

Use this mode to wait for a specified amount of milliseconds between requests to avoid being banned by a web server.

For example, to make two requests per second in a batch cycle, use a delay of 300-400 milliseconds:

gsqlcmd sleep 300

gsqlcmd download ...

If you download data from multiple URLs using task files, use the /delay option instead.

In this case, you can specify the exact delay between requests:

gsqlcmd download @task.txt /delay=500

You can use the sleep mode to automate downloading data or executing scripts with a specified interval or at a specific time.

For example, to repeat an action every 60 minutes, use 3600000 milliseconds (60601000):

gsqlcmd sleep 3600000

start <any command>

To start an action at a specified time, use the HH:mm[:ss] format. For example:

gsqlcmd sleep 09:30

<any command>

Note that your computer must not go to sleep mode to start the action.

You can use the /hideWindow or /minimizeWindow options to hide or minimize the console window.

For example, you can minimize the window and sleep for a minute using the following command:

gsqlcmd sleep 60000 /minimizeWindow

stamp

Use this mode to set the current time in batch environment variables.

For example:

for /F %%i in ('gsqlcmd.exe stamp') do set stamp=%%i

The default format is yyyyMMdd-HHmmss. So, the stamp variable has a value like 20190321-124925.

You can customize the output format using a format string as a second parameter.

For example:

for /F "delims==" %%i in ('gsqlcmd.exe stamp "yyyy-MM-dd HH:mm:ss"') do set stamp=%%i

version

Use this mode to show and test the gsqlcmd version.

To show the version, use:

gsqlcmd version

To test the version, specify a minimum required version and use %errorlevel% as a result.

For example:

@echo off

gsqlcmd version 5.1

if errorlevel 3 (
    echo Update gsqlcmd to version 5.1
    goto :EOF
)

gsqlcmd returns errorlevel 3 if the required version is greater and errorlevel 0 if the required version is below or equal.

hide-window

Use this mode to hide the console window.

For example:

gsqlcmd hide-window

Use the show-window mode to show the window.

Note that you can hide the window for any command using the /hideWindow option.

In this case, gsqlcmd shows the window when it finishes the command.

show-window

Use this mode to show the console window hidden with the hide-window mode.

For example:

gsqlcmd show-window

minimize-window

Use this mode to minimize the console window.

For example:

gsqlcmd minimize-window

Use the restore-window mode to restore the window.

Note that you can minimize the window for any command using the /minimizeWindow option.

In this case, gsqlcmd restores the window when it finishes the command.

restore-window

Use this mode to restore the minimized console window.

For example:

gsqlcmd restore-window

auto-update

Use this mode to start the tool to check and install software updates.

For example:

gsqlcmd auto-update

register

Use this mode to start the registration wizard.

For example:

gsqlcmd register

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.