Extract specific data from one text field (1 Viewer)

Alhakeem1977

Registered User.
Local time
Today, 17:28
Joined
Jun 24, 2017
Messages
308
I have got a text field called [Address] contains the whole address of the customer which has been extracted from an XML file with the below result:

Flat/Villa 12 Bldg #:2358 Road :8347 Rd Name :ROAD 8347/طريق 8347 Block #:666 Block :SAAN TOWN Gov #:0

I would like to extract the value only of the address field and populate them to specific fields like:
[Flat/Villa] 12
[bldg#:] 2358
[Road] 8347
[Rd Name :]
[Block# :] 666
[block :] SALMAN TOWN/مدينة سلمان Gov

How can I create a query or VBA to populate the above fields?

Your earliest response would be highly appreciated.


Thanks in advance.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 01:28
Joined
Jan 20, 2009
Messages
12,849

Alhakeem1977

Registered User.
Local time
Today, 17:28
Joined
Jun 24, 2017
Messages
308
Thanks for your earliest response, yes, I did but I got the above result in one field and the rest I couldn't you can refer to the below:

<SmartcardData>
<AddressArabic>Flat/Villa 0 Bldg #:79 Road :23 Rd Name :ROAD 23/???? 23 Block #:583 Block : TOWN/?? Gov #:0</AddressArabic>
<AddressEnglish>Flat/Villa 0 Bldg #:79 Road :23 Rd Name :ROAD 23/???? 23 Block #:583 Block : TOWN/?? Gov #:0</AddressEnglish>
<ArabicFullName>???????????? </ArabicFullName>
<BirthDate>01/01/1977</BirthDate>
<CardCountry>BAH</CardCountry>
<CardexpiryDate>10/08/2022</CardexpiryDate>
<CardIssueDate>10/08/2017</CardIssueDate>
<CardSerialNumber>210555555554</CardSerialNumber>
<EmploymentFlag>C</EmploymentFlag>
<EmploymentId>0601</EmploymentId>
<EmploymentNameArabic>????????????</EmploymentNameArabic>
<EmploymentNameEnglish>BAPCO</EmploymentNameEnglish>
<EnglishFullName>NOHA???????????</EnglishFullName>
<FingerprintCode>0</FingerprintCode>
<Gender>F</Gender>
<IdNumber>??????????486</IdNumber>
<IsMatchOnCardAvailiable>false</IsMatchOnCardAvailiable>
<MiscellaneousBinaryData>
<item key="CountryFlagMini" value="Qk3qAQAAAAAAAFoAAAAoAAAAIQAAABQAAAABAAQAAAAAAAAAAADEDgAAxA4AAAkAAAAJAAAAJhHO/ycSzv88KdP/YlPc/5GH5v+6s+//5uT5//z7/v//////iIiIiGQgAAAAAAAAAAAAAAAAAACIiIiIiHUxAAAAAAAAAAAAAAAAAIiIiIiIdTEAAAAAAAAAAAAAAAAAiIiIiGQgAAAAAAAAAAAAAAAAAACIiIiIZCAAAAAAAAAAAAAAAAAAAIiIiIiIdTEAAAAAAAAAAAAAAAAAiIiIiIh1MQAAAAAAAAAAAAAAAACIiIiIZCAAAAAAAAAAAAAAAAAAAIiIiIhkIAAAAAAAAAAAAAAAAAAAiIiIiIh1MQAAAAAAAAAAAAAAAACIiIiIiHUxAAAAAAAAAAAAAAAAAIiIiIhkIAAAAAAAAAAAAAAAAAAAiIiIiGQgAAAAAAAAAAAAAAAAAACIiIiIiHUxAAAAAAAAAAAAAAAAAIiIiIiIdTEAAAAAAAAAAAAAAAAAiIiIiGQgAAAAAAAAAAAAAAAAAACIiIiIZCAAAAAAAAAAAAAAAAAAAIiIiIiIdTEAAAAAAAAAAAAAAAAAiIiIiIh1MQAAAAAAAAAAAAAAAACIiIiIZCAAAAAAAAAAAAAAAAAAAA==" />
</MiscellaneousBinaryData>
<MiscellaneousTextData>
<item key="FirstNameArabic" value="????????" />
<item key="LastNameArabic" value="?????" />
<item key="MiddleName1Arabic" value="?????" />
<item key="MiddleName2Arabic" value="???????" />
<item key="MiddleName3Arabic" value="" />
<item key="MiddleName4Arabic" value="" />
<item key="BloodGroup" value="" />
<item key="CPRNO" value="???????486" />
<item key="DateOfBirth" value="19770101" />
<item key="FirstNameEnglish" value="A????DHA" />
<item key="LastNameEnglish" value="HA??D" />
<item key="MiddleName1English" value="ALI" />
<item key="MiddleName2English" value="AB??????LLA" />
<item key="MiddleName3English" value="" />
<item key="MiddleName4English" value="" />
<item key="Gender" value="F" />
<item key="Email" value="r??????@gmail.com" />
<item key="ContactNo" value="????????772" />
<item key="ResidenceNo" value="??????2" />
<item key="FlatNo" value="0" />
<item key="BuildingNo" value="79" />
<item key="BuildingAlpha" value="" />
<item key="BuildingAlphaArabic" value=" " />
<item key="RoadNo" value="23" />
<item key="RoadName" value="ROAD 23" />
<item key="RoadNameArabic" value="???? 23" />
<item key="BlockNo" value="583" />
<item key="BlockName" value="SA TOWN" />
<item key="BlockNameArabic" value="????????????" />
<item key="GovernorateNo" value="0" />
<item key="GovernorateNameEnglish" value="?????THERN" />
<item key="GovernorateNameArabic" value="???????????" />
<item key="EmployerName1Arabic" value="????????????" />
<item key="LatestEducationDegreeArabic" value="" />
<item key="OccupationDescription1Arabic" value="??" />
<item key="SponsorNameArabic" value="" />
<item key="ClearingAgentIndicator" value="F" />
<item key="EmployerFlag1" value="C" />
<item key="EmployerName1" value="BAPCO" />
<item key="EmployerNo1" value="4601" />
<item key="EmploymentFlag1" value="0" />
<item key="LaborForceParticipation" value="1" />
<item key="LatestEducationDegree" value="" />
<item key="OccupationDescription1" value="GER" />
<item key="SponsorCPRNoorUnitNo" value="0" />
<item key="SponsorFlag" value="" />
<item key="SponsorName" value="" />
<item key="LfpNameEnglish" value="WORKING" />
<item key="LfpNameArabic" value="????" />
<item key="EnglishCountryName" value="Bahrain" />
<item key="ArabicCountryName" value="????? ???????" />
<item key="IACOCode" value="048" />
<item key="Alpha2Code" value="BH" />
<item key="Alpha3Code" value="BHR" />
<item key="Nationality" value="499" />
<item key="CountryOfBirth" value="" />
<item key="PassportNo" value="??????6/2" />
<item key="PassportType" value="7" />
<item key="PassportSequnceNo" value="0" />
<item key="IssueDate" value="20120808" />
<item key="ExpiryDate" value="20220808" />
<item key="VisaNo" value="" />
<item key="VisaExpiryDate" value="" />
<item key="VisaType" value="" />
<item key="ResidentPermitNo" value="" />
<item key="ResidentPermitExpiryDate" value="" />
<item key="TypeOfResident" value="" />
<item key="CardVerificationStatus" value="Active" />
</MiscellaneousTextData>
<NationalityCode>499</NationalityCode>
<OccupationArabic>?????</OccupationArabic>
<OccupationEnglish>NGER</OccupationEnglish>
<PassportExpiryDate>08/08/2022</PassportExpiryDate>
<PassportIssueDate>08/08/2012</PassportIssueDate>
<PassportNumber>92</PassportNumber>
<PassportType>7</PassportType>
<SponserId>0</SponserId>
<SponserNameArabic />
<SponserNameEnglish />
</SmartcardData>

I tried to extract from: <MiscellaneousTextData> the specific address but I couldn't I got only the <SmartcardData> data.

Below is my VBA code:
Code:
Private Sub cmdGetSmartCardData_Click()

Dim Cancel As Variant
Beep
   If MsgBox("Please make sure you insert the Smart Card to the reader." & vbCrLf & "Are you sure you want to proceed?", vbYesNo, "Confirm Excution") = vbNo Then
       Cancel = True
       Exit Sub
       Else
    Me.lblWait.Visible = True
On Error Resume Next
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim strPath As String ' Path to file folder
Dim User As String
User = Environ("UserName")

    strPath = "C:\Users\" & [User] & "\AppData\Local\Temp\"
 '   strPath = "C:\Users\redha\AppData\Local\Temp\"
    strFile = Dir(strPath & "eRevealerGcc*.XML")
 
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
 
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
 
    'cycle through the list of files
    For intFile = 1 To UBound(strFileList)
        Application.ImportXML strPath & strFileList(intFile), 2
    Next intFile
 '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim SQL As String
    Dim SQLD1 As String
    Dim SQLD2 As String
    Dim SQLD3 As String
    
    
    SQL = "UPDATE Customers, SmartcardData SET Customers.CustomerName = [SmartcardData].[EnglishFullName], " & _
    "Customers.[Job Title] = [SmartcardData].[OccupationEnglish], Customers.Address = [SmartcardData].[AddressEnglish], " & _
    "Customers.BirthDate = [SmartcardData].[BirthDate], Customers.CardCountry = [SmartcardData].[CardCountry], " & _
    "Customers.CardexpiryDate = [SmartcardData].[CardexpiryDate], Customers.CardIssueDate = [SmartcardData].[CardIssueDate], " & _
    "Customers.EmploymentId = [SmartcardData].[EmploymentId], Customers.EmploymentNameEnglish = [SmartcardData].[EmploymentNameEnglish], " & _
    "Customers.Gender = [SmartcardData].[Gender], Customers.IdNumber = [SmartcardData].[IdNumber], " & _
    "Customers.PassportExpiryDate = [SmartcardData].[PassportExpiryDate], Customers.PassportIssueDate = [SmartcardData].[PassportIssueDate], " & _
    "Customers.PassportNumber = [SmartcardData].[PassportNumber], Customers.SponserId = [SmartcardData].[SponserId], " & _
    "Customers.SponserNameEnglish = [SmartcardData].[SponserNameEnglish]" & _
    "WHERE (((Customers.ID)=[Forms]![Customer Details]![ID]));"
    
    SQLD1 = "Delete From SmartcardData"
    SQLD2 = "Delete From MiscellaneousBinaryData"
    SQLD3 = "Delete From MiscellaneousTextData"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQL
    DoCmd.RunSQL SQLD1
    DoCmd.RunSQL SQLD2
    DoCmd.RunSQL SQLD3
    DoCmd.SetWarnings True
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Me.lblWait.Visible = False
    Refresh
    MsgBox "Smart Card data imported successfully", vbOKOnly, "Successful Operation"
End If
End Sub

But if it's not possible to get it by VBA let's extract the data from the text field in MS Access and populate them to specific fields.

thanks for your help.
 

ebs17

Well-known member
Local time
Today, 15:28
Joined
Feb 7, 2020
Messages
1,883
I would use regular expressions (keyword for further self-research).
 

ebs17

Well-known member
Local time
Today, 15:28
Joined
Feb 7, 2020
Messages
1,883
Approach for a solution - works on this one example value:
Code:
Sub do_RegEx()
    Const cText = "Flat/Villa 12 Bldg #:2358 Road :8347 Rd Name :ROAD 8347/???? 8347 Block #:666 Block :SAAN TOWN Gov #:0 "
    Dim oMC As Object
    Dim i As Long

    Set oMC = RegExMatchCollection(cText, _
                                   "(Flat/Villa )(\d+) (Bldg #:)(\d+) (Road :)(\d+) (Rd Name :)(.*)" & _
                                   " (Block #:)(\d+) (Block :)(.*)")
    If oMC.Count > 0 Then
        'Debug.Print oMC.Count
        'Debug.Print oMC(oMC.Count - 1).SubMatches.Count
      
        For i = 0 To oMC(0).SubMatches.Count - 1 Step 2
            Debug.Print oMC(0).SubMatches(i), oMC(0).SubMatches(i + 1)
            ' here you could now write in the table
        Next
    End If

End Sub
Code:
' In a standard module'

Private pRegEx As Object

Public Property Get oRegEx(Optional Reset As Boolean) As Object
   If (pRegEx Is Nothing) Then Set pRegEx = CreateObject("Vbscript.Regexp")
   If Reset Then Set pRegEx = Nothing
   Set oRegEx = pRegEx
End Property

Public Function RegExMatchCollection(ByVal SourceText As String, _
      ByVal SearchPattern As String, _
      Optional ByVal bIgnoreCase As Boolean = True, _
      Optional ByVal bGlobal As Boolean = True, _
      Optional ByVal bMultiLine As Boolean = True) As Object

   With oRegEx
      .Pattern = SearchPattern
      .IgnoreCase = bIgnoreCase
      .Global = bGlobal
      .MultiLine = bMultiLine
      Set RegExMatchCollection = .Execute(SourceText)
   End With
End Function
 
Last edited:

Alhakeem1977

Registered User.
Local time
Today, 17:28
Joined
Jun 24, 2017
Messages
308
Thanks for your help,.
Sorry of my limited language in VBA how can I use the booth solutions? In which event I have to call them? And how to call them?
Thanks in advance.
 
Last edited:

ebs17

Well-known member
Local time
Today, 15:28
Joined
Feb 7, 2020
Messages
1,883
The use of the approach depends on the context of the other analysis of the original document.

As an example, I have converted the procedure do_RegEx into a function GetValuesFromExpression, which takes the expression mentioned and outputs a pipe-separated string. the output string can be split in the calling procedure, the individual contents can be written into a table.

Code:
Function GetValuesFromExpression(ByVal AnyText As String) As String
    Dim oMC As Object
    Dim sResult As String
    Dim i As Long

    Set oMC = RegExMatchCollection(AnyText, _
                                   "(Flat/Villa )(\d+) (Bldg #:)(\d+) (Road :)(\d+) (Rd Name :)(.*)" & _
                                   " (Block #:)(\d+) (Block :)(.*)")
    If oMC.Count > 0 Then
        For i = 1 To oMC(0).SubMatches.Count - 1 Step 2
            sResult = sResult & "|" & oMC(0).SubMatches(i)
        Next
        If Len(sResult) > 0 Then sResult = Mid(sResult, 2)
    End If
    GetValuesFromExpression = sResult
End Function

Sub call_GetValuesFromExpression()
     ' wherever the content comes from
    Const cText = "Flat/Villa 12 Bldg #:2358 Road :8347 Rd Name :ROAD 8347/???? 8347 Block #:666 Block :SAAN TOWN Gov #:0 "

    Dim sArr() As String
    Dim i As Long
    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("TargetTable", dbOpenDynaset, dbAppendOnly)
    With rs
        sArr = Split(GetValuesFromExpression(cText), "|")
'        For i = 0 To UBound(sArr)
'            Debug.Print sArr(i)
'        Next
        .AddNew
        .Fields("[Flat/Villa]") = sArr(0)
        .Fields("[bldg#:]") = sArr(1)
        ' ...
        .Update
        .Close
    End With
End Sub

String processing in turn provides strings as individual content. If necessary, these must be converted to the data type of the receiving field.
 

Users who are viewing this thread

Top Bottom