Импорт данных 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.