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:
- Executes
get-parameters.sql
against thedb
connection. - Prepares
select-data.sql
with its values. - Executes
select-data.sql
against thedb2
connection. - 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:
- Executes
get-parameters.sql
against thedb
connection. - Prepares
select-data.sql
with its values. - Executes
select-data.sql
against thedb2
connection. - 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:
- ODBC Schema Collections
- OLE DB Schema Collections
- SQL Server Schema Collections
- Oracle Schema Collections
- MySQL Schema 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
Contents
- help
- exec
- select
- download
- convert
- import
- make
- get-token
- refresh-token
- revoke-token
- get-authcode
- get-schema
- create-database
- edit-connections
- show-connections
- show-connection
- show-providers
- show-roots
- show-html-tables
- clean-html
- del-if-html
- del-if-empty
- sleep
- stamp
- version
- hide-window
- show-window
- minimize-window
- restore-window
- auto-update
- register