Converting Data with gsqlcmd

gsqlcmd allows converting table data from local files and web resources.

The convert mode has the following form:

gsqlcmd convert [<input> [<output> [<output2>]]]

where input:

<query> | <file> | <mask> | @[<task>] | <url>

gsqlcmd supports the following input and output formats: plain text, HTML, XML, JSON, and CSV

Here are examples of the three basic forms:

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

gsqlcmd convert "https://query1.finance.yahoo.com/v7/finance/chart/AAPL?range=1d&interval=1m&indicators=quote&includeTimestamps=true" aapl.json aapl.csv

gsqlcmd convert aapl.json aapl.csv

In the first form, gsqlcmd downloads and converts data.

In the second form, gsqlcmd downloads data, saves the file, and converts data. You can use it to store files to reparse later.

In the third form, gsqlcmd converts the file.

You can customize parser and output options to get the required results.

For example, the following options add the Symbol column to the output and suppress the use of double quotes:

/add=Symbol=AAPL /quoteChar=

You can find a lot of ready-to-use samples in the Downloading and converting to CSV folder of the downloaded gsqlcmd package.

Automating

You can automate downloading and converting data.

For example, you can create a task file like symbols.txt:

Symbol
AAPL
FB
GOOGL

The file contains the Symbol column. So, you can use its values in the {symbol} parameter.

Here is a simple batch file that downloads and converts 1-minute intraday stock prices from Yahoo Finance:

@echo off

set url="https://query1.finance.yahoo.com/v7/finance/chart/**{symbol}**?range=1d&interval=1m&indicators=quote&includeTimestamps=true"

gsqlcmd convert %url% src\{symbol}.JSON csv*.CSV /taskfile=symbols.txt ^
        /add=Symbol={symbol} ^
        /quoteChar= /echoOutputFileName

See Using Batch Files and Tasks for details.

Select

gsqlcmd supports a simple SELECT form in the input queries.

For example, the basic convert command produces the following result:

gsqlcmd convert aapl.json aapl.csv

Close;High;Low;Open;Timestamp;Volume
200.3800;200.4500;199.68;200.3200;2019-04-09 13:30:00;1332001
200.6920;200.8850;200.32;200.3726;2019-04-09 13:31:00;351343

You can use the following command to add the symbol column, rename and reorder columns:

gsqlcmd convert "SELECT 'AAPL' AS symbol, timestamp AS time, open, high, low, close, volume FROM aapl.json" aapl.csv /quoteChar=

symbol;time;open;high;low;close;volume
AAPL;2019-04-09 13:30:00;200.3200;200.4500;199.68;200.3800;1332001
AAPL;2019-04-09 13:31:00;200.3726;200.8850;200.32;200.6920;351343

Additional Notes

Use the make mode to generate SQL commands to import data to a database.

Also, you can use the import mode that combines the convert, make, exec modes.