VBS imports local Excel data into SQL Server

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