Extract-2-Database.vbs

From iMacros

This script demonstrates how to extract data from a web site and store this information directly in a database (MS ACCESS) using the Microsoft Access Driver. It uses the macro Wsh-Extract-Jobs.iim.

Visual Basic Script:

 'Note: EXTRACT requires Internet Explorer 6.0 or better to be installed 
 
 Dim MyArray
 Dim objFileSystem, objOutputFile
 Dim strOutputFile
 Dim pos
 
 
 
 ' find out current folder
 myname = WScript.ScriptFullName
 mypath = Left(myname, InstrRev(myname, "\"))
 
 
 ' access database
 set db = CreateObject("ADODB.Connection")
 db.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
 & mypath & "IIM-TEST-EXTRACT.MDB")
 
 MsgBox "This script demonstrates how to extract data from a web site and store this information directly in a database 
(MS ACCESS). It uses the macro <wsh-extract-jobs.iim>." + vbCrLf + VbCrLf + "Tip: This script has the same function as 
<extract-2-file.vbs> but stores the data in a database instead of a text file."
 
 
 set iim1= CreateObject ("imacros")
 iret = iim1.iimInit("")
 
 
 For num = 1 To 3 
    str = cstr(num)  'Convert integer to string
    iret = iim1.iimDisplay("Listing No: " + str)
 
    pos = num '+ 4'start at 5: Offset for POS= statement
    str = cstr(pos)  'Convert integer to string
    iret = iim1.iimSet("-var1", str) 'Select a new link for each run
 
    iplay = iim1.iimPlay("wsh-extract-jobs")
    data = iim1.iimGetLastExtract()
    If iplay = 1 and len (data) > 0 Then
       MyArray = Split(data, "[EXTRACT]")
 
 ' use SQL to insert new data
 sql = "insert into tableJobListings (JobTitle, Salary, PositionType, RefCode) values ('" _
 & MyArray(0) & "', '" & MyArray(1) & "' ,  '" & MyArray(2) & "' ,  '" & MyArray(3) & "')"
 
 
 ' execute sql statement
 set rs = db.Execute(sql)
 
 
 End If
 
    If iplay = 1 Or iplay < 0 Then
       MsgBox "Error!"
    End If
 Next
 
 iret = iim1.iimExit
 
 MsgBox "The data was stored in the <IIM-TEST-EXTRACT.MDB> database. The script is now completed."
 
 WScript.Quit(0)

Macro code for Wsh-Extract-Jobs:

 VERSION BUILD=300705     
 TAB T=1     
 TAB CLOSEALLOTHERS  
 URL GOTO=http://www.iopus.com/imacros/demo/v5/extract1 
 'We use the CMDLINE statement to set var1=1
 'if var1 is NOT submitted via the scripting interface
 CMDLINE !VAR1 1    
 TAG POS={{!VAR1}} TYPE=A ATTR=HREF:http://www.iopus.com/imacros/demo/v5/extract1/*
 EXTRACT POS=1 TYPE=TXT ATTR=<P<SP>class=heading2>*      
 EXTRACT POS=1 TYPE=TXT ATTR=<NOBR>*salary*      
 EXTRACT POS=1 TYPE=TXT ATTR=<NOBR>*position*   
 EXTRACT POS=1 TYPE=TXT ATTR=<NOBR>*ref*