EXCEL表格导入到SQL数据库

经常有大量数据导入到数据库的需求，提供一个简单的用SQL语句导入的办法。

 

核心语句：SELECT *

FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'DataSource="c:\Finance\account.xls";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...xactions

 

Data Source就是excel文件的路径，这个简单；UserId、Password和Extendedproperties这三个属性为“User ID=Admin;Password=;Extended properties=Excel 5.0”。最后那个“xactions”就是excel文件里所选的工作表名，注意默认的Sheet1要写成[Sheet1$]。

 

例如：

数据库里建好一个表testTable_1，有5个字段id, name, date, money, content，C盘下book1.xls文件的sheet1工作表里写好对应的数据并设好数据类型，执行如下插入语句即可：

 

insert into testTable_1 ([name], [date],[money], [content])

Select [姓名], [日期], [金额], [内容]

FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',

'Data Source="C:\Book1.xls";

User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]    

 

另：由于，SQL Server 2005为了安全性，安装完以后，将很多组件的默认状态设置为不启用。如果需要用到这些特性，可以由管理员手工启用。为此，SQL Server 2005还提供了一个专门的管理工具：SQL Server Surface Area Configuration

要解决本文的问题，也就是要启用AD Hoc Distributed Queries特性，执行Surface AreaConfiguration，选择Surface Area Configuration for features

在弹出的窗口中选择Database Engine->Ad Hoc Remote Queries，然后勾选"EnableOPENROWSET and OPENDATASOURCE support"，确认即可。

 

如果导入的EXCEL里和表里已经有的规定唯一的键有重复的内容时候，可以分别执行下面的语句保留原有的内容。

 

1.      首先建立一个和目标表（User）完全一样的临时表（temp）

select * into temp  from [User] where 1=2

 

2.      用上述方法将数据先导入到临时表里

INSERT INTO temp

                      (UserName, AccountID)

SELECT    a, b

FROM        OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',

                      'DataSource="C:\import.xls";

User ID=Admin;Password=;Extendedproperties=Excel 5.0' )...[Sheet1$] AS derivedtbl_1

 

3.      使用SQL语句使得找出不重复的表插入到目标表里

INSERT INTO User

SELECT    *

FROM    temp

WHERE    (NOT EXISTS

                          (SELECT     AccountID

                            FROM  User                           

WHERE      (AccountID = temp.AccountID)))