Creating Format Files with gsqlcmd

Creating Format Files with gsqlcmd

Microsoft SQL Server allows using format files with the bcp utility, in the BULK INSERT command, and in the OPENROWSET function to import text data.

gsqlcmd allows generating such format files.

It also allows using the format files with the /formatFile option to define new column names.

For example, aapl.csv has the following content:

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

You can generate a format file using the make-fmt mode:

gsqlcmd make-fmt aapl.csv yahoo1m.fmt

The output yahoo1m.fmt file has the following content:

9.0
7
1   SQLCHAR   0  255  ";"          1   "symbol"                       ""
2   SQLCHAR   0  255  ";"          2   "time"                         ""
3   SQLCHAR   0  255  ";"          3   "open"                         ""
4   SQLCHAR   0  255  ";"          4   "high"                         ""
5   SQLCHAR   0  255  ";"          5   "low"                          ""
6   SQLCHAR   0  255  ";"          6   "close"                        ""
7   SQLCHAR   0  255  "\r\n"       7   "volume"                       ""

gsqlcmd generates non-XML format files.

You can rename columns here and use the file with Microsoft SQL Server and gsqlcmd with the /formatFile option.