gsqlcmd

The best-in-class command-line tool

to download, convert, import, export, and synchronize

1. Download

gsqlcmd allows downloading data using URL templates with parameters from task files.

For example, the following command downloads historical stock prices from Yahoo Finance:

set url="https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=2y&interval=1d"

gsqlcmd download %url% {symbol}.json /taskfile=symbols.txt

Moreover, gsqlcmd allows downloading next pages automatically.

The following command loads all options chains for every stock symbol from Yahoo Finance:

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

gsqlcmd download %url% *.JSON /taskfile=symbols.txt /pages=50

You may download a list of URLs at once.

For example, the following command extracts URLs from sitemap.xml and download all pages:

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

gsqlcmd download @sitemap.txt *.* /echoURL

2. Convert

gsqlcmd allows converting JSON, XML, HTML, CSV, and plain text data.

A simple conversion command looks like

gsqlcmd convert *.json *.csv

However, you may use a lot of parser options and functions to tune the result.

For example, the following command extracts historical stock prices, adding a stock symbol:

gsqlcmd convert *.json *.csv ^
        /rootpath=*.*.timestamp,*.*.*.quote,*.*.*.adjclose ^
        /add=Symbol=FileNameWithoutExtension()

Moreover, you may use SQL scripts to download and convert data.

For example, the following command downloads and extracts historical stock prices from MSN Money:

gsqlcmd convert select.sql data.csv /taskfile=symbols.txt /echoURL ^
        /referer=https://www.msn.com/en-us/money/ ^
        /rootPath=Chart.Series /skippedNodes=IsStitched

Where select.sql has the following content:

SELECT
    '{symbol}' AS [symbol]
    , T AS [date], Op AS [open], Hp AS [high], Lp AS [low], P AS [close], V AS volume
FROM
    https://finance-services.msn.com/Market.svc/ChartAndQuotes?symbols={symbol}&chartType=1y

3. Import

gsqlcmd allows generating INSERT, UPDATE, MERGE and DELETE SQL commands.

For example, the following command creates the merge commands to import historical prices:

gsqlcmd make /merge db *.csv *.merge.sql /table=historical_prices /keys=symbol,date

Where db is a named connection string defined in the configuration file.

You may generate commands for any supported database platform, including SQL Server, MySQL, and SQLite.

Then, you may execute generated SQL commands to import data:

gsqlcmd exec db *.merge.sql

You may use a single import mode instead of step-by-step download, convert, make, and exec modes.

For example, you may import historical prices from Yahoo Finance into a database using a command:

gsqlcmd import /merge db select.sql /table=historical_prices /taskfile=symbols.txt ^
        /rootpath=*.*.timestamp,*.*.*.quote

Where select.sql has the following code:

SELECT
    '{symbol}' AS [symbol]
    , [Timestamp] AS [date]
    , [Open] AS [open], High AS [high], [Low] AS [low], [Close] AS [close]
    , Volume AS volume
FROM
    https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=2y&interval=1d

Importing data from a database to database has a simple form:

gsqlcmd import /merge db2 db1::dbo.historical_prices /table=historical_prices

This command selects data from dbo.historical_prices in db1 and merges data into historical_prices in db2.

You may customize selecting data using SQL scripts like

gsqlcmd import /merge db2 db1::select.sql /table=historical_prices

Where select.sql can contain regular SELECT commands as well as EXEC commands to execute stored procedures.

4. Export

gsqlcmd allows exporting database data into CSV, JSON, XML, HTML, and plain text.

For example:

gsqlcmd exec db "SELECT * FROM historical_prices" historical_prices.csv

Also, you may use templates to customize the JSON, XML, and HTML output. For example:

gsqlcmd exec db export.sql sales.htm /template=template.htm /placeholder={sales}

You may use these features on any supported platform including running with xp_cmdshell in Microsoft SQL Server.

5. Synchronize

gsqlcmd allows implementing various synchronization scenarios using the exec and import modes.

Below is a simple case of dual-way synchronization using GUID primary key columns in the merge mode:

gsqlcmd import db2 db1::table1 /table=table2 /merge

gsqlcmd import db1 db2::table2 /table=table1 /merge

You can synchronize data across multiple databases on multiple platforms.

For example, you may easily synchronize data between online MySQL and on-premise SQL Server or Oracle databases.

Feature Comparison by Editions

FeatureFree
Version
Personal
Edition
Standard
Edition
Enterprise
Edition
Modes
Executing SQL queries and scripts
Exporting database data and converting web and text data to text
Exporting database data to CSV, HTML, XML, and JSON
Converting web and text data to CSV, HTML, XML, and JSONup to 5 rows
Importing data from the web and files to databasesup to 5 rows
Importing data from a database to database using a single commandxxx
Synchronizing database datax
Making INSERT, UPDATE, MERGE and DELETE commandsup to 5 rows
Editing database contents using the /toFiles and /fromFiles optionsup to 5 rows
Downloading files
Supported database platforms and data sources
SQL Server, Oracle, DB2, MySQL, PostgreSQL, NuoDB, Snowflake
SQLite, SQL Server Compact, DBF, FoxPro, Excel
Plain text, CSV, HTML, XML, JSON
Licensing
Commercial usex
Annual subscription, per computer, USD 149524953995
Lifetime license, per computer, USDFree299549957995

Next Steps

The gsqlcmd download package includes examples.

You will find ready-to-use solutions to import historical prices, option chains, and financial data from Yahoo Finance and MSN Money.

Also, try other examples as well. Refer to the online documentation to detailed descriptions.

Download

You can use gsqlcmd for free and activate the trial version to try paid edition features later when you need.

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.