Microsoft SQL Server supports additional native ways to import data:
gsqlcmd allows creating format files and generating codes to use the OPENROWSET functions.
You can import CSV data into Microsoft SQL Server with the bcp utility using a command like this:
bcp "dbo.data" in data.csv -S . -d <database> -T -f data.fmt -E
The -E option keeps identity values.
You can use the make-fmt mode to create format files.
Versions prior to SQL Server 2016 (13.x) do not support UTF-8 encoding.
You can use a newer bcp utility version, convert CSV data into Windows ANSI encoding using the /outputCodepage option, or use other methods described below.
You can import CSV data into Microsoft SQL Server using the BULK INSERT command like this:
BULK INSERT dbo.data FROM 'd:\data\data.csv' WITH (FORMATFILE='d:\data\data.fmt', CODEPAGE=65001)
You can use the make-fmt mode to create format files.
Versions prior to SQL Server 2016 (13.x) do not support UTF-8 encoding.
You can convert CSV data into Windows ANSI encoding using the /outputCodepage option or use other methods described below.
Use the make-bulk mode to generate a ready-to-use SQL code with the OPENROWSET function with the BULK option.
For example, data.csv contains the following data:
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
As described in the Creating Format Files topic, you can create a format file using a command like
gsqlcmd make-fmt data.csv data.fmt
Then, use a command like this to generate an SQL code:
gsqlcmd make-bulk data.csv insert.bulk.sql /table=dbo.data /formatFile=data.fmt
The result looks like this:
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
You can add LEFT OUTER JOIN and WHERE clauses to skip importing existing rows.
For example:
LEFT OUTER JOIN dbo.data s ON s.symbol = t.symbol AND s.time = t.time WHERE s.symbol IS NULL
You can configure the format file to skip or rename columns. For example, set 0 in column 6 of the format file to skip the column.
Versions prior to SQL Server 2016 (13.x) do not support UTF-8 encoding.
You can convert CSV data into Windows ANSI encoding using the /outputCodepage option or use the method described below.
See additional details in the /table, /formatFile, and /insertIdentity options.
Use the make-ace mode to generate a ready-to-use SQL code with the OPENROWSET function with OLE DB providers.
For the example used above, use a command like this
gsqlcmd make-ace data.csv insert.ace.sql /table=dbo.data /formatFile=data.fmt
The result has the following content:
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
You can change MICROSOFT.ACE.OLEDB.12.0 to MICROSOFT.ACE.OLEDB.16.0 or Microsoft.Jet.OLEDB.4.0 and the actual data path manually.
Use the following links to download ACE OLE DB providers:
You can install the provider of the same bitness as Microsoft Office installed.
If you have 32-bit Microsoft Office installed on 64-bit Windows, use gsqlcmd32.exe to execute generated statements.
Microsoft OLE DB providers require a source file section like [data.csv] in the schema.ini file.
You can generate such sections using a command like this
gsqlcmd make-ini data.csv schema.ini
See details in the Creating SCHEMA.INI topic.
See also additional details in the /table, /formatFile, and /insertIdentity options.