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