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 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.
Comments are closed.
|
2016-17 WorkExamples of my new work in 2016 and 2017 Archives
January 2018
Categories |