opendatasource mysql_通过异类查询(OpenDataSource)把外部数据(Excel,TXT)导入到SqlSe

最新推荐文章于 2021-03-17 05:06:17 发布

少年商学院

最新推荐文章于 2021-03-17 05:06:17 发布

阅读量728

点赞数

文章标签： opendatasource mysql

本文链接：https://blog.csdn.net/weixin_32427105/article/details/113470079

版权

本文介绍了如何在SQLServer中利用OpenDataSource和OLEDB驱动引擎将Excel和TXT格式的数据导入到数据库。通过创建存储过程sp_ReadXLSSource和sp_ReadSource，详细展示了具体的SQL语句和配置步骤，包括环境准备、文件格式要求和执行检查。

摘要由CSDN通过智能技术生成

无论用sqlcmd或者通过C#逻辑处理，或者还是直接在SqlServer里执行操作，个人总结大概不过可归总为这两种方式： 1，SSIS； 2，OLEDB驱动引擎。 SSIS实在也好用，现在这里先不去讨论。其中大多数人用的都是第二种方法，下面，本人也以第二种方法在SqlServer里导

无论用sqlcmd或者通过C#逻辑处理，或者还是直接在SqlServer里执行操作，个人总结大概不过可归总为这两种方式：

1，SSIS；

2，OLEDB驱动引擎。

SSIS实在也好用，现在这里先不去讨论。其中大多数人用的都是第二种方法，下面，本人也以第二种方法在SqlServer里导入excel和txt格式文件的具体实例来说明怎样在项目里灵活通过OLEDB导入数据！

一，必要的环境说明

本实例用sql语句在SqlServer里面完成对数据的导入操作。

在本实例开始之前,可能需要先安装AccessDatabase引擎包

(本人机器64位，安装的是32位的office组件，所以需要另外安装office驱动引擎包(此引擎包作用在于使得office系统文件与office应用程序之间进行数据传输) - 此步骤中需要先卸载32位office组件，否则会提示驱动安装不成功)

本人所用引擎包下载地址如下：

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

安装完成后,具体配置可以这里配,建议如非必要,默认就好。

二，代码准备 - 建数据库及存储过程

建数据库这里不多说，建立与导入数据相匹配的数据库而已。

而为了项目拓展及方便使用管理,需要创建存储过程,目的是为了批量导入外部数据。

因为本例以excel和txt分别来做实例说明，所以，需要新建两个存储过程 - "sp_ReadXLSSource"和"sp_ReadSource"。代码分别如下：

存储过程：sp_ReadXLSSource

USE [CUSTOMS]

/****** Object: StoredProcedure [dbo].[sp_ReadXLSSource] Script Date: 11/21/2014 3:01:39 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[sp_ReadXLSSource]

@trgPath varchar(255),

@trgTable varchar(255),

@xlsFile varchar(255),

@xlsSheet varchar(255),

@xlsFields varchar(8000)

declare @mySql varchar(8000)

declare @myIntermediate varchar(8000)

declare @Firstfield varchar(100)

declare @FirstfieldValue varchar(100)

--Lookup lastfield

set @Firstfield = dbo.fnGetFirstField(@xlsFields)

set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))

--PRINT @FirstfieldValue

--Drop table if exists

set @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'

set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTable

exec(@mySql)

--PRINT @mySql

--Load Table

set @mySql = 'SELECT ' + @xlsFields + ' INTO ' + @trgTable + ' FROM OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',

''Excel 12.0;DATABASE=' + @trgPath + '\' + @xlsFile + ';IMEX=1'', ''Select * from ' + @xlsSheet + ''')'

+ 'WHERE NOT ' + @Firstfield + ' IS NULL'

--PRINT @mySql

exec(@mySql)存储过程：sp_ReadSourceUSE [WOOX_CQM]

/****** Object: StoredProcedure [dbo].[sp_ReadSource] Script Date: 11/21/2014 2:54:33 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[sp_ReadSource]

@trgPath varchar(255),

@trgTable varchar(255),

@trgFields varchar(8000)

declare @mySql varchar(8000)

declare @myIntermediate varchar(8000)

declare @Firstfield varchar(100)

declare @FirstfieldValue varchar(100)

--Lookup lastfield

set @Firstfield = dbo.fnGetFirstField(@trgFields)

set @FirstfieldValue = RTRIM(LTRIM(SUBSTRING(@Firstfield,2,CHARINDEX(']',@Firstfield,0)-2)))

--PRINT @FirstfieldValue

--Drop table if exists

set @myIntermediate = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @trgTable + ']'') AND type in (N''U''))'

set @mySql = @myIntermediate + ' DROP TABLE ' + @trgTable

exec(@mySql)

--PRINT @mySql

--Load Table

set @mySql = 'SELECT ' + @trgFields + ' INTO ' + @trgTable + ' ' +

'FROM OpenDataSource (''Microsoft.ACE.OLEDB.12.0'',

''Data Source="' + @trgPath + '";Extended properties=Text'')...'

+ @trgTable + '#txt '

+ 'WHERE NOT ' + @Firstfield + ' IS NULL AND ' + @Firstfield + ' <> ' + '''' + @FirstfieldValue + ''''

--PRINT @mySql

exec(@mySql)

三，文件准备,导入源及Schema.ini配置文件

对于excel文件来说,相对txt格式，实在简单很多，关键不过以下这段代码

TRUNCATE TABLE WOOX_INFORMATICA..VBPA;

INSERT INTO WOOX_INFORMATICA..VBPA select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\InitialData\db_data.xlsx;HDR=YES', 'SELECT * FROM [VBPA$]')

--(适用于office2007及以上版本，2003用Jet - "Microsoft.Jet.OLEDB.4.0")其它需要注意的不过就是保证excel内的数据格式是统一的文本格式，这里只简单给出事例图，不做详细说明。

对于txt格式文件，本人所用格式如下。

vcr9vt21xMXk1sPOxLz+U2NoZW1hLmluaaOhyOe5+7W8yOuyu7PJuaa78tXfs/a07aOstPO2vMrH1eLSu7K91ui07cHLPGJyPgqjqHBzo7q0y8Xk1sPOxLz+0qq6zbW8yOvUtLfF1NrNrNK7uPbOxLz+vNDPwqOswe3N4qOszsS8/rzQxL/CvNfuusOx8Myrs6S78tXfsPy6rNPQv9UmIzI2Njg0O9auwOC1xKOs1eLQqdfUvLrXotLio6GjqTxicj4KxuTW0FNjaGVtYS5pbmnA77nmt7YmIzI2Njg0O8q9yOfPwqGjPHByZSBjbGFzcz0="brush:sql;">[SAP_TVSTZ.txt]