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

https://www.cnblogs.com/habin/archive/2012/08/01/2618845.html

https://www.cnblogs.com/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);