gsqlcmd Command-Line Modes

gsqlcmd Command-Line Modes

help

Run gsqlcmd without parameters to get a short help.

Use the help mode to get extended help on the console.

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

Use the help mode with the chm parameter to start 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 example, the utility executes the application-install.sql script against the named database connection.

You can use a mask of files to execute or a zip archive of SQL script files. For example:

gsqlcmd exec db application-install-*.sql

gsqlcmd exec db application-install.zip

gsqlcmd executes such files in alphabetical order.

You can create a list of files to execute in a task file and use it in the following form:

gsqlcmd exec db @task.txt

You can use an inline SQL query:

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

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

Use the /asText, /asCsv, /asHtml, /asXml, and /asJson options to convert the output to the 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 appends "SELECT * FROM " automatically.

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

gsqlcmd exec db dbo.cashbook /limit=10

You can use the exec or execute keyword.

Also, you can omit the keyword as exec is the default mode. For example:

gsqlcmd db "SELECT * FROM dbo.cashbook"

However, using the mode with the keyword is a little bit faster.

Use the edit-connections mode to create named connections.

Also, you can specify a complete connection string instead of the named connection.

Below 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 several examples for SQLite, SQL Server Compact, DBF, Excel, CSV, and text:

gsqlcmd exec test.db   "SELECT * FROM cashbook"

gsqlcmd exec test.sdf  "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 of these cases, gsqlcmd creates a suitable .NET, OLE DB, or ODBC connection automatically.

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

gsqlcmd show-connection test.xslx 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 to trace executed commands.

select

Use this mode as a synonym of the exec or convert mode.

For example, the select command with a named connection is a synonym of the exec mode:

gsqlcmd select db dbo.cashbook

Note that gsqlcmd converts this command to the exec mode in the following form:

gsqlcmd exec db "SELECT * FROM dbo.cashbook"

The select mode without the named connection is an equivalent of the convert mode.

Below 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 as the select keyword describes select operations much better than the exec or convert keywords.

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 mask 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, you can place URLs to download in a file like sitemap.txt and use the command:

gsqlcmd download @sitemap.txt *.*

Below is a simple batch file that allows downloading 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 even simplify the batch using a pipe:

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

You can use the /check option to check response status codes only.

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

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

gsqlcmd detects next page URLs and allows downloading multiple pages for the specified initial URLs.

For example, to download all option chain pages from Yahoo Finance, you can 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.

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 example, the tool downloads the HTML page, extracts data, and saves the data to the aapl.csv file.

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 a lot of parser options to customize the parser of input data and a lot of formatting options to customize the output.

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

For example, here is 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 is 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

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, use a name of the stored procedure 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 file name:

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

Note that 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 absent in a 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 the primary key columns are absent in a 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 in this way from any to any supported database, for example, from MySQL to SQL Server and back.

Also, you can 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 does the following:

  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. Usually, tasks like get-parameters.sql return a single row with columns for each select query parameter.

You can get the same results in gsqlcmd Standard. However, this 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 command makes 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 file name:

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

Note that 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 absent in a data source.

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

Use the /insertNulls option to keep 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 the primary key columns are absent in a data source.

gsqlcmd Enterprise supports getting 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

Also, you can 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 does the following:

  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 get the same results in gsqlcmd Standard. However, this requires separate batch, task, and data files.

make-fmt+

Use this mode to generate a set of files generated 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 column name source. Otherwise, it creates a new format file.

Usually, 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

For example, data.csv contains the following data:

FirstName;LastName
John;Doe
Jane;Doe

For sample data, 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.

It is an easy way to substitute source column names with the 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, /sqlce, /mysql, /oracle, /db2, /nuodb, /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 the default length 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 change the actual data path manually.

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 following link to download the ACE.OLEDB provider:

You can install the provider of the same bitness as Microsoft Office installed.

If you have 32-bit Microsoft Office installed on 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 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 in an interactive mode.

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 sample, client_secret.json contains the required options except for the grant type and scope.

Usually, you can 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 the use of a single file, you can specify OAuth options directly.

The access_token.json file, specified in the /tokenJson option, is an 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 of the /grantType option is authorization_code.

The /scope option values are specific for every API. Refer to the used API documentation.

refresh-token

Use this mode to refresh an OAuth2 token explicitly.

For example:

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

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

Note that gsqlcmd tries to refresh an access token in other modes automatically 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

See 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 to 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

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

Below is a list of the common collections:

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

Below is a list of well-documented collections:

Collections can support or require using restrictions.

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

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

For example:

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

Below is 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 and SQL Server Compact databases.

For example:

gsqlcmd create-database test.db

gsqlcmd detects database types by extensions: SQLite for \*.db and SQL Server Compact for \*.sdf.

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

For example:

gsqlcmd create-database db

Also, you have an alternative way to create databases using a connection and the CreateDatabase command.

For example:

gsqlcmd exec db CreateDatabase

gsqlcmd includes .NET providers for SQLite and SQL Server Compact 4.0.

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 value is the default.

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

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

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

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

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

Also, you can 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 of the specified configuration file.

For example:

gsqlcmd show-connections /app

Also, you can use the following options to filter connections of the specified platform:

/mssql, /sqlce, /mysql, /oracle, /db2, /nuodb, /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

The 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 use the following options to filter provider of the specified platform:

/mssql, /sqlce, /mysql, /oracle, /db2, /nuodb, /pgsql, /snowflake, /sqlite, /dbf, /foxpro, /csv, /excel

For example:

gsqlcmd show-providers /excel

This command can have 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

The command has 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.

So, the following command has 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 getting 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 the 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 deleting files that have no expected data to download and convert them once again.

sleep

Use this mode to wait for a specified amount of milliseconds between requests to avoid banning your IP by a web server.

For example, to make two requests per second in a batch cycle, use a delay in 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 (60*60*1000):

gsqlcmd sleep 3600000

start <any command>

To start an action at the 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 the 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.