Creating Format Files

Creating Format Files

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 the format files. Also, it 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 may 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 may rename columns here and use it with Microsoft SQL Server and gsqlcmd with the formatFile option.

This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.