VB实现Excel数据导入SQL+Server方法解析pdf

DATABASEANDINFORMATIONMANAGEMENT数据库与信息管理VB实现Excel数据导入SQLServer方法解析李爱华（中国石油长庆油田分公司超低渗透油藏研究中心油藏地质室，西安710021）摘要：基于VisualBasic编程语言，举例说明了Excel数据导入MicrosoftSQLServer数据库常用的3种方法。关键词：VB；Excel；数据导入；SQLServerTheResolutionofExcelDataImportSQLServer’sUsingVBLIAihua（PetrochinaEhangqingOilfieldCompanyUltra-lowPermeabilityReservoirResearchcentreReservoirGeologyOffice,Xi'an710021）Abstract：ThisarticleisbasedonVBprogramminglanguage,whichillustratesthreewaysofthatExceldataimportMi－crosoftSQLServerdatabase.Keywords：VB;Excel;Dataimport;SQLServer随着计算机及互联网技术的普及，数据网络存取、分散应用已成为目前国内各大中型企业数据库应用的主要途径，数据库系统在办公信息化的应用也越来越广泛，如何将已有的Excel数据自动转入数据库系统已经成为数据转换及应用的焦点，下面就介绍几种使用VB编写数据库程序的方法，以实现本地Excel数据快速批量导入MicrosoftSQLServer网络数据库。1前期准备1.1数据准备为了使示例能够正常运行，需要在本机上安装MicrosoftSQLServer2000数据库、VisualBasic6.0及MicrosoftExcel（版本在2003以上），并准备一个Excel数据文件（必须是数据格式，这里以油田某日的生产数据个别字段为例），格式如表1所示，数据记录数(行)和字段数（列）可根据需要增加。表1数据表井号日期投产日期生产时间采油方式日产液日产油日产水桐24-212010-10-252008-8-1524抽油机4.690.294.4桐24-222010-10-252008-8-1524抽油机2.670.472.2桐24-232010-10-252008-8-1524抽油机2.280.042.241.2界面设计打开VisualBasic6.0应用程序，新建一个工程，在form1中添加3个按钮，将其caption属性分别设为“Excel数据导入(OpenRowSet)”、“Excel数据导入(OpenDataSource)”和“Ex－cel数据导入(OpenSchema)”，以实现通过按按钮来运行VB代码。为了使示例能够自动选取存在本机上的Excel文件，本例应用了commandialog控件，因此这里需要首先添加相应的部件(详情如图1)，部件添加后，在工具栏里就会出现一个工具，将它添加到form1窗口的任意位置即可，做好的界面如图2所示。双击“Excel数据导入(OpenRowSet)”按钮，在代码编辑器中输入以下代码：图1添加控件Excel数据导入SQL方法Excel数据导入（OpenRowSet）Excel数据导入（OpenDataSource）Excel数据导入（OpenSchema）图2示例界面作者简介：李爱华（1975-），女，工程师，研究方向：数据2代码编写库建设及软件开发应用。2.1OpenRowSet方法收稿日期：2010-11-172011009.0241电脑编程技巧与维护PrivateSubcommand1_Click()DimstrconnAsString'定义连接字符串'初始化commandialog1的属性CommonDialog1.Filter=电子表格文件(.xls)|*.xlsCommonDialog1.DialogTitle=请选择要导入的文件'初始化记录集及连接Setrstemp=CreateObject(ADODB.Recordset)Setconn=CreateObject(adodb.connection)'打开连接，并加参数Strconn=Provider=SQLOLEDB.1;PersistSecurityInfo=False;UserID=sa;password=qaz;InitialCatalog=scsjk;DataSource=(local)conn.Openstrconn'选取Excel数据文件，文件路径及名称记录在Com'monDialog1.FileName中CommonDialog1.ShowOpen'写SQL语句，数据写入SQLServer中strsql=select*intotablenamefromOpenRowSet('microsoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database=&CommonDialog1.FileName&;','select*from[sheet1$]')where井号'合计'rstemp.Openstrsql,conn,1,1'提示MsgBox数据导入成功！,vbExclamation+vbOKOnly'关闭数据库连接及记录集，释放资源conn.CloseSetconn=Nothingrstemp.CloseSetrstemp=NothingEndSub2.1.1数据库连接及提取记录集字符串Strconn=Provider=SQLOLEDB.1;PersistSecurityInfo=False;UserID=sa;password=qaz;InitialCatalog=scsjk;DataSource=(lo－cal)，其中User=sa;password=qaz,initialCatalog=scsjk;DataSource=(local)为需要修改部分，分别为SQLServer数据库的用户名、密码、数据库名称、数据库所在机器的机器名或IP地址。从Excel文件中取得记录集的语句是OpenRowSet('mi－crosoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database=&Com－monDialog1.FileName&;','select*from[sheet1$]')，其中database=数据的路径和文件，[sheet1$]为工作表的名称，读者可根据实际情况进行修改，或者设置变量来存取这些内容，使程序运行不受数据文件自身因素的影响。2.1.2写入数据库SQL语句strsql=select*intotablenamefromOpenRowSet('mi－crosoft.jet.oledb.4.0','Excel8.0;HDR=Yes;database=&Com－monDialog1.FileName&;','select*from[sheet1$]')where井号'合计'这里用到了最简单的从一个表创建另一个表的方式，即“select*into新表名from记录集where条件”；如果数据库有相同结构的表，可向已有表中追加数据，使用“insertinto已有的表名select*from记录集where条件”，另外也可以根据需要写出更加复杂的sql语句，这里不再详述。2.2OpenDataSource方法2011.0242双击“Excel数据导入(OpenDataSource)”按钮，打开代码编辑器，将command1_click中的代码拷贝到PrivateSubcommand2_Click()事件代码中，并将strsql=……语句修改为以下代码，strsql=select*fromOpenDataSource('Microsoft.Jet.OLEDB.4.0','DataSource=&CommonDialog1.FileName&;Extendedproperties=Excel8.0')...[Sheet1$]where井号'合计'此种方法与上一种的不同之处在于这里使用了OpenData－Source的方法来取得记录集，该方法的语法详细说明如下：OPENDATASOURCE(provider_name,init_string)provider_name：注册为用于访问数据源的OLEDB提供程序的PROGID的名称。provider_name的数据类型为字符型，没有默认值。init_string：连接字符串，这些字符串将要传递给目标提供程序的IDataInitialize接口。提供程序字符串语法是以关键字值对为基础的，这些关键字值对由分号隔开。在本例中init_string是DataSource和Extendedproperties的组合，设置了数据源文件的路径和提供程序特定的连接字符串。2.3OpenSchema方法上述两种方法一般用于Excel文件与SQLServer安装在同一台计算机上的情况，下面介绍一种本地Excel导入异地SQL的方法。双击“Excel数据导入(OpenSchema)”按钮，在代码编辑器中输入以下代码。PrivateSubCommand3_Click()DimstrconnAsString'定义Excel连接字符串DimcnAsADODB.Connection'定义Excel连接Setcn=NewADODB.Connection'初始化commandialog1的属性，选取Excel文件，文'件名保存在CommanDialog1.filename中备用CommonDialog1.Filter=电子表格文件(.xls)|*.xlsCommonDialog1.DialogTitle=请选择要导入的文件CommonDialog1.ShowOpen'设置连接SQL数据库的连接字符串strtemp=[odbc;Driver={SQLServer};Server=10.78.173.77;Database=scsjk;UID=test;PWD=test]'设置Excel数据连接strconn=Provider=Microsoft.Jet.OLEDB.4.0;DataSource=&CommonDialog1.FileName&;ExtendedProp－erties=Excel8.0cn.OpenstrconnSetrstschema=cn.OpenSchema(adSchemaTables)'设置Excel记录集'循环将Excel数据文件中的各个数据表写入数据库DoUntilrstschema.EOFstrsql=insertinto&strtemp&.rukcselect*from[&rstschema!TABLE_NAME&]cn.Executestrsql,lngRecsAff,adExecuteNoRecordsMsgBox成功导入--&CommonDialog1.File－Name&--文件中--&rstschema!TABLE_NAME&---的数据到SQL数据库中！,vbExclamation+vbOKOnlyrstschema.MoveNextDATABASEANDINFORMATIONMANAGEMENT数据库与信息管理Loop'释放资源rstschema.Closecn.CloseSetcn=NothingSetrstschema=NothingEndSub这种方法的优势就在于它可以自动读取Excel工作簿中各工作表名称，循环将本地Excel数据批量导入到远程的SQLServer数据库。2.3.1利用ODBC连接远程数据库strtemp=[odbc;Driver={SQLServer};Server=10.78.173.77;Database=scsjk;UID=test;PWD=test]2.3.2多工作表数据循环入库Setrstschema=cn.Op