Creating Format Files with gsqlcmd

Microsoft SQL Server supports format files with the bcp utility, the BULK INSERT command, and the OPENROWSET function for importing text data.

You can generate these format files using gsqlcmd. Additionally, you can use the format files with the /formatFile option to define new column names.

For example, consider the following content in aapl.csv:

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 file, yahoo1m.fmt, will contain the following:

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 in the format file and use it with Microsoft SQL Server and gsqlcmd via the /formatFile option.