Converting Data with gsqlcmd

gsqlcmd allows you to convert table data from local files and web resources.

The convert mode has the following syntax:

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

Where <input> can be one of the following:

<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 example, gsqlcmd downloads and converts data.

In the second example, it downloads data, saves the file, and then converts it. This is useful for storing files for later parsing.

In the third example, gsqlcmd simply converts the specified file.

You can customize parser and output options to achieve the desired results.

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

/add=Symbol=AAPL /quoteChar=

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

Automating

You can automate the process of downloading and converting data.

For example, create a task file named symbols.txt:

Symbol
AAPL
FB
GOOGL

This file contains the Symbol column, allowing you to use its values in the {symbol} parameter.

Here's 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

Refer to Using Batch Files and Tasks for more details.

Select

gsqlcmd supports a simple SELECT form in the input queries.

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

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=

This will produce:

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 for importing data into a database.

You can also use the import mode, which combines the functionalities of the convert, make, and exec modes.