Simplify SQL Server Data Importing with gsqlcmd

Microsoft SQL Server offers several native methods for importing data:

Import using the bcp utility
Import using the BULK INSERT command
Import using the OPENROWSET function with the BULK option
Import using the OPENROWSET function with OLE DB providers

The gsqlcmd tool allows you to create format files and generate code for using the OPENROWSET functions.

Importing CSV Data using the BCP Utility

To import CSV data into Microsoft SQL Server with the bcp utility, use a command like this:

bcp "dbo.data" in data.csv -S . -d <database> -T -f data.fmt -E

The -E option preserves identity values.

You can use the make-fmt mode to create format files.

Note that versions prior to SQL Server 2016 (13.x) do not support UTF-8 encoding.

You can use a newer version of the bcp utility to convert CSV data to Windows ANSI encoding with the /outputCodepage option, or explore other methods described below.

Importing CSV Data using the BULK INSERT Command

To import CSV data into Microsoft SQL Server using the BULK INSERT command, use the following syntax:

BULK INSERT dbo.data FROM 'd:\data\data.csv' WITH (FORMATFILE='d:\data\data.fmt', CODEPAGE=65001)

You can use the make-fmt mode to create format files.

As with the bcp utility, versions before SQL Server 2016 (13.x) do not support UTF-8 encoding.

You can convert CSV data to Windows ANSI encoding using the /outputCodepage option or apply other methods outlined below.

Importing CSV Data using the OPENROWSET Function with the BULK Option

Use the make-bulk mode to generate SQL code for the OPENROWSET function with the BULK option.

For example, if data.csv contains the following data:

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 create a format file with a command like:

gsqlcmd make-fmt data.csv data.fmt

Then, generate the SQL code with:

gsqlcmd make-bulk data.csv insert.bulk.sql /table=dbo.data /formatFile=data.fmt

The resulting SQL will look like this:

INSERT INTO dbo.data
    ( [symbol]
    , [time]
    , [open]
    , [high]
    , [low]
    , [close]
    , [volume]
    )
SELECT
    t.[symbol]
    , t.[time]
    , t.[open]
    , t.[high]
    , t.[low]
    , t.[close]
    , t.[volume]
FROM
    (
    SELECT
        [symbol] AS [symbol]
        , [time] AS [time]
        , [open] AS [open]
        , [high] AS [high]
        , [low] AS [low]
        , [close] AS [close]
        , [volume] AS [volume]
    FROM
        OPENROWSET(
            BULK 'D:\data\data.csv',
            FORMATFILE = 'D:\data\data.fmt',
            CODEPAGE = '1251',
            FIRSTROW = 2) t
    ) t

You can add a LEFT OUTER JOIN and WHERE clauses to skip importing existing rows, like this:

    LEFT OUTER JOIN dbo.data s ON s.symbol = t.symbol AND s.time = t.time
WHERE
    s.symbol IS NULL

You can also configure the format file to skip or rename columns. For example, set 0 in column 6 of the format file to skip that column.

As noted earlier, versions before SQL Server 2016 (13.x) do not support UTF-8 encoding. You can convert CSV data to Windows ANSI encoding using the /outputCodepage option or apply other methods described below.

Refer to the /table, /formatFile, and /insertIdentity options for more details.

Importing CSV Data using the OPENROWSET Function with OLE DB Providers

Use the make-ace mode to generate SQL code for the OPENROWSET function with OLE DB providers.

For the example above, run:

gsqlcmd make-ace data.csv insert.ace.sql /table=dbo.data /formatFile=data.fmt

The resulting SQL will look like this:

INSERT INTO dbo.data
    ( [symbol]
    , [time]
    , [open]
    , [high]
    , [low]
    , [close]
    , [volume]
    )
SELECT
    t.[symbol]
    , t.[time]
    , t.[open]
    , t.[high]
    , t.[low]
    , t.[close]
    , t.[volume]
FROM
    (
    SELECT
        [symbol] AS [symbol]
        , [time] AS [time]
        , [open] AS [open]
        , [high] AS [high]
        , [low] AS [low]
        , [close] AS [close]
        , [volume] AS [volume]
    FROM
        OPENROWSET('Microsoft.ACE.OLEDB.16.0',
            'Text;Database=D:\data;HDR=YES;Format=Delimited(;)',
            'SELECT * FROM [data.csv]') t
    ) t

You can manually adjust the actual data path as needed.

To download the ACE OLE DB provider, visit:

Microsoft Access Database Engine 2016 Redistributable

Make sure to install the provider that matches the bitness of your installed Microsoft Office.

If you have 32-bit Microsoft Office on a 64-bit Windows system, use gsqlcmd32.exe to execute the generated statements.

Microsoft OLE DB providers require a section in the schema.ini file for the source file, like [data.csv]. You can generate this section with a command like:

gsqlcmd make-ini data.csv schema.ini

For more details, check the Creating SCHEMA.INI topic.

Refer to the /table, /formatFile, and /insertIdentity options for additional information.