GEORGE SCOTT 619-792-7153
  • HOME
  • Adwords Q&A
  • Work Diary
    • 2020-21 Work
    • 2018-19 Work
    • 2016-17 Work
    • 2013-14 Work
  • WEBSITES
  • HUMOR

Importing Leads from Remote Spreadsheet

12/23/2016

 
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.  
Picture

    2016-17 Work

    Examples of my new work in 2016 and 2017

    Archives

    January 2018
    December 2017
    November 2017
    October 2017
    July 2017
    June 2017
    May 2017
    April 2017
    March 2017
    February 2017
    January 2017
    December 2016
    November 2016

    Categories

    All

    RSS Feed

San Diego Easy Traffic School | San Diego Small Business Insurance | Traffic School Online | Finish Traffic School Today | Lucky Traffic School
  • HOME
  • Adwords Q&A
  • Work Diary
    • 2020-21 Work
    • 2018-19 Work
    • 2016-17 Work
    • 2013-14 Work
  • WEBSITES
  • HUMOR