VBA Pattern Matching with the Levenshtein Algorithm

Public Sub match()
    'MsgBox ("asdfs")
    For i = 3 To 200
    
        If (Cells(i, 2).Value <> "") Then
        
            If (Trim(UCase(Cells(i, 2).Value)) = Trim(UCase(Cells(i, 3).Value))) _
            And (Trim(UCase(Cells(i, 4).Value)) = Trim(UCase(Cells(i, 5).Value))) _
            And (Trim(UCase(Cells(i, 6).Value)) = Trim(UCase(Cells(i, 7).Value))) _
            And (Trim(UCase(Cells(i, 8).Value)) = Trim(UCase(Cells(i, 9).Value))) _
            Then
                Cells(i, 13).Value = "Y"
            Else
                Cells(i, 13).Value = "N"
            End If
            Cells(i, 14).Value = Levenshtein(Cells(i, 2).Value, Cells(i, 3).Value)
            Cells(i, 15).Value = Levenshtein(Cells(i, 4).Value, Cells(i, 5).Value)
            Cells(i, 16).Value = Levenshtein(Cells(i, 6).Value, Cells(i, 7).Value)
            Cells(i, 17).Value = Levenshtein(Cells(i, 8).Value, Cells(i, 9).Value)
            Cells(i, 18).Value = Cells(i, 14).Value + Cells(i, 15).Value + Cells(i, 16).Value + Cells(i, 17).Value
        Else
            Cells(i, 13).Value = ""
            Cells(i, 14).Value = ""
            Cells(i, 15).Value = ""
            Cells(i, 16).Value = ""
            Cells(i, 17).Value = ""
            Cells(i, 18).Value = ""
        End If
        
        ' Write sql
        ' Good ones
         If ((Cells(i, 13).Value = "Y") Or (Cells(i, 18).Value < 5)) And (Cells(i, 2).Value <> "") Then
        ' Likely good
        ' If (Cells(i, 18).Value > 4) (Cells(i, 18).Value < 10) Then
            writeSQL (i)
        ' the names are an exact match but there is not city/state/zip information
        ' this is the case with a lot of the SMO_2015_Voting_Member_List ones
        ElseIf (UCase(Trim(Cells(i, 2).Value)) = UCase(Trim(Cells(i, 3).Value))) _
            And UCase(Trim((Cells(i, 4).Value)) = UCase(Trim(Cells(i, 5).Value))) _
            And UCase(Trim((Cells(i, 6).Value)) = "NULL") _
            And UCase(Trim((Cells(i, 8).Value)) = "NULL") _
            And UCase(Trim((Cells(i, 10).Value)) = "NULL") Then
            writeSQLNULL (i)
        Else
            Cells(i, 20).Value = ""
            Cells(i, 21).Value = ""
        End If
        
    Next i
    
End Sub


Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long
    'https://en.wikipedia.org/wiki/Levenshtein_distance
    'http://stackoverflow.com/questions/4243036/levenshtein-distance-in-excel

    string1 = UCase(Trim(string1))
    string2 = UCase(Trim(string2))

    Dim i As Long, j As Long
    Dim string1_length As Long
    Dim string2_length As Long
    Dim distance() As Long
    
    string1_length = Len(string1)
    string2_length = Len(string2)
    ReDim distance(string1_length, string2_length)
    
    For i = 0 To string1_length
        distance(i, 0) = i
    Next
    
    For j = 0 To string2_length
        distance(0, j) = j
    Next
    
    For i = 1 To string1_length
        For j = 1 To string2_length
            If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
                distance(i, j) = distance(i - 1, j - 1)
            Else
                distance(i, j) = Application.WorksheetFunction.Min _
                (distance(i - 1, j) + 1, _
                 distance(i, j - 1) + 1, _
                 distance(i - 1, j - 1) + 1)
            End If
        Next
    Next
    
    Levenshtein = distance(string1_length, string2_length)
    
End Function

Function writeSQL(ByVal i As Integer)

    Dim customer As String, firstName As String, lastName As String, _
    city As String, state As String, zip As String, _
    _id As String, sqlSelect As String, sqlUpdate As String
        
    customer = RTrim(Cells(i, 1).Value)
    firstName = RTrim(Cells(i, 3).Value)
    lastName = RTrim(Cells(i, 5).Value)
    city = RTrim(Cells(i, 6).Value)
    state = RTrim(Cells(i, 8).Value)
    zip = RTrim(Cells(i, 10).Value)
    _id = RTrim(Right("0000000000000" & Cells(i, 12).Value, 12))
    
    sqlSelect = "-- SELECT * FROM ca WHERE ca.firstName = '" + _
    firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
    "' AND [state] = '" + state + "' AND zip = '" + zip + "'"

    sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca. = '" + _id + _
    "' FROM ca WHERE ca.firstName = '" + firstName + "' AND " + _
    "ca.lastName = '" + lastName + "' AND city = '" + city + "' AND [state] = '" + state + _
    "' AND zip = '" + zip + "' AND customer = '" + customer + "'"

    If (firstName = "firstName") Then
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = ""
        Rows(i).Interior.Color = 45535
    Else
        'Cells(i, 20).Value = sqlSelect
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = sqlUpdate
        Rows(i).Interior.Color = 65535
    End If
    
End Function

Function writeSQLNULL(ByVal i As Integer)

    Dim customer As String, firstName As String, lastName As String, _
    city As String, state As String, zip As String, _
    ship_master_customer_id As String, sqlSelect As String, sqlUpdate As String
        
    customer = Cells(i, 1).Value
    firstName = Cells(i, 3).Value
    lastName = Cells(i, 5).Value
    city = Cells(i, 6).Value
    state = Cells(i, 8).Value
    zip = Cells(i, 10).Value
    _id = Right("0000000000000" & Cells(i, 12).Value, 12)
    
    sqlSelect = "-- SELECT * FROM  ca WHERE ca.firstName = '" + _
    firstName + "' AND ca.lastName = '" + lastName + "' AND city = '" + city + _
    "' AND [state] = '" + state + "' AND zip = '" + zip + "'"

    sqlUpdate = "' -- UPDATE ca SET ca.Member = 40, ca.= '" + _id + _
    "' FROM ca ca WHERE ca.firstName = '" + firstName + "' AND " + _
    "ca.lastName = '" + lastName + "' AND city IS NULL AND [state] IS NULL AND zip IS NULL AND customer = '" + customer + "'"

    If (firstName = "firstName") Then
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = ""
        Rows(i).Interior.Color = 45535
    Else
        'Cells(i, 20).Value = sqlSelect
        Cells(i, 20).Value = ""
        Cells(i, 21).Value = sqlUpdate
        Rows(i).Interior.Color = 65535
    End If
    
End Function

FYI: Resolution - Build / Compile Issues - The XML namespace for the conceptual model's Schema element is not supported on this project's target framework version

Apparently when compiled w/ a 2012 vs. a 2013 it IDE (minimum known is “Premium”) it will no longer play nicely yielding many rabbit holes and a rather generic error of “The XML namespace for the conceptual model's Schema element is not supported on this project's target framework version”. 

Force Restart Over RDC

http://www.fixedbyvonnie.com/2013/11/shutdown-restart-windows-remote-desktop-connection/

How to shutdown or restart Windows over a Remote Desktop connection

Posted on November 20, 2013 by vonnie — 5 Comments ↓

I would love the world to think of me as an industrious, prolific IT director and blogger; however, I’ve finally avowed this one truth:

I’m lazy.

Yep.  Why hide it.  It’s true.

I like to take the easy way out, the path of least resistance, and I have a propensity to be dormant unless someone lights a flame under my butt.

My indolence is precisely what makes programs like Remote Desktop such a joy to use.  Why should I get up and walk over to a server when I can remotely connect to it from the comfort of my desk?  Remote Desktop lets me access almost any Windows computer on my network and gives me the feeling of sitting right there in front of the box.

A bunch of smart people at Microsoft concocted the Remote Desktop Protocol (RDP), and with the advent ofWindows XP,  Microsoft began bundling the Remote Desktop Connection (RDC) with ever OS since.

The RDC client is great because it supports cool features like 24-bit color, audio, and file system redirection. So, for example, you can open a Youtube video on the remote computer and hear the audio on your local machine. Or you can access the files on your local machine from the remote machine.  All this magic is made possible through the RDP and what Microsoft calls the Terminal Services client (mstsc.exe).

Whenever you connect to a Microsoft Server, the Shutdown and Restart buttons are visible in the little arrow immediately to the right of the lock icon.

But you might be wondering how to do the same thing when opening a remote connection on a Windows 7 box.

As you can see, there’s only a Lock and Disconnect option but no Shutdown or Restart function.

To restart the remote computer, click the Start button and enter this command in the search box:

shutdown /r /t 0

The /r switch tells the machine to reboot and the /t option sets the number of seconds to execute the command. Since we set /t to zero, the restart should initiate immediately.

To shutdown the machine just enter:

shutdown /s

The other option is to just to click the Desktop and press Alt + F4 to bring up the shutdown menu.

Finally, if you execute the shutdown command but then suddenly realize you made a mistake type:

shutdown /a

This will attempt to abort the shutdown sequence.

About vonnie

Connect with Vonnie on Twitter

Find the BAK, MDF and LDF Files

-- Find the BAK files:
SELECT physical_device_name,
                backup_start_date,
                backup_finish_date,
                backup_size/1024.0 BackupSizeKB
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
WHERE database_name like '%%'
ORDER BY backup_finish_date DESC

-- Find the MDF's and LDF's:
DECLARE @command varchar(1000)
SELECT @command = 'SELECT * FROM sys.database_files'
EXEC sp_MSforeachdb @command

-- Or ... easier
EXEC sp_MSforeachdb 'USE ? SELECT ''?'', SF.filename, SF.size FROM sys.sysfiles SF'

-- Or ... even easier

SELECT 'USE [' + name + ']; SELECT SF.filename, SF.size FROM sys.sysfiles SF'

FROM master.dbo.sysdatabases