Automate SQL Operations with Batch and Task Files in gsqlcmd

Let's automate the task of downloading 1-minute stock prices from Yahoo Finance for a list of tickers.

The basic URL format is as follows:

https://query1.finance.yahoo.com/v7/finance/chart/AAPL?range=1d&interval=1m&indicators=quote&includeTimestamps=true

The symbols.txt file contains a list of tickers:

AAPL
FB
GOOGL

Using Batch Files

Here's a simple batch file to fetch data for the tickers listed in symbols.txt:

@echo off

for /F %%i in (symbols.txt) do (

    gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/%%i?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
            %%i.csv /add=Symbol=%%i ^
            /datetimeformat=yyyy-MM-dd /echoUrl

    gsqlcmd sleep 300
)

Use the sleep mode to introduce a delay between requests, helping to avoid IP bans from web servers.

This batch file will download three files: AAPL.CSV, FB.CSV, and GOOGL.CSV.

To download the data into a single output file, modify the batch file as follows:

@echo off

if exist data.csv del data.csv

for /F %%i in (symbols.txt) do (

    gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/%%i?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
            data.csv /append /add=Symbol=%%i ^
            /datetimeformat=yyyy-MM-dd /echoUrl

    gsqlcmd sleep 300
)

This version deletes the output file first and uses the /append option to add data to the existing file.

Using Task Files with /TaskFile Option

The /taskFile option simplifies typical tasks into a single command.

First, add a header to symbols.txt:

Symbol
AAPL
FB
GOOGL

The final command will use the {symbol} parameter instead of the %%i variable:

@echo off

gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
        data.csv /add=Symbol={symbol} ^
        /datetimeformat=yyyy-MM-dd /echoUrl ^
        /delay=300 /taskFile=symbols.txt

This command includes the /taskFile and /delay options (instead of using sleep) and omits the /append option.

If you're using gsqlcmd Enterprise, you can retrieve task values from a database using the format <connection>::<query>.

For example, you can replace symbols.txt with a SELECT query:

@echo off

gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/{symbol}?range=1d&interval=1m&indicators=quote&includeTimestamps=true" ^
        data.csv /add=Symbol={symbol} ^
        /datetimeformat=yyyy-MM-dd /echoUrl ^
        /delay=300 "/taskfile=db::SELECT Symbol FROM dbo.symbols"

Using Positional Task Files

You can use task files instead of positional parameters.

For example, place URLs in a file named urls.txt:

https://www.gsqlcmd.com
https://www.gsqlcmd.com/buy.htm

Then use the command:

gsqlcmd download @urls.txt *.* /echoUrl

The download command requires two positional parameters. In this case, gsqlcmd reads the first parameter from the task file and uses the second parameter from the command line.

You can also specify both URLs and output files in the task file:

https://www.gsqlcmd.com         index.htm
https://www.gsqlcmd.com/buy.htm buy.htm

Then, use a single task file in the command:

gsqlcmd download @urls.txt /echoUrl

In this setup, gsqlcmd treats the first value as the first parameter and the second value as the second parameter.

You can create task files with headers as well:

URL                             FileName
https://www.gsqlcmd.com         index.htm
https://www.gsqlcmd.com/buy.htm buy.htm

Use URL, FileName, and OutputFileName to define positional parameter values.

You can also add extra columns to serve as values for the /set option.