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