gsqlcmd

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 can 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.savetodb.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 can 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 can 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 can generate commands for any supported database platform, including SQL Server, MySQL, and SQLite.

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

gsqlcmd exec db *.merge.sql

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

For example, you can 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 can 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 can 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 can 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 can easily synchronize data between online MySQL and on-premise SQL Server or Oracle databases.