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.DOMDocument 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.

[adamdaban]

I just Want to say Thanks so much for your effort and helping me as much...........

[ADezii]

I just Want to say Thanks so much for your effort and helping me as much...........

That's why we are all here.

[ADezii]

@NeoPa:
Thanks for having a look at this. Here is the Upgraded Version.

[adamdaban]

Dear One last question hope you don't mind, is that posible we can get result like this for example:
SNO RBC ID
---- --- ---
27987 3.4 398774

not like this
test
----
ID
SNO
Test

[ADezii]

1. Are you filtering for the SNO and RBC Tests only?
2. If this is the case, according to the *.txt File SNO has a static Value of 26052 whereas RBC has multiple Values as the Data indicates below.
   

   Test    Results
   RBC    250, 80, 2, 9
   RBC    4.34, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    5.80, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    5.33, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    4.35, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    4.58, 3.50, 5.50
   RBC    250, 80, 2, 9
   RBC    5.38, 3.50, 5.50
   RBC    250, 80, 2, 6
   RBC    3.59, 3.50, 5.50
   RBC    250, 80, 2, 7
   RBC    4.40, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    4.61, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    0.00, 3.50, 5.50
   RBC    250, 80, 2, 9
   RBC    4.23, 3.50, 5.50
   RBC    250, 80, 2, 10
   RBC    4.85, 3.50, 5.50
   RBC    250, 80, 2, 10

3. Need further clarification.

[adamdaban]

dear I want result like this
ID SNO RBC
---- ---- -----
value value value


not like
Test Result
---- -------
ID value
SNO value
RBC value

--------
if you can do it these 3 I will check your code and I will complete rest of them

[ADezii]

Kindly do not refer to me as dear, it is not appreciated, and besides I am a male. That being said, it appears as though you want the Data Transposed from its current format. Will you always be filtering for SNO and RBC?

[adamdaban]

Sorry for I using this word! Actually, I need to get some of them but I don't want to make you tired when I get Idea from you I will do it another part
ID Date WBC LYM MID GRAN LYM MID GRA RBC HGB
--- --- --- --- --- ---- --- --- -- -- ---

[ADezii]

Finally, I think I have a clearer understanding of what you are looking for. Unfortunately, this makes a solution more complicated since the Date component now needs to be extracted and the Unique Test Names now have to be Columns (Fields) in a Table. I need to regroup and figure out how to deal with this, may be a little while.

[ADezii]

1. Sorry, but I cannot proceed any further unless I know the exact meaning/significance of the following Tag Pairs:
   

   <sample></sample>
   <instrinfo></instrinfo>
   <smpinfo></smpinfo>
   <smpresults></smpresults>
   <tparams></tparams>
   <hgrams></hgrams>
   <hgram></hgram>
   <hgdata></hgdata>

2. I also need to know how the Date/Time Stamps factors into the Data. It appears that all Tests within the <sample></sample> Tags apply to the Date within these Tags also, but I am not sure.
   

   <n>DATE</n><v>2017-08-08T08:32:43</v></p>

3. Without this specific information, I'm afraid that I am dead in the water!

P.S. - You need to speak to the individual(s) who are responsible for generating the Text File.

[adamdaban]

yes ADezii I want to like this if you check my picture
about Date is not important because I use the default date=date()

yes that's I want it's true it's complicated but I believe you can figure out. Thanks, I really make you so tired .

[NeoPa]

Hi ADezii.

Please check your PMs for the solution to your problem. Actually, I only identify the problem but it's well within your capabilities to find a solution. I wouldn't insult you by trying to offer one.

All the best -Ade.

[ADezii]

Thanks NeoPa, still learning from you.

[ADezii]

For the life of me I cannot see how the Data depicted in the Graphic is derived from the Text File. The Graphic is very hazy but the Column Headers appear to be Chemical Symbols supposedly representing the presence of these Elements in the Blood Sample. I see no listing of these Elements within the Text File and, to be perfectly honest, I am lost at this point.

[adamdaban]

always this blood test machine give us this result, this machine name is Medonic

[adamdaban]

Ade, I don't know what I should do? Also, i need it, if you have any advice, please let me know

[NeoPa]

Adam.

It seems clear you don't have the necessary skills to handle a project like this at this stage. Earlier advice for you (See posts #7 & #8.) indicated you needed better understanding and that a tutorial or two might help. That is still an option, but frankly you communication skills fall too far short to allow you to benefit as much as others from this site. I suspect that's largely due to a language barrier, but it's also, at least partly, due to your failure to respond to all that's written for you. Questions put to you go without an answer. Possibly due to the fact you don't understand the question, but nevertheless a response of some kind is always required.

I suspect my most helpful advice at this stage is to get someone in with experience who can help. Someone that has the whole story that you've struggled to impart clearly here. Employers often misjudge the skill and experience levels required for such projects. It seems you've been dropped in to one that's a bit above your level. If you do manage to convince your boss to get someone in then they may well wish to start with the work that ADezii has already prepared for you. I suspect ADezii has now given up on trying to extract the meaningful information he needs in order to be able to continue to help you.

Best of luck.

PS. Please don't think I'm trying to be rude when I talk about your abilities. We all start somewhere and they are important considerations when trying to answer your question as where you can go next. This may be a learning experience for you - if you want it to be and you have the drive to make it so.

[adamdaban]

I don't understand why you are thinking like this way and judged me! This website for exchange idea and if you don't like I am there just talk with admin and withdraw me!

[Rabbit]

Adam, have you done a class or tutorial on Access VBA yet?

[NeoPa]

AdamDaban:
I don't understand why you are thinking like this way and judged me!

It's quite simple really. I'm not judging you or condemning you. You've asked me for advice and I feel bound to give the most helpful advice I can. Clearly I have to take into consideration whom it is that I'm giving advice to. If I were to give the same advice to you as I would to someone else who is experienced in Access then that would be inappropriate. You would have been unable to understand it or make any use of it.

Of course, I've used your comments and your responses to questions and other posts to allow me to respond in the most helpful manner I can. Bear in mind my comment was not offered out of the blue. It was posted only in response to your request. If it had been my intention to insult you then there was far more scope available (that I did not use). I used only what was needed in order to answer your question.

As with every piece of advice I offer, the person to whom it is directed, you in this case, is always perfectly at liberty to choose how much, if any, they follow that advice.

AdamDaban:
This website for exchange idea and if you don't like I am there just talk with admin and withdraw me!

Technically this site is for Developers and IT professionals. That said we have a large number of amateurs come to ask for help and technical advice. As a general rule you'll find we're happy to work with anyone who wishes to ask a question.

For the record, I am an admin. Not that I would ban you even if I did have a problem with you being here (which clearly I don't). We do ban members from time to time, quite rarely, but only when they continually break our rules (or spammers who go straight away). The worst that could be said about you is that you don't make it at all easy for people to help you. So, I have no problem with you or your presence here. Apart from some frustration trying to assist you, nor does anyone else.

Let me just say, in case it's relevant, that most of us here appreciate that people who generally use languages other than English have a much harder time trying to communicate their questions. We are an exclusively English Language forum. While we understand that, and make allowances where possible, we can only work with what we understand.

[adamdaban]

I trying to learn more I'm not so good at VBA

[adamdaban]

Thanks for every things

[Rabbit]

I trying to learn more I'm not so good at VBA

The first thing you'll want to start with is to learn the basics. Without it, you won't be able to implement any of the solutions we provide you with.

So start with a tutorial, something like this one: http://www.functionx.com/vbaccess/index.htm.

There is a list of lessons on the left, start with lesson 1 and work your way through them to get the basics. Normally, I would say you can stop at lesson 52. But for the purposes of your question, you should do all 65 because the ADO and XML lessons will be helpful in solving your original question.

Once you have a foundation, then you will be able to understand and implement the code that ADezii has provided.

[adamdaban]

amazing resource thanks

[NeoPa]

Hi Adam.

I understand that learning a new skill can be slow and laborious. I recommend trying out some tutorials before going for the more complicated stuff. Play with the tutorials and see if you feel this is something you can do, and would like to do. It's always hard to jump right in on a proper and difficult project.

Give yourself time to get up to speed. Enjoy the feeling of creating something and coming up with new ideas to make something happen the way you want it to. It can be real fun if you're suited to it.

Why not see what you can get from some of these tutorial links :

1. Microsoft Access Tutorials (Strive4Peace).
2. Microsoft Access Tutorials
3. Microsoft Office Tutorials.
4. VBA Tutorial (Excel).

They may not all suit you but I expect you'll find some of them very helpful.