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

Импорт данных 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.16.0',
            'Text;Database=D:\data;HDR=YES;Format=Delimited(;)',
            'SELECT * FROM [data.csv]') t
    ) t

Не забудьте вручную изменить путь к данным.

Скачайте поставщика 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.

Этот веб-сайт использует куки. Продолжая использовать веб-сайт, Вы принимаете условия Политики защиты персональных данных.