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