Data Extraction from local HTML

From iMacros

Jump to: navigation, search

This project extracts data from a locally saved page. The page is formatted HTML and the script calls a macro that extracts each row of data. This information is then stored in an MS-Access database.

Visual Basic Script 

 Option Explicit
 Dim iim1, iret, i, db, iretsql
 Dim myname, mypath, sql, ExtractedString
 Dim ExtractedArray
 
 ' 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 & "Mikneus2.MDB")
 
 MsgBox "This script extracts information from a website and stores this information in a database (MS ACCESS)."
 
 'Initialize iMacros and log in to the website
 set iim1= CreateObject ("InternetMacros.iim")
 iret = iim1.iimInit("")
 If iret<0 Then 
   MsgBox  "Error initializing iMacros: "+ cstr (iret) +iim1.iimGetLastError()
   EndAll()
 End If
 
 'Loop through the entries on the webpage
 i=1
 Do
   iret = iim1.iimSet("-var_line", CStr(i))
   'Play macro to extract each line
   iret = iim1.iimPlay("mikneusextract")
   If iret>0 Then 
     
     ExtractedString = iim1.iimGetLastExtract()
     ExtractedString = replace(ExtractedString, "
", " ")
     ExtractedArray = split(ExtractedString, "[EXTRACT]")
 
     'write to database using SQL
     sql = "insert into Output (Dattim, Phone1, CAMPAIGN, RINGTO, ORIGINATION, NAME, LENGTH, ACTIONS) values ('" & 
ExtractedArray(0) & "', '" & ExtractedArray(1) & "', '" & ExtractedArray(2) & "', '" & ExtractedArray(3) & "', '" & 
ExtractedArray(4) & "', '" & ExtractedArray(5) & "', '" & ExtractedArray(6) & "', '" & ExtractedArray(7) & "')"
 
 
     set iretsql = db.Execute(sql)
   
   End If
   
   i=i+1
 Loop Until iret<0
   
 MsgBox "The script is now completed."
 EndAll()
 
 Function EndAll
   'Exit database
   db.Close
   'Exit iMacros and end script
   iret = iim1.iimExit(1)
   if iret < 0 Then
     MsgBox  "Error exiting iMacros: "+ cstr (iret) +iim1.iimGetLastError()
   End If
   WScript.Quit(0)
 End Function

Macro code for mikneusextract 

 VERSION BUILD=5200814     
 TAB T=1     
 TAB CLOSEALLOTHERS     
 URL GOTO=file:///C:/Documents%20and%20Settings/Daniel's%20iOpus%20Work/My%20Documents/Projects/GOJ-953293/Mikneus.htm     
 SIZE X=801 Y=602    
 ONERRORDIALOG BUTTON=0 CONTINUE=YES    
 TAG POS={{line}} TYPE=INPUT:CHECKBOX FORM=NAME:msgs ATTR=NAME:chkFile&&VALUE:* CONTENT=NO 
 EXTRACT POS=R1 TYPE=HTM ATTR=<TD<SP>class=bdrLeft<SP>width=63<SP>height=39>*   
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft<SP>width=112>*   
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft<SP>width=79>*   
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft<SP>width=106>* 
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft<SP>width=104>*   
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft>*   
 EXTRACT POS=R1 TYPE=TXT ATTR=<TD<SP>class=bdrLeft<SP>width=59>* 
 EXTRACT POS=R1 TYPE=HREF ATTR=<A<SP>class=lnkStyle12<SP>href="https://ivr.server.com/*
Retrieved from "http://wiki.imacros.net/Data_Extraction_from_local_HTML"
Content