VBS imports local Excel data into SQL Server

Source: Internet
Author: User
Tags prepare

VBS imports local Excel data into SQL Server

Recently there was a test that needed to import the local Excel data into SQL Server, so I wrote a script for the reference of the students in need. Because the test is demonstrated here, the data prepared is relatively simple.

We're going to insert a column of local Excel into the username column in the database

Prepare the local Excel data first

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/95/A2/wKioL1kX4vegH_gOAADdPRASlNY395.png "height=" 283 "/>

Then prepare the database and table structure

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/95/A2/wKiom1kX4vmzryc3AAHjHJOmrc0376.png "height="/>

Start on script

Dim db,objrecordsetdim excel ' sql server server address dim sqlserver ' sql server  database Dim  database ' sql server login name dim uid ' sql server login password dim pwd ' sql server  database table name Dim  tablename ' Sql server database table column field dim column ' Excel table Path dim xlspathsqlserver =  ' 192.168.7.170 "database = " Passwordinfo "uid = " sa "pwd = " Password "tablename  =  "Userserverinfo" column =  "Username" xlspath =  "d:\users.xlsx" set db =  createobject ("ADODB. Connection ") Set objrecordset = createobject (" ADODB. RECORDSET ") set excel = createobject (" Excel.Application ") DB. open  "driver=sql server;server=" &SQLserver& ";D atabase=" &Database& "uid=" &uid & ";p wd=" &pwd& ";" Excel.displayalerts=falseexcel.visible=falseexcel.workbooks.open (Xlspath) line = 1value =  Excel.ActiveSheet.Cells (line,1). Valuedo while value <>  "" Line = line + 1insertdb (value) value =  Excel.ActiveSheet.Cells (line,1). valueloopmsgbox  "Finish" Function insertdb (value) sql =  "insert into " & tablename & "(" & Column & ")  VALUES  (" & "'" &value& "'" & ")" Msgbox sqldb.execute (SQL) end function

We start the execution and start inserting the first piece of data

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/95/A2/wKiom1kX4vrBtKrUAAExujdaBNM408.png "height=" 245 "/>

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/95/A2/wKioL1kX4vvxlyIPAAD-mJwLAls443.png "height="/>

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M00/95/A2/wKiom1kX4vzTVwXDAAFuDPOXLoo457.png "height=" 283 "/>

Execution complete

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/95/A2/wKiom1kX4v-C5iM5AAJDDKh_93Q221.png "height=" 323 "/>

We view database tables

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/95/A2/wKiom1kX4wTSf3bZAAFBj1OjlJs518.png "height=" 404 "/>

Because the VBS script has connected to the database account and password, more sensitive, so we need to be safe, we have to convert VBS to EXE, but in this case, the file path is written dead, inconvenient, so we need to change the source file path to browse box.

Before replacement: ' Xlspath = ' d:\users.xlsx ' After Replacement: MsgBox "Please select source file" Xlspath = CreateObject ("Wscript.Shell"). Exec ("Mshta VBScript:" "<input type=file Id=f><script>f.click (); new ActiveXObject (' Scripting.FileSystemObject '). Getstandardstream (1). Write (F.value) [Close ()];</script> "" "). Stdout.readall

The whole code after the change

Dim db,objrecordsetdim excel ' sql server server address dim sqlserver ' sql server  database Dim  database ' sql server login name dim uid ' sql server login password dim pwd ' sql server  database table name Dim  tablename ' Sql server database table column field dim column ' Excel table Path dim xlspath sqlserver =  ' 192.168.7.170 "database = " Passwordinfo "uid = " sa " pwd = " Password "tablename  =  "Userserverinfo"  column =  "Username" ' xlspath =  "D:\users.xlsx" MsgBox   "Please select Source file" Xlspath = createobject ("Wscript.Shell"). Exec ("Mshta vbscript:" "<input type=file id=f><script>f.click ();new  ActiveXObject (' Scripting.FileSystemObject '). Getstandardstream (1). Write (F.value) [Close ()];</script> "" "). Stdout.readallset db = createobject ("ADODB. Connection ") Set objrecordset = createobject (" ADODB. RECORDSET ") Set excel = createobject (" Excel.Application") DB. open  "driver=sql server;server=" &SQLserver& ";D atabase=" &Database& "uid=" &uid & ";p wd=" &pwd& ";"    Excel.DisplayAlerts=FALSE    Excel.visible=FALSE     Excel.workbooks.open (Xlspath) line = 1value = excel.activesheet.cells (line,1). value   do while value <>  ""      line =  line + 1            insertdb (value )         value = excel.activesheet.cells (line,1). value          loopmsgbox  "Finish" Function  Insertdb (value)   sql =  "insert into " &Tablename & "(" &  column & ")  VALUES  (" & "" &value& "" "&") "          ‘ Msgbox sql          db. Execute (SQL)      end function

Execution Result:

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard" src= "Http://s3.51cto.com/wyfs02/M01/95/A2/wKioL1kX4wjT42ksAANRTbvcjbo510.png" height= "359"/>

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt = "Clipboard[1]" src= "http://s3.51cto.com/wyfs02/M01/95/A2/wKiom1kX4w_heA6yAAG1agW4aiY983.png" height= "325"/>

We will prompt you to insert the MsgBox comment for the data and then perform the attempt

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M01/95/A2/wKioL1kX4xCA_IsmAACP5LPOVGk641.png "height=" 188 "/>

After execution, you will be prompted to complete

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/95/A2/wKiom1kX4xLTv75rAAJ7NSBajvY118.png "height=" 325 "/>

We look at the database results again

650) this.width=650; "title=" image "style=" border-top:0px;border-right:0px;background-image:none;border-bottom:0 px;padding-top:0px;padding-left:0px;border-left:0px;margin:0px;padding-right:0px; "border=" 0 "alt=" image "src=" Http://s3.51cto.com/wyfs02/M02/95/A2/wKioL1kX4xTTFWtqAAFYWWeRRZQ217.png "height=" 460 "/>

This article from "Gao Wenrong" blog, declined reprint!

VBS imports local Excel data into SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.