SQL SERVER導入Excel csv

CSV有, 逗號分隔，tab分隔，但是一個字段里的字符串任然包含逗號和tab，導致分列不準確，

"a,　　b"    這是一個字段值，用""拆分最準確，用2022 format csv試試，2019支持format csv 導入完美。2008/2014不支持該語法。

用 KSQL里的導入CSV準確分割，就是速度慢

WIN1064電腦，sql server 2014/2019 導入工具請用開始菜單里的64位，因為sql SMS數據庫右鍵導入默認是32位。

https://stackoverflow.com/questions/40360932/microsoft-ace-oledb-16-0-provider-is-not-registered-on-the-local-machine-sys

1. Go [here][1], download Microsoft Access Database Engine 2016 Redistributable and install
2. Close SQL Server Management Studio
3. Go to Start Menu -> Microsoft SQL Server 2017 -> SQL Server 2017 Import and Export Data (64-bit)
4. Open the application and try to import data using the "Excel 2016" option, it should work fine.

在64位的機器上安裝64位的
Microsoft.ACE.OLEDB.12.0

SQL Management Studio:SQL服務器-->服務器對象-->鏈接服務器-->Microsoft.ACE.OLEDB.12.0 屬性-->常規-->選中：1、動態參數；2、允許進程內

http://www.microsoft.com/zh-cn/download/details.aspx?id=13255

 無法初始化鏈接服務器“(null)”的 OLE DB 訪問接口“Microsoft.ACE.OLEDB.16.0”的數據源對象

未在本地計算機上注冊“Microsoft.ACE.OLEDB.12.0”提供程序

鏈接服務器 "(null)" 的 OLE DB 訪問接口 "Microsoft.ACE.OLEDB.12.0" 報錯。提供程序未給出有關錯誤的任何信息。
無法初始化鏈接服務器“(null)”的 OLE DB 訪問接口“Microsoft.ACE.OLEDB.12.0”的數據源對象。

2016

https://www.microsoft.com/en-us/download/details.aspx?id=54920

2010

https://www.microsoft.com/en-us/download/details.aspx?id=13255

2007

https://www.microsoft.com/en-us/download/details.aspx?id=23734

注意下載那個64位的：

AccessDatabaseEngine_X64.exe

https://zhidao.baidu.com/question/918558518828001539.html

 https://blog.csdn.net/www_rsqdz_net/article/details/79886942

http://www.rzrgm.cn/habin/archive/2012/08/01/2618845.html

http://www.rzrgm.cn/NichkChang/p/6381309.html

 Microsoft.ACE.OLEDB.12.0，沒安裝office2010也好了，安裝這個AccessDatabaseEngine_X64.exe就可以了

 --開啟導入功能
    exec sp_configure 'show advanced options',1
    reconfigure
    exec sp_configure 'Ad Hoc Distributed Queries',1
    reconfigure
    --允許在進程中使用ACE.OLEDB.12
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    --允許動態參數
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    
    --導入臨時表 
    exec ('insert into jihua(id,[批次號],Right('''+ @filepath +''',charindex(''\'',REVERSE('''+ @filepath +'''))-1),getdate() FROM OPENDATASOURCE (''Microsoft.ACE.OLEDB.12.0'', ''Data Source='+@filepath+';User ID=Admin;Password='' )...計劃匯總表')

    --注意這里，要先關閉外圍的設置，然后再關閉高級選項
    exec sp_configure'Ad Hoc Distributed Queries',0
    reconfigure
    exec sp_configure'show advanced options',0
    reconfigure
    --關閉ACE.OLEDB.12的選項
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 0
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 0

CREATE TABLE CSVTable( 
Name NVARCHAR(MAX), 
Email NVARCHAR(MAX), 
Area NVARCHAR(MAX) 
) 
然后執行下面的語句：2022.3.8用此方法導入成功。90萬記錄1分鐘。

2022.8.31 ，50完12秒完成，推薦此方法。csv方法用逗號分隔不可靠，改用txt的 tab分隔符

BULK INSERT CSVTable
FROM 'D:\csv.txt'
WITH(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
SELECT * FROM CSVTable

Beginning with SQL Server 2017 (14.x), BULK INSERT supports the CSV format

 https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16

BULK INSERT Sales.Orders
FROM '\\SystemX\DiskZ\Sales\data\orders.csv'
WITH ( FORMAT = 'CSV');

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH (FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

BULK INSERT Sales.Invoices
FROM '\\share\invoices\inv-2016-07-25.csv'
WITH ( CODEPAGE = '65001'
      , FORMAT = 'CSV'
      , FIRSTROW = 2
      , FIELDQUOTE = '\'
      , FIELDTERMINATOR = ';'
      , ROWTERMINATOR = '0x0a');

BULK INSERT MyTable
FROM 'D:\data.csv'
WITH
( CODEPAGE = '65001'
   , DATAFILETYPE = 'char'
   , FIELDTERMINATOR = ','
);

TXT方式導入

Excel文件里的字段值有逗號 a,b,"c,d"

這個用CSV拆分成4列了，實際是3列。改用txt方式 ，打開Excel文件，另存txt文件代tab符號的。

BULK INSERT table1
FROM 'D:\data\data.txt'
WITH(
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n',

--DATAFILETYPE ='widechar'

--, KEEPNULLS
CODEPAGE =N'raw'
)
先建好表table1。避免了逗號分隔錯誤的困擾。速度快100萬2分鐘內。

openrowset查詢CSV文件

insert into table1

select   *
from openrowset (
    bulk 'D:\a1.csv'
    , formatfile='D:\fmt.xml'
    , firstrow=2
) as a
xml格式文件要自定義

 2024.2.13 第一行是字段名，跳過第一行

BULK INSERT CSVTable
FROM 'E:\aa.csv'
WITH ( FORMAT = 'CSV', FIRSTROW = 2);