Click here to Skip to main content
15,882,464 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I NEED HELP To convert Excel Macro (VB) code to SQL statements...

I would usually copy the 3 Values (ID1, ID2 and ID3) into an excel worksheet and run the Macro which creates the "Output" (single digit between 0-9) and "OCR Code" (which is a concatenated value of ID1, ID2, ID3 and Output columns). Below are what these are:

1. ID1 can be anywhere from 3 digits upto 8 digits and also can start with a # or letter with a "-" as 2 character, therefore format is required in the OCR Code output.
2. ID2 is always a big number only, no characters, and prefixed with zeros to output a total of 11 digits.
3. ID3 is a mix of letters and is formatted to have an output of 5 characters.
4. Output column is generated by the code that runs in the Macro (VB) attached within the excel.
5. OCR Code - This is the final output which is a concatenated version of all the 4 columns of data.

ID1	ID2	ID3	Output	OCR Code
14368	123262891	E0473	6	0 00014368 00123262891 E0473 6
8-10704535	123214785	E0473	7	8 10704535 00123214785 E0473 7
231640	123258733	T571	3	0 00231640 00123258733 1T571 3


Below is the Macro code attached to the excel worksheet. I don't know how to convert this into T-SQL (basically SQL statements) so that it works as if I had loaded the 3 values in a table and when the SQL runs, I get the OCR Code output.


VB
Sub Check_Digits()
    
    Dim iRow   As Long
    
    Dim strID1       As String
    Dim strID2       As String
    Dim strID3       As String
    
    Dim TitleLetter As String
    Dim ColumnLN  As Long
    Dim strDigit As Integer
    Dim STRnUM As String
   
    Dim i As Integer
    Dim iEven As Integer
    Dim iOdd As Integer
    Dim iTotal As Integer
    Dim strOneChar As String
    Dim iTemp As Integer
    
    Dim strID1OUT       As String
    Dim strID2OUT       As String
    Dim strID3OUT       As String
    Dim strID3OUT2      As String
   
    strDigit = 0
    iRow = 2: ColumnLN = 1
    strID1 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN))))
    strID2 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 1))))
    strID3 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 2))))
     
    Do While (Trim(strID1) <> "")

        iEven = 0: iOdd = 0: iTotal = 0: strOneChar = "": iTemp = 0

        If Len(strID3) = 1 Then
            STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
                     Right("00000000000" & strID2, 11) & _
                     Right("00000" & strID3, 5)
        ElseIf Len(strID3) = 4 Then
            STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
                     Right("00000000000" & strID2, 11) & _
                     Right("11111" & strID3, 5)
        Else
            STRnUM = Right(("0000000000" & Trim(Replace(strID1, "-", ""))), 10) & _
                     Right("00000000000" & strID2, 11) & _
                     Right("11111" & strID3, 5)
        End If

        Debug.Print STRnUM & "   " & Len(STRnUM)

        ' Add digits in even ordinal positions
        ' starting from rightmost
        For i = Len(STRnUM) - 1 To 2 Step -2

            strOneChar = Mid$(STRnUM, i, 1)
                
            If IsNumeric(strOneChar) Then
                iEven = iEven + CInt(strOneChar)
            Else
                Select Case UCase(strOneChar)
                    Case "A", "K", "U"
                        iEven = iEven + 0
                    Case "B", "L", "V"
                        iEven = iEven + 1
                    Case "C", "M", "W"
                        iEven = iEven + 2
                    Case "D", "N", "X"
                        iEven = iEven + 3
                    Case "E", "O", "Y"
                        iEven = iEven + 4
                    Case "F", "P", "Z"
                        iEven = iEven + 5
                    Case "G", "Q"
                        iEven = iEven + 6
                    Case "H", "R"
                        iEven = iEven + 7
                    Case "I", "S"
                        iEven = iEven + 8
                    Case "J", "T"
                        iEven = iEven + 9
                End Select
            End If
            Debug.Print (strOneChar)
        Next i

        ' Process digits in odd ordinal positions
        ' starting from rightmost
        strOneChar = "": i = 0
        For i = Len(STRnUM) To 1 Step -2
            strOneChar = Mid$(STRnUM, i, 1)
            If IsNumeric(strOneChar) Then
                ' Double it
                iTemp = CInt(strOneChar) * 2
                If iTemp > 9 Then
                    ' Break the digits (e.g., 19 becomes 1+9)
                    iOdd = iOdd + (iTemp \ 10) + (iTemp - 10)
                Else
                    iOdd = iOdd + iTemp
                End If
            Else
                Select Case UCase(strOneChar)
                    Case "A", "K", "U"
                        iTemp = 0
                    Case "B", "L", "V"
                        iTemp = 1
                    Case "C", "M", "W"
                        iTemp = 2
                    Case "D", "N", "X"
                        iTemp = 3
                    Case "E", "O", "Y"
                        iTemp = 4
                    Case "F", "P", "Z"
                        iTemp = 5
                    Case "G", "Q"
                        iTemp = 6
                    Case "H", "R"
                        iTemp = 7
                    Case "I", "S"
                        iTemp = 8
                    Case "J", "T"
                        iTemp = 9
                End Select
                iTemp = iTemp * 2
                If iTemp > 9 Then
                    ' Break the digits (e.g., 19 becomes 1+9)
                    iOdd = iOdd + (iTemp \ 10) + (iTemp - 10)
                Else
                    iOdd = iOdd + iTemp
                End If
            End If
            Debug.Print iTemp & "  "; iOdd
        Next i

        ' Add even and odd
        iTotal = iEven + iOdd

        ' Return the 10's complement
        If iTotal Mod 10 = 0 Then
            CheckDigit = 0
        Else
            CheckDigit = 10 - (iTotal Mod 10)
        End If


        Sheet1.Cells(iRow, ColumnLN + 3) = CheckDigit
        
        If InStr(1, strID1, "-") > 0 Then
            strID1OUT = Right(("0000000000" & Trim(Replace(strID1, "-", " "))), 10)
        Else
            If InStr(1, strID1, "C") > 0 Then
                strID1OUT = "C " & Right(("00000000" & Trim(strID1)), 8)
            Else
                If InStr(1, strID1, "S") > 0 Then
                    strID1OUT = "S " & Right(("00000000" & Trim(strID1)), 8)
                Else
                    strID1OUT = "0 " & Right(("00000000" & Trim(strID1)), 8)
                End If
            End If
        End If
        strID2OUT = Right("00000000000" & strID2, 11)
        
        If Len(strID3) = 1 Then
            strID3OUT = Right("00000" & strID3, 5)
            strID3OUT2 = Right("0000" & strID3, 5)
        ElseIf Len(strID3) = 4 Then
            strID3OUT = Right("11111" & strID3, 5)
            strID3OUT2 = strID3
        Else
            strID3OUT = strID3
            strID3OUT2 = strID3
        End If
        
        Sheet1.Cells(iRow, ColumnLN + 5) = strID1OUT & " " & strID2OUT & " " & strID3OUT & " " & CheckDigit
    
        Sheet1.Cells(iRow, ColumnLN + 6) = strID1OUT & " " & strID2OUT & " " & strID3OUT2 & " " & CheckDigit
    
        iRow = iRow + 1
        strID1 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN))))
        strID2 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 1))))
        strID3 = UCase(Trim(CStr(Worksheets(1).Cells(iRow, ColumnLN + 2))))

    Loop
    Sheet1.Cells(1, ColumnLN + 3) = "Check_Digit"
    Sheet1.Cells(1, ColumnLN + 5) = "Output"
    MsgBox "Finished!", vbInformation + vbOKOnly
    
End Sub


What I have tried:

Tried to understand but could not .... HELP !!!
Posted
Updated 3-Jul-20 16:22pm
v2

1 solution

Why in 'SQL' ? you've got code you could convert to C++ just as easily, then add it as an external stored procedure as per Adding an Extended Stored Procedure to SQL Server - SQL Server | Microsoft Docs[^]

btw, this is some of the same sort of thing you'd end up doing in SQL Calculating and Verifying Check Digits in T-SQL - Simple Talk[^] - not impossible
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900