The renewal maintenance has officially ended for Progress iMacros effective November 30, 2023.
This Wiki site will also no longer be moderated from the Progress side.
Thank you again for your business and support.
Sincerely, The Progress Team

Extract-2-Database.vbs

From iMacros
Jump to navigation Jump to search

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. Note: This examples uses a MS ACCESS database, but the same method works with every scripting/programming language and with every database. To use a different database you only need to edit the connection string.

Important: The ODBC driver which connects to an Access database, is only available in 32-bit. If your OS is 64-bit, you have to force the script to run in x86 mode. Please see the x64 page for an explanation on how to achieve this.


Visual Basic Script:

  ' iMacros Extract-2-Database Script
' (c) 2008-2015 iOpus/Ipswitch Inc. 

Option Explicit

Dim objFileSystem, objOutputFile
Dim strOutputFile


' find current folder
Dim myname, mypath
myname = WScript.ScriptFullName
mypath = Left(myname, InstrRev(myname, "\"))

'Note for x64 users: You must start the VBS script in 32bit mode (C:\Windows\SysWOW64\wscript.exe) 
'for the Microsoft ODBC Driver to work
'Please see the note about VBS scripts on http://wiki.imacros.net/x64 for more details 

' access database
Dim db
set db = CreateObject("ADODB.Connection")
db.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" _
& mypath & "IIM-TEST-EXTRACT.MDB")

Dim message
message = "This script demonstrates how to extract data from a web site and store this information directly in a database (MS ACCESS)."
message = message + " It uses the macro <wsh-extract-jobs.iim>." + vbCrLf + VbCrLf 
message = message + "Tip: This script has the same function as <extract-2-file.vbs> but stores the data in a database instead of a text file."

MsgBox(message)

Dim iim1, iret, iplay
set iim1= CreateObject ("imacros")
iret = iim1.iimOpen("-ng")

if iret < 0 then
      MsgBox ("Error: " + CStr(iret))
      WScript.Quit(iret)
end if
      

Dim num, pos, str, rs, sql
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("myvar", str) 'Select a new link for each run

   iplay = iim1.iimPlay(mypath & "Macros\wsh-extract-jobs.iim")
   If iplay = 1  Then
	' use SQL to insert new data
	sql = "insert into tableJobListings (Salary, PositionType, RefCode) values ('" _
	& iim1.iimGetExtract(1) & "', '" & iim1.iimGetExtract(2) & "' ,  '" & iim1.iimGetExtract(3) & "')"

	' execute sql statement
	set rs = db.Execute(sql)
   End If

   If  iplay < 0 Then
      MsgBox "Error: " + iim1.iimGetErrorText()
   End If
Next

iret = iim1.iimClose

MsgBox "The data was stored in the <IIM-TEST-EXTRACT.MDB> database. The script is now completed."

WScript.Quit(iret)

Macro code for Wsh-Extract-Jobs:

TAB T=1     
TAB CLOSEALLOTHERS  
URL GOTO=http://demo.imacros.net/Automate/ExtractDemo2
TAG POS={{myvar}} TYPE=A ATTR=HREF:http://demo.imacros.net/Automate/listing*
TAG POS=1 TYPE=H3 ATTR=TXT:* EXTRACT=TXT

TAG POS=1 TYPE=LI ATTR=TXT:* EXTRACT=TXT
TAG POS=2 TYPE=LI ATTR=TXT:* EXTRACT=TXT
TAG POS=3 TYPE=LI ATTR=TXT:* EXTRACT=TXT