Thursday, August 18, 2016

''ORACLE FORMATS TO TEXT- Missing is an ISO LIST in EXCEL of the COUNTRY/STATE codes.
Attribute VB_Name = "tEXTtOOLS"

Option Explicit

Function CleanString(strString As String)
Dim result, re

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    '''http://www.regular-expressions.info/vbscriptexample.html
    '''regex   =      *.  9999    .  RFA or RVT or RTE
    ''.Pattern = ".*?(\.)(\d{4})(\.)(rfa|rvt|rte)"
    .Pattern = "[^A-Z,0-9,a-z]"
    CleanString = .Replace(strString, "_")
   End With


Set re = Nothing
End Function

Function SplitOracle(strOracle As String, Optional retval As Integer)
''United States/CA/San Diego/US - San Diego, CA - 401 West A Street
retval = retval - 1
Dim result, re, objRow
Dim ISO As ListObject
Set ISO = shtISO3166.ListObjects("ISO3166x")
       
On Error GoTo SplitOracle_Err

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    .Pattern = "(.*?)(?:\/|,|$)"
    ''CleanString = .Replace(strString, "_")
    Set result = .Execute(Trim(strOracle))
   End With
  
   If retval > result.count - 1 Then retval = result.count
  
    SplitOracle = UCase(result(retval).Value)
    SplitOracle = Trim(Left(SplitOracle, Len(SplitOracle) - 1))
  
    ''UNITED STATES OF AMERICA (THE) is proper ISO3166 formats the name
    If SplitOracle = "UNITED STATES" Then SplitOracle = UCase("United States of America (the)") ''commonly malformed entry IN ORACLE
       
   Select Case retval
    Case 0: ''country code

        For Each objRow In ISO.DataBodyRange.Rows
            If UCase(objRow.Cells(1, 1)) Like "*" & SplitOracle & "*" Then
                SplitOracle = UCase(objRow.Cells(1, 4).Value)
                Exit For
            End If
        Next objRow

    Case Else:
        With re
         .Global = True
         .IgnoreCase = True
         .Pattern = "[^A-Z,0-9,a-z]"
         ''CleanString = .Replace(strString, "_")
         SplitOracle = .Replace(SplitOracle, "_")
        End With
    End Select


GoTo SplitOracle_cleanup:
SplitOracle_Err:
SplitOracle = "#ERR"

SplitOracle_cleanup:
Set re = Nothing

End Function

Function OracleToName(strOracle As String)

''United States/CA/San Diego/US - San Diego, CA - 401 West A Street
Dim result, re, objRow
Dim intResults As Integer
Dim arrX() As String
Dim I As Integer

Dim ISO As ListObject
Set ISO = shtISO3166.ListObjects("ISO3166x")
       
On Error GoTo OracleToName_cleanup_Err

   Set re = New RegExp
   With re
    .Global = True
    .IgnoreCase = True
    .Pattern = "(.*?)(?:\/|,|$)"
    ''CleanString = .Replace(strString, "_")
    Set result = .Execute(Trim(strOracle))
   End With
   intResults = result.count - 1
  
   ReDim arrX(0 To intResults)
  
   For I = 0 To intResults

    ''regexp doesn't behave exactly correctly to the vanilla version- clean up delimeters:
    arrX(I) = UCase(result(I).Value) ''set workable value
   
    Do While Right(arrX(I), 1) = "/" Or Right(arrX(I), 1) = "," Or Right(arrX(I), 1) = " " Or Right(arrX(I), 1) = "\"
        arrX(I) = Trim(Left(arrX(I), Len(arrX(I)) - 1))
    Loop

    ''UNITED STATES OF AMERICA (THE) is proper ISO3166 formats the name
    If arrX(I) = "UNITED STATES" Then arrX(I) = UCase("United States of America (the)")   ''commonly malformed entry IN ORACLE
      
    Select Case I
    Case 0: ''country code
   
        For Each objRow In ISO.DataBodyRange.Rows
            If UCase(objRow.Cells(1, 1)) Like "*" & arrX(I) & "*" Then
                arrX(I) = UCase(objRow.Cells(1, 4).Value)
                Exit For
            End If
        Next objRow
   
    Case Else:
        With re
         .Global = True
         .IgnoreCase = True
         .Pattern = "[^A-Z,0-9,a-z]"
         ''CleanString = .Replace(strString, "_")
         arrX(I) = .Replace(arrX(I), "_")
        End With
    End Select
    Next I

    I = 0
    Do While arrX(I) > ""
        If I > 0 Then OracleToName = OracleToName & "."
        OracleToName = OracleToName & arrX(I)
        I = I + 1
    Loop


GoTo OracleToName_cleanup:
''''''''''''''''''''''''''''''''''''''''''''''''
'''error out
OracleToName_cleanup_Err:
'MsgBox Err.Description, vbCritical + vbOKOnly
'Stop
'Resume
'
''VBA provides a function called CVErr that takes a numeric input parameter
'specifying the error and returns a real error value that Excel will recognize
'as an error. The values of the input parameter to CVErr are in the XLCVError
'Enum and are as follows:
'
'xlErrDiv0 (= 2007) returns a #DIV/0! error.
'xlErrNA (= 2042) returns a #N/A error.
'xlErrName (= 2029) returns a #NAME? error.
'xlErrNull (= 2000) returns a #NULL! error.
'xlErrNum (= 2036) returns a #NUM! error.
'xlErrRef (= 2023) returns a #REF! error.
'xlErrValue (= 2015) returns a #VALUE! error.
OracleToName = CVErr(xlErrNA)

'''''''''''''''''''''''''''''''''''''''''''''''''''
''exit clean
OracleToName_cleanup:
    Set re = Nothing
   
End Function


No comments:

Post a Comment