gsqlcmd | Importing Data to SQL Server (bcp, BULK INSERT, OPENROWSET)

Microsoft SQL Server supports additional native ways to import 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.

gsqlcmd allows creating format files and generating codes to use the OPENROWSET functions.

Importing CSV Data using the BCP utility

You may import CSV data into Microsoft SQL Server with the bcp utility using a command like this:

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

The -E option keeps identity values.

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

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

You may use a newer bcp utility version, convert CSV data into Windows ANSI encoding using the /outputCodepage option, or use other methods described below.

Importing CSV Data using the BULK INSERT command

You may import CSV data into Microsoft SQL Server using the BULK INSERT command like this:

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

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

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

You may convert CSV data into Windows ANSI encoding using the /outputCodepage option or use other methods described below.

Importing CSV Data using the OPENROWSET function with the BULK option

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

For example, 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

As described in the Creating Format Files topic, you may create a format file using a command like

gsqlcmd make-fmt data.csv data.fmt

Then, use a command like this to generate an SQL code:

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

The result looks 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 may add LEFT OUTER JOIN and WHERE clauses to skip importing existing rows.

For example:

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

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

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

You may convert CSV data into Windows ANSI encoding using the /outputCodepage option or use the method described below.

See additional details in the /table, /formatFile, and /insertIdentity options.

Importing CSV Data using the OPENROWSET function with OLE DB providers

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

For the example used above, use a command like this

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

The result has the following content:

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.12.0',
            'Text;Database=D:\data;HDR=YES;Format=Delimited(;)',
            'SELECT * FROM [data.csv]') t
    ) t

You may change MICROSOFT.ACE.OLEDB.12.0 to MICROSOFT.ACE.OLEDB.16.0 or Microsoft.Jet.OLEDB.4.0 and the actual data path manually.

Use the following links to download ACE OLE DB providers:

You can install the provider of the same bitness as Microsoft Office installed.

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

Microsoft OLE DB providers require a source file section like [data.csv] in the schema.ini file.

You may generate such sections using a command like this

gsqlcmd make-ini data.csv schema.ini

See details in the Creating SCHEMA.INI topic.

See also additional details in the /table, /formatFile, and /insertIdentity options.