set Conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")
Const REMOTE_FILE_URL="https://www.sandiego.gov/sites/default/files/tr_recent.csv"
Call ShowRemoteFile
Sub ShowRemoteFile
Dim objXML, strContents, arrLines
Dim x
Set objXML=Server.CreateObject("Microsoft.XMLHTTP")
'read text file...
objXML.Open "GET", REMOTE_FILE_URL, False
objXML.Send
strContents=objXML.ResponseText
Set objXML=Nothing
'split into lines and read line by line...
arrLines=Split(strContents, VBCrLf)
For x=0 To UBound(arrLines)
call writetoDB(arrLines(x))
'response.write arrLines(x) & "<br><br>" 'Read out results for testing
Next
End Sub
sub writetoDB(strLine)
strLine = replace(strLine,"""","")
strLine = replace(strLine,"'","")
if len(strLine) < 15 then
response.write "Finished Import"
exit sub
end if
arrLeads = Split(strLine,",")
'Read out the results for testing
'For x=0 To UBound(arrLeads)
' 'call writetoDB(arrLines(x))
' response.write x & ":" & arrLeads(x) & "<br>"
'Next
'exit sub
BUSINESSACCT = arrLeads("0")
DBANAME = left(arrLeads("1"),250)
OWNERSHIPTYPE = left(arrLeads("2"),250)
ADDRESS = left(arrLeads("3"),250)
CITY = left(arrLeads("4"),250)
ZIP = left(arrLeads("5"),250)
STATE = left(arrLeads("6"),250)
BUSINESSPHONE = left(arrLeads("7"),250)
OWNERNAME = left(arrLeads("8"),250)
CREATIONDT = left(arrLeads("9"),250)
STARTDT = left(arrLeads("10"),250)
EXPDT = left(arrLeads("11"),250)
NAICS = left(arrLeads("12"),250)
ACTIVITYDESC = left(arrLeads("13"),250)
'Skips the first line of the spreadsheet
if BUSINESSACCT = "BUSINESS ACCT#" then exit sub
strSQL = " IF NOT EXISTS" _
& " (" _
& " SELECT top 1 ID FROM newbusinessleads_sandiego WHERE BUSINESSACCT=" & cdbl(BUSINESSACCT) _
& " )" _
& " BEGIN" _
& " INSERT INTO newbusinessleads_sandiego" _
& (BUSINESSACCT, DBANAME ,OWNERSHIPTYPE, ADDRESS,CITY, ZIP,STATE, BUSINESSPHONE, _
& "OWNERNAME, CREATIONDT, STARTDT,EXPDT, NAICS ,ACTIVITYDESC)" _
& " VALUES" _
& " (" _
& cdbl(BUSINESSACCT) & "," _
& "'" & DBANAME & "'," _
& "'" & OWNERSHIPTYPE & "'," _
& "'" & ADDRESS & "'," _
& "'" & CITY & "'," _
& "'" & ZIP & "'," _
& "'" & STATE & "'," _
& "'" & BUSINESSPHONE & "'," _
& "'" & OWNERNAME & "'," _
& "'" & CREATIONDT & "'," _
& "'" & STARTDT & "'," _
& "'" & EXPDT & "'," _
& "'" & NAICS & "'," _
& "'" & ACTIVITYDESC & "'" _
& " )" _
& " END"
if isnumeric(BUSINESSACCT) then rs.Open strSQL, CONNECTIONSTRING, 0, 1
end sub
set rs = Server.CreateObject("ADODB.Recordset")
Const REMOTE_FILE_URL="https://www.sandiego.gov/sites/default/files/tr_recent.csv"
Call ShowRemoteFile
Sub ShowRemoteFile
Dim objXML, strContents, arrLines
Dim x
Set objXML=Server.CreateObject("Microsoft.XMLHTTP")
'read text file...
objXML.Open "GET", REMOTE_FILE_URL, False
objXML.Send
strContents=objXML.ResponseText
Set objXML=Nothing
'split into lines and read line by line...
arrLines=Split(strContents, VBCrLf)
For x=0 To UBound(arrLines)
call writetoDB(arrLines(x))
'response.write arrLines(x) & "<br><br>" 'Read out results for testing
Next
End Sub
sub writetoDB(strLine)
strLine = replace(strLine,"""","")
strLine = replace(strLine,"'","")
if len(strLine) < 15 then
response.write "Finished Import"
exit sub
end if
arrLeads = Split(strLine,",")
'Read out the results for testing
'For x=0 To UBound(arrLeads)
' 'call writetoDB(arrLines(x))
' response.write x & ":" & arrLeads(x) & "<br>"
'Next
'exit sub
BUSINESSACCT = arrLeads("0")
DBANAME = left(arrLeads("1"),250)
OWNERSHIPTYPE = left(arrLeads("2"),250)
ADDRESS = left(arrLeads("3"),250)
CITY = left(arrLeads("4"),250)
ZIP = left(arrLeads("5"),250)
STATE = left(arrLeads("6"),250)
BUSINESSPHONE = left(arrLeads("7"),250)
OWNERNAME = left(arrLeads("8"),250)
CREATIONDT = left(arrLeads("9"),250)
STARTDT = left(arrLeads("10"),250)
EXPDT = left(arrLeads("11"),250)
NAICS = left(arrLeads("12"),250)
ACTIVITYDESC = left(arrLeads("13"),250)
'Skips the first line of the spreadsheet
if BUSINESSACCT = "BUSINESS ACCT#" then exit sub
strSQL = " IF NOT EXISTS" _
& " (" _
& " SELECT top 1 ID FROM newbusinessleads_sandiego WHERE BUSINESSACCT=" & cdbl(BUSINESSACCT) _
& " )" _
& " BEGIN" _
& " INSERT INTO newbusinessleads_sandiego" _
& (BUSINESSACCT, DBANAME ,OWNERSHIPTYPE, ADDRESS,CITY, ZIP,STATE, BUSINESSPHONE, _
& "OWNERNAME, CREATIONDT, STARTDT,EXPDT, NAICS ,ACTIVITYDESC)" _
& " VALUES" _
& " (" _
& cdbl(BUSINESSACCT) & "," _
& "'" & DBANAME & "'," _
& "'" & OWNERSHIPTYPE & "'," _
& "'" & ADDRESS & "'," _
& "'" & CITY & "'," _
& "'" & ZIP & "'," _
& "'" & STATE & "'," _
& "'" & BUSINESSPHONE & "'," _
& "'" & OWNERNAME & "'," _
& "'" & CREATIONDT & "'," _
& "'" & STARTDT & "'," _
& "'" & EXPDT & "'," _
& "'" & NAICS & "'," _
& "'" & ACTIVITYDESC & "'" _
& " )" _
& " END"
if isnumeric(BUSINESSACCT) then rs.Open strSQL, CONNECTIONSTRING, 0, 1
end sub
The end result is that each time the script runs it parses the remote CSV file from the county, it double checks the "businessaccount" field to make sure that even when it runs against the same data it won't make any duplicate entries into the SQL database... the data is imported and made available to the insurance salespersons when there is a lull in organic and adwords leads. Now that the data is in the database the salesperson can pull this data into the lead management system without typing.