Импорт данных CSV в SQL Server

Microsoft SQL Server поддерживает дополнительные собственные способы импорта данных:

Импорт с помощью утилиты bcp;
Импорт с помощью команды BULK INSERT;
Импорт с помощью функции OPENROWSET с опцией BULK;
Импорт с помощью функции OPENROWSET с поставщиками OLE DB.

gsqlcmd позволяет создавать файлы формата и генерировать SQL-код для использования функций OPENROWSET.

Импорт данных CSV с помощью утилиты BCP

Вы можете импортировать данные CSV в Microsoft SQL Server с помощью утилиты bcp, используя команду вида:

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

Параметр -E сохраняет значения идентификаторов.

Вы можете использовать режим make-fmt для создания форматных файлов.

Версии до SQL Server 2016 (13.x) не поддерживают кодировку UTF-8.

Вы можете использовать более новую версию утилиты bcp, или конвертировать данные CSV в кодировку Windows ANSI с помощью опции /outputCodepage, или же использовать другие методы, описанные ниже.

Импорт данных CSV с помощью команды BULK INSERT

Вы можете импортировать данные CSV в Microsoft SQL Server, используя команду BULK INSERT, как например:

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

Вы можете использовать режим make-fmt для создания форматных файлов.

Версии до SQL Server 2016 (13.x) не поддерживают кодировку UTF-8.

Вы можете конвертировать данные CSV в кодировку Windows ANSI с помощью опции /outputCodepage или использовать другие методы, описанные ниже.

Импорт данных CSV с помощью функции OPENROWSET с опцией BULK

Используйте режим make-bulk для создания готового к использованию кода SQL для функции OPENROWSET с параметром BULK.

Например, data.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

Как описано в разделе Создание файлов формата, вы можете создать файл формата с помощью такой команды, как

gsqlcmd make-fmt data.csv data.fmt

Затем используйте следующую ??команду, чтобы сгенерировать код SQL:

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

Результат команды выглядит следующим образом:

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

Вы можете самостоятельно добавить предложения LEFT OUTER JOIN и WHERE, чтобы пропустить импорт существующих строк.

Например:

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

Вы можете настроить файл формата для пропуска или переименования столбцов. Например, установите 0 в столбце 6 файла формата, чтобы пропустить столбец.

Версии до SQL Server 2016 (13.x) не поддерживают кодировку UTF-8.

Вы можете конвертировать данные CSV в кодировку Windows ANSI с помощью опции /outputCodepage или же применить метод, описанный ниже.

См. дополнительные сведения в описании опций /table, /formatFile и /insertIdentity.

Импорт данных CSV с помощью функции OPENROWSET с поставщиками OLE DB

Используйте режим make-ace для создания готового к использованию кода SQL для функции OPENROWSET с поставщиками OLE DB.

Для примера, использованного выше, используйте команду, подобную этой

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

Результат содержит следующую команду:

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

Вы можете изменить MICROSOFT.ACE.OLEDB.12.0 на MICROSOFT.ACE.OLEDB.16.0 или же Microsoft.Jet.OLEDB.4.0 и фактический путь к данным вручную.

Используйте следующие ссылки для загрузки поставщиков ACE OLE DB:

Можно установить провайдер той же разрядности, что и установленный Microsoft Office.

Если у вас установлен 32-разрядный Microsoft Office в 64-разрядной версии Windows, используйте gsqlcmd32.exe для выполнения сгенерированных команд.

Поставщикам Microsoft OLE DB требуется раздел исходного файла, например [data.csv], в файле schema.ini.

Вы можете создавать такие разделы, используя команду, подобную этой

gsqlcmd make-ini data.csv schema.ini

См. подробности в разделе Создание файлов schema.ini.

См. также дополнительные сведения в описании опций /table, /formatFile и /insertIdentity.