Complex text file into ms access

[adamdaban]

dear friends, I have one text file and I want to import to access table here is text file contains:
<!--:Begin:Chksum:1 :-->
<!--:Begin:Msg:2:0:-->
<sample>
<ver>1.1</ver>
<instrinfo>
<p><n>PRDI</n><v>BM800</v></p>
<p><n>FIWV</n><v>2.9.3</v></p>
<p><n>SNO</n><v>26052</v></p>
<p><n>BRND</n><v>M</v></p>
<p><n>IAPL</n><v>H</v></p>
<p><n>LMOF</n><v>1</v></p>
<p><n>PMPM</n><v>25</v></p>
</instrinfo>
<smpinfo>
<p><n>ID</n><v>8798176</v></p>
<smpresults>
<p><n>RBC</n><v>0.00</v><l>0.00</l><h>0.01</h></p>
<p><n>MCV</n><l>0.5</l><h>0.0</h></p>
<p><n>HCT</n><l>0.3</l><h>0.0</h></p>

so I want to import specific part like these words have color red and bold, any one can help me?

Regards,

[ADezii]

1. How about a more concrete and clearer example of those 'specific parts' that you wish to Import?
2. Which Codes are indicators of these parts?
3. etc...

[NeoPa]

AdamDaban:
so I want to import specific part like these words have color red and bold, any one can help me?

That will be very difficult if you don't even explain what tags in your data represent red and bold. I searched for <red>, <b> & <bold> in your text and found none present. I'm astounded that the idea that we may need such information to be able to help you hadn't occurred to you before you posted the question.

[adamdaban]

this file come from lab test machine usually I will get automatically from it, this is blood test result so I need import this text file into my database, in this case, what should i do?

for example in this sample have SNO and result is 26052 so I need to get a result from each one of them

[adamdaban]

this code I got from Mr.arnelgp, so how I used? you know better than me how to use in ms access

Public Function fnSpclParser(ByVal strToParsed As String) As String
    
    Dim lngPos As Long
    Dim lngPos2 As Long
    Dim strResult As String
    
    lngPos = InStr(1, strToParsed, "<n>ID", vbTextCompare)
    If lngPos <> 0 Then
        strResult = "ID:"
        lngPos = InStr(lngPos, strToParsed, "<v>", vbTextCompare)
    End If
    If lngPos <> 0 Then
        lngPos = lngPos + Len("<v>")
        lngPos2 = InStr(lngPos, strToParsed, "</v>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "RBC", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "RBC:"
        lngPos = InStr(lngPos + Len("RBC"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "MCV", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "MCV:"
        lngPos = InStr(lngPos + Len("MCV"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    If lngPos <> 0 Then
        lngPos = InStr(lngPos, strToParsed, "HCT", vbTextCompare)
    End If
    If lngPos <> 0 Then
        strResult = strResult & "HCT:"
        lngPos = InStr(lngPos + Len("HCT"), strToParsed, "<l>", vbTextCompare)
        lngPos = lngPos + Len("<l>")
        lngPos2 = InStr(lngPos, strToParsed, "</l>", vbTextCompare)
        strResult = strResult & Mid(strToParsed, lngPos, lngPos2 - lngPos) & ";"
        lngPos = lngPos2
    End If
    
    fnSpclParser = strResult
End Function

Private Sub test()
    Dim var1 As Variant
    Dim var2 As Variant
    Dim i As Integer
    Dim strToParsed As String
    strToParsed = "<!--:Begin:Chksum:1:-->" & _
    "<!--:Begin:Msg:2:0:-->" & _
    "<sample>" & _
    "<ver>1.1</ver>" & _
    "<instrinfo>" & _
    "<p><n>PRDI</n><v>BM800</v></p>" & _
    "<p><n>FIWV</n><v>2.9.3</v></p>" & _
    "<p><n>SNO</n><v>26052</v></p>" & _
    "<p><n>BRND</n><v>M</v></p>" & _
    "<p><n>IAPL</n><v>H</v></p>" & _
    "<p><n>IID</n></p>" & _
    "<p><n>LMOF</n><v>1</v></p>" & _
    "<p><n>PMPM</n><v>25</v></p>" & _
    "</instrinfo>" & _
    "<smpinfo>" & _
    "<p><n>ID[/COLOR]</n><v>8798176</v></p>" & _
    "<smpresults>" & _
    "<p><n>RBC</n><v>0.00</v><l>0.00</l><h>0.01</h></p>" & _
    "<p><n>MCV</n><l>0.5</l><h>0.0</h></p>" & _
    "<p><n>HCT</n><l>0.3</l><h>0.0</h></p>"
    
    var1 = Split(fnSpclParser(strToParsed), ";")
    
    For i = 0 To UBound(var1) - 1
        var2 = Split(var1(i), ":")
        Debug.Print var2(0) & vbTab & " = " & var2(1)
    Next
End Sub

[adamdaban]

I edited my question you can check which words have been bold these words I want into my table

[NeoPa]

So, we now have a clear understanding of what words you are looking for and how they are identified.

As the question is far too broad in scope (We have nothing from you as to how you're reading in the file or what format you want the various words returned in.) I will restrict this thread to how to return an array of such words extracted from a string as passed into the function. This will involve identifying any text between either <n> & </n> or <v> & </v>.

Unfortunately I have to run for now but will return at a later date/time in order to offer guidance.

In the mean time I suggest you have a think about what you need from this. Currently it seems your understanding of your own issue seems to be far too unclear. I worry that you won't even know what to do with the help when it comes. This is a position you should not be in.

[Rabbit]

As far as I can tell, you have a file that is storing relational data in XML format. Where each p node represents a key-value pair with the n node representing the name of the field and the v node represents the value of that column. And where the parent node is the name of a table.

For a more robust solution, I would use an XML parser. For that, you can use the MSXML2.DOMDocum ent object.

But as NeoPa pointed out, your problem exists at a more basic level. Even if someone were to help you out with a solution, you wouldn't know how to use it. I suggest that you do an Access / VBA tutorial before you attempt a project of this scope.

[NeoPa]

Rabbit has posted that using an XML parser may be a good idea. I suspect he's correct. However I promised some help to do a specific task so here's the code for that task :

Public Function GetWords(ByRef strIn As String) As String()
Private Const conChars As String = "nv"
    Dim lngIdx As Long, lngPtr As Long, lngLen As Long
    Dim strWork As String, strChar As String

    For lngIdx = 1 To Len(conChars)
        strChar = Mid(conChars, lngIdx, 1)
        lngPtr = 1
        Do
            lngPtr = InStr(lngPtr, strIn, "<" & strChar & ">")
            If lngPtr < 1 Then Exit Do
            lngLen = InStr(lngPtr, strIn, "</" & strChar & ">") - lngPtr - 3
            strWork = strWork & "," & Mid(strIn, lngPtr + 3, lngLen)
            lngPtr = lngPtr + lngLen + 7
        Loop
    Next lngIdx
    GetWords = Split(Mid(strWork, 2), ",")
End Function

NB. I have assumed the data will be in the correct format so have written the code to crash if not. This is preferable to overlooking errors but I'll leave you to insert correct error handling for yourself.

[ADezii]

Because of the precise Start and End Tag Structure surrounding the Test Names and associated Test Values, I should be able to come up with Code that displays each Test Name and associated Values. This would only involve a Base Code Segment and a Function Call. Will keep in touch.

[ADezii]

1. Because of the precise <Tag></End Tag> structure of the Text File, I was able to write some Code that will:
   1. Process al 11,370+ lines in the Text File.
   2. Parse the Test Name.
   3. Parse the Test Values for each specific Test.
   4. Display the Test Name along with the Test Values to the Immediate Window. This information can just as easily be written to a Table, Text File, etc.
2. I am still a bit hazy on your Filtering Logic but it should be very easy to implement it within the existing Code Logic.
3. Base/Entry Level Code:
   

   Dim strLine As String
   Dim intRow As Integer
   Dim strTest As String
   Const conFolder As String = "C:\_Critical Files\"
   Const conFILE_NAME As String = "BM-26052-S-20170808-104248-6345-6396.txt"
   
   Open conFolder & conFILE_NAME For Input As #1
   
   Do While Not EOF(1)
     Line Input #1, strLine
       If InStr(strLine, "<n>") > 0 Then    'Extract Test Name
        intRow = intRow + 1
          strTest = Mid$(strLine, InStr(strLine, "<n>") + 3)       'Test Name*
          strTest = Left$(strTest, InStr(strTest, "</n>") - 1)     'Test Name
            Debug.Print strTest & " - " & fBuildResults(strLine)
       End If
   Loop
   
   Close #1

4. Function Definition:
   

   Public Function fBuildResults(strLine As String) As String
   Dim strNewLine As String
   Dim strLineTwo As String
   Dim astrDelimiters As Variant
   Dim astrEndDelimiters As Variant
   Dim intCtr As Integer
   Dim strBuild As String
   
   astrDelimiters = Array("<v>", "<l>", "<h>", "<m>", "<k>", "<w>", "<d>")
   astrEndDelimiters = Array("</v>", "</l>", "</h>", "</m>", "</k>", "</w>", "</d>")
   
   For intCtr = LBound(astrDelimiters) To UBound(astrEndDelimiters)
     If InStr(strLine, astrDelimiters(intCtr)) > 0 Then
       strLineTwo = Mid$(strLine, InStr(strLine, astrDelimiters(intCtr)) + 3)
       strNewLine = Left$(strLineTwo, InStr(strLineTwo, astrEndDelimiters(intCtr)) - 1)
         strBuild = strBuild & strNewLine & ", "
     End If
   Next
   
   fBuildResults = Left$(strBuild, Len(strBuild) - 2)
   End Function

5. Sample OUTPUT:
   

   WDCH - 149
   WLGL - 160
   WDIL - 115
   WDIH - 165
   WDWD - 2
   MCVX - 0
   RBC - 5.10, 3.50, 5.50
   MCV - 86.4, 75.0, 100.0
   HCT - 44.1, 35.0, 55.0
   MCH - 28.3, 25.0, 35.0
   MCHC - 32.8, 31.0, 38.0
   RDWR - 11.6, 11.0, 16.0
   RDWA - 52.6, 30.0, 150.0
   PLT - 253, 150, 450
   MPV - 7.9, 7.0, 11.0
   PCT - 0.20, 0.01, 9.99
   PDW - 10.9, 0.1, 99.9
   LPCR - 14.7, 0.1, 99.9
   HGB - 14.4, 11.5, 16.5
   WBC - 8.4, 3.5, 11.0
   LA - 2.1, 0.5, 5.0
   MA - 0.6, 0.1, 1.5
   GA - 5.7, 1.2, 8.0
   LR - 25.5, 15.0, 50.0
   MR - 6.6, 2.0, 15.0
   GR - 67.9, 35.0, 80.0
   RCT - 15014
   WCT - 10731
   aspt - 1778
   xfrt - 2423
   acps - 0
   asl1 - 0
   asl2 - 0
   rdmx - 22
   rdmn - 416

6. I will not go into details as to how the Code works, but should you have any questions, feel free to ask.

[adamdaban]

Thanks for all of you, but I don't know how I use your code and how to put in ms access, hope someone can do it I really need it...

[NeoPa]

AdamDaban:
I don't know how I use your code and how to put in ms access, hope someone can do it I really need it.

I'm afraid that's beyond the scope of this question. It's already got into a bit of a mess as the question was never clearly specified. Please see advice given earlier about using tutorials.

[ADezii]

this file come from lab test machine usually I will get automatically from it, this is blood test result so I need import this text file into my database, in this case, what should i do?

1. I have created a Demo for you based solely on the above quote. I'm still not clear about any specific filtering on the Blood Tests but one step at a time.
2. Download the Attachment and extract 'both' Files to the same Folder. It makes absolutely no difference what this Folder is, as long as you have the required Permissions on it.
3. Open Complex Text File.mdb and click on the only Command Button.
4. The Code, in the Click() Event of this Command Button will:
   1. Open BM-26052-S-20170808-104248-6345-6396.txt and analyze every line in this File.
   2. If a Test is detected based on the parsing of a certain Tag, it will extract the actual Line of Text, the Test Name, and the Test Values.
   3. Each of these three Items will then be added to a Table (tblTestResults ) with the Line of Text, the Test Name, and the resultant values in their own Fields. Normally I would never place multiple Values (Test Results) into a single Field, but in this case I feel as though it was warranted.
   4. The last step in the Code execution is the Opening of this Table displaying the parsed results.
   5. You need to be crystal clear as far as any other filtering of Tests, the criteria for these Filters, etc. Right now things are not that clear, at least to me.
5. I happen to live in Florida (USA) and a Category 5 Hurricane (IRMA) is bearing down on us. If I do not get back to you in a couple of days it will not be for lack of interest but for lack of power (LOL).
6. Good Luck with this Project.

P.S. - NeoPa, I have an annoying problem with this Demo (cmdTest_Click( )) that hopefully you can assist with. The Code works fine, achieving the desired results, as long as On Error Resume Next is in place. The minute I attempt to enforce a normal Error Handler, I received an Invalid Procedure Call or Argument. I have tried every Debugging Technique known to pinpoint the origin of this Error, but to no avail. It really isn't a hugh issue, but more a matter of I can't trace it. In addition, I rarely never use On Error Resume Next and am not particularly fond of it. Thanks in advance for you help in this matter.

[NeoPa]

Happy to look into it for you my friend :-)

I notice the Access file is MDB. If you have a 2010 version that exhibits the same symptoms that would save me having to find somewhere to work in 2003. If not, I can find somewhere, but if you have a 2010 version it would certainly be easier.