Importing Leads from Remote Spreadsheet

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.