Import text by keywords 2

[btj]

I am trying to create some code that will copy text based upon a starting and ending keyword.

For example, I would want the code to begin copying everything when it sees "Description" but stop when it sees "end."

The amount of data copied will be different for each document.

I am messing around with a complex If...Then statement but it hasn't worked yet.

I really appreciate any advice.

- Ben

 

[snoopy75]

Let's see...

Function CopyString(str As String, StartWord As String, EndWord As String) As String

Dim intStart As Integer
Dim intEnd As Integer

intStart = InStr(str, StartWord)
intEnd = InStr(str, EndWord)

If intStart <> 0 And intEnd > intStart Then
   CopyString = Mid(str, intStart, intEnd - intStart + Len(EndWord))
Else
   CopyString = &quot;&quot;
End If

End Function

I've tested it, and it seems to work fine for me. Try it and let me know if it works for you.

--Ryan

 

[btj]

Ryan,
I think this will work. The concept matches my question. I am trying to integrate your code with my code but it is not matching.

Here is what I am using:
Public Sub ImportPermits()

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblTestACL", dbOpenDynaset)
Dim strData As String

Close #1
Open "c:\projects\test\text.txt" For Input As #1 'data file for import

Do Until EOF(1)

Line Input #1, strData 'grab a line
'check for which
If Left(strData, 2) = "TC" Then
rs.AddNew
rs!tcID = Mid(strData, 1, 10)
ElseIf Left(strData, 22) = "Test Case Description:" Then
'rs.AddNew
rs!tcDesc = Trim(Mid(strData, 23))
ElseIf Left(strData, 11) = "Test Group:" Then
'rs.AddNew
rs!TestGroup = Trim(Mid(strData, 12))
ElseIf Left(strData, 19) = "Related Test Cases:" Then
'rs.AddNew
rs!RelatedTestCase = Trim(Mid(strData, 20))
ElseIf Left(strData, 15) = "Test Objective:" Then
'rs.AddNew
rs!TestObjective = Trim(Mid(strData, 16))
ElseIf Left(strData, 20) = "Completion Criteria:" Then
'rs.AddNew
rs!CompletionCriteria = Trim(Mid(strData, 21))
ElseIf Left(strData, 20) = "Product Requirement:" Then
'rs.AddNew
rs!ProductRequirement = Trim(Mid(strData, 21))
ElseIf Left(strData, 11) = "Description" Then
'rs.AddNew
rs!Procedure = Trim(Mid(strData, 12))
ElseIf Left(strData, 9) = "Pass/Fail" Then
'rs.AddNew
rs!tcStatus = Trim(Mid(strData, 10))
ElseIf Left(strData, 9) = "Comments:" Then
'rs.AddNew
rs!Comments = Trim(Mid(strData, 11))
ElseIf Left(strData, 15) = "Tracking Number:" Then
'rs.AddNew
rs!bugID = Trim(Mid(strData, 16))
rs.Update
End If

Loop

Close #1
'rs.Close
Set rs = Nothing
End Function

What I want your function to do is run at one of the If points (as example - the first IF when it references "TC&quot.
Does this make sense? If this is easy, I apologize. I was thrown into this project and am trying to learn as I go.

I would appreciate your thoughts and greatly appreciate your initial response.

Thanks.

Ben

 

[snoopy75]

Interesting. A few things:

1. With this big 'ol If statement, it looks as though you are unable to predict the format of your input file. Is this true? If, in fact, you know exactly how your input file is laid out, you can save yourself a lot of hassle, and get rid of those If statements.

2. Change your last statement, "End Function", to "End Sub" ;-)

3. My function was designed to grab a piece of string between (and including) two words. For example,

CopyString("The quick brown fox jumped over the lazy dog", "brown", "the")

would return

"brown fox jumped over the"

. It looks as though you simply want to grab the string after a certain keyword, in which case, your Mid statements should suffice. So why exactly doesn't the code you have posted work?

Perhaps it will help me understand what you're trying to accomplish if you post a segment of your test input file.

--Ryan

 

[btj]

The format of my file is relatively stable. It is in the format of a table built in word. So I want to key off of title headings (the words in "" in each IF statement). As you can see from the sample, only the headers are constant. That is why I tried to make the code be more intelligent in its reading of the input file.

Here is a sample of the text:

Test Case Description: Call phone A to phone B, phone call is denied.
Test Group: Access Lists Policy
Related Test Cases:
Test Objective: To prove the ability to deny a call from phones.

Completion Criteria: Phone A unsuccessfully calls phone B.
Product Requirement: 001
Step
Label
Description
Reference
Pass/Fail
Results

Within this file, I want to copy whatever comes between the headings. The reason I thought about using a "copy" function was that the IF statements I build were only returning the immediate string after the heading. I need a more robust function.

For example, I cannot currrently copy the text between "Step" and "Results" with my IF statements.

My IF statements do work for very formated data, but this is not always the case with the input files I have to work with.

Please let me know if this clarifies my position and my current challenge.

Again, I appreciate your continued assistance.

- Ben

 

[snoopy75]

Okay, that's a little trickier. If the desired text will always be between two certain words, and you don't mind concatenating that text into one string, then incorporating (somehow) the following code might work for you:

Dim boolCopy As Boolean
Dim strCopy As String
boolCopy = False
strCopy = ""

Do Until EOF(1)
   Line Input #1, strData
   If Left(strData, "Step") Then
      boolCopy = True
   End If
   If boolCopy Then
      strCopy = strCopy & strData & " "
   End If
   If Left(strData, "Results") Then
      boolCopy = False
   End If
Loop

In a nutshell, this code watches for the keyword "Step", and begins copying everything after (and including) that word, no matter how many lines the data spans. It stops copying when it finds the keyword "Results". All the data from the multiple lines is contained in the one string strCopy, separated by spaces.

Is this kind of what you wanted to do?

--Ryan

 

[btj]

OK, so I tried integrating the code and it works great when I use it to only retrieve the data one time. When I try to create a more complex statement (i.e. for each of my previous IF statements) this is what I get:

tcDesc2
memTotal Test Objective: TC-ES0002 memUsed
TestObjective
memTotal Test Objective: TC-EC memUsed Test Objective:

I tried retrieving data for two fields - tcDesc2 and TestObjective. The entry of "memTotal" is correct for tcDesc2 but the rest is not accurate. TestObjective, as you can see, didn't retreive any correct information, but just a repeate of the first search. The entries of "memTotal" and "memUsed" indicate that the program is searching the two sample records and combining their information for tcDesc2 into one entry.

Here is the code I am using now:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTestEC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
Dim strData As String

Dim boolCopy As Boolean
Dim strCopy As String
boolCopy = False
strCopy = ""

Close #1
Open "c:\projects\test\textEC.txt" For Input As #1 'data file for import

Do Until EOF(1)

Line Input #1, strData 'grab a line
'check for which
If Left(strData, 5) = "TC-ES" Then
boolCopy = True
rs.AddNew
If boolCopy Then
strCopy = strCopy & strData & " "
rs!tcDesc2 = Trim(Mid(strCopy, 10))
If Left(strData, 16) = "Test Case Number" Then
boolCopy = False
End If
End If
End If

If Left(strData, 14) = "Test Objective" Then
boolCopy = True
'rs.AddNew
If boolCopy Then
strCopy = strCopy & strData & " "
rs!TestObjective = Trim(Mid(strCopy, 16))
rs.Update
If Left(strData, 17) = "Related Test Case" Then
boolCopy = False
End If
End If
End If

Loop

I am trying to nest the multiple IF statements so that I can get it to work , but that isn't helping. Additionally, in my original code (sent earlier), the majority of the commands retrieved the correct information.

What you sent me is definitely on the right track. My inexperience may again be hindering my progress, but I appreciate your knowledge.

Let me know what you think.

-Ben

 

[snoopy75]

Okay. So you want to do this thing for more than one of your If statements? Then you need to declare a different string variable and boolean variable for each one, so that they don't all run together like that.

Also, you don't need to use both the Trim/Mid statement and the boolCopy method in each If statement. If you know that the data will only be on the one line, use the Trim/Mid method that you had before. Do the multi-line copy only when you think the data could span multiple lines.

You're getting there. Keep working at it.

--Ryan

 

[btj]

So...I use my old IF statement and am successful in pulling in one line strings of data. I can use multiple of those IF statements to retrieve data.

When I incorporate the boolCopy method, it continues to work but creates issues. What the method seems to do is copy everything from my keyword ("Technique:&quot to the end of the txt file.

As my code creates a new record for each entry in the txt file, the first record copies everything from "Technique" to the end of the first data entry and the second record copies everything from the first "Technique" to the end of the second data entry.

Where did I stray in my logic? I tried declaring multiple string and boolean variables, but something isn't clicking.

Here is what I am using:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTestEC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
Dim strData As String

Dim boolCopy1 As Boolean
Dim boolCopy2 As Boolean
Dim strCopy1 As String
Dim strCopy2 As String

boolCopy1 = False
boolCopy2 = False
strCopy1 = ""
strCopy2 = ""

Close #1
Open "c:\projects\test\textEC.txt" For Input As #1 'data file for import

Do Until EOF(1)

Line Input #1, strData 'grab a line
'check for which
If Left(strData, 16) = "Test Case Number" Then
rs.AddNew
rs!tcID = Trim(Mid(strData, 18))
End If
If Left(strData, 10) = "Max Access" Then
'rs.AddNew
rs!MaxAccess = Trim(Mid(strData, 13))
End If
If Left(strData, 10) = "Technique:" Then
boolCopy1 = True
'rs.AddNew
End If
If boolCopy1 Then
strCopy1 = strCopy1 & strData & " "
rs!ProductRequirement = strCopy1
rs.Update
End If
If Left(strData, 20) = "Completion Criteria" Then
boolCopy1 = False
End If

Loop

Close #1
'rs.Close
Set rs = Nothing
End Sub

Hope something jumps out at you...I am lost. Thanks for the continued encouragement. I have made so much progress in the last day or so. I cannot thank you enough!

- Ben

 

[GComyn]

Here is what I use to import Multiple line information into one record with a memo field.

It works every time. But every file is a different record. If you have several records in one file, you will need to modify it slightly.

I use Access 97, with DAO.

Option Compare Database
Option Explicit
Const strReportsPath As String = "c:\Temp\"
Const TABLENAME As String = "tblReports-Temp"
Const DblQuote As String = """"

Private Sub ImportReports()
    Dim db As DAO.database
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim intFileNumber As Integer
    Dim strFileName As String
    Dim strLine As String
    Dim strMemoLine As String
    Dim strPathName As String
    Dim datDate As Date
    Dim strSourceFile As String
    Dim intCounter As Integer
    Dim strTemp As String
    Dim strCLTR As String
    Dim strDebtorName As String
    Dim varFound As Variant
    Dim bolImport As Boolean
    Dim intFileCount As Integer

    DoCmd.SetWarnings False
    Close  'Make sure there are no open files....
    
    Set db = CurrentDb                      '
    Set rs = db.OpenRecordset(TABLENAME)
    intFileCount = 0
    'Get the first filename
    strFileName = Dir(strReportsPath & "*.*")
    Do While strFileName <> &quot;&quot;
        intFileCount = intFileCount + 1
        Close  'Make sure there are no open files....
    'Initialize the variables for each file
        strSourceFile = strReportsPath & strFileName
        intCounter = 0
        strMemoLine = &quot;&quot;
        strCLTR = &quot; &quot;
        strDebtorName = &quot; &quot;
        intFileNumber = FreeFile
    'Open the file for input
        Open strSourceFile For Input As #intFileNumber
        datDate = CDate(Format(FileDateTime(strSourceFile), &quot;mm/dd/yyyy&quot;))
        Do While Not EOF(intFileNumber)
            Line Input #intFileNumber, strLine
            intCounter = intCounter + 1
            If intCounter = 1 Then
                'Check to see if the file has already been imported.
                varFound = DLookup(&quot;[strfile]&quot;, TABLENAME, &quot;[strfile]=&quot; & _
                            DblQuote & strFileName & DblQuote)
                If Not IsNull(varFound) Then
                    bolImport = False
                    Exit Do
                End If
                varFound = DLookup(&quot;[strfile]&quot;, &quot;tblreports&quot;, &quot;[strfile]=&quot; & _
                            DblQuote & strFileName & DblQuote)
                If Not IsNull(varFound) Then
                    bolImport = False
                    Exit Do
                End If
                varFound = DLookup(&quot;[strfile]&quot;, &quot;tblReports-Older&quot;, &quot;[strfile]=&quot; & _
                            DblQuote & strFileName & DblQuote)
                If Not IsNull(varFound) Then
                    bolImport = False
                    Exit Do
                End If
                strTemp = Trim(strLine)
                'Check to see if the debtor has been imported before from a different file
                If strTemp <> &quot;000000000&quot; Then
                    Set rs2 = db.OpenRecordset(&quot;SELECT CLTR, DBTRNM FROM &quot; & _
                                &quot;[tblImportedCreditBureauInquiries-Archive] where [ssno]=&quot; & _
                                DblQuote & strTemp & DblQuote)
                    If Not rs2.EOF Then
                        strCLTR = rs2(&quot;cltr&quot;)
                        strDebtorName = rs2(&quot;dbtrnm&quot;)
                        bolImport = True
                    Else
                        bolImport = False
                    End If
                    rs2.close: Set rs2 = Nothing
                    varFound = DLookup(&quot;[strdebtorname]&quot;, &quot;tblreports&quot;, &quot;[strdebtorname]=&quot; & _
                                DblQuote & strDebtorName & DblQuote)
                    If Not IsNull(varFound) Then
                        bolImport = False
                        Exit Do
                    End If
                    varFound = DLookup(&quot;[strdebtorname]&quot;, &quot;tblReports-Older&quot;, &quot;[strdebtorname]=&quot; & _
                                DblQuote & strDebtorName & DblQuote)
                    If Not IsNull(varFound) Then
                        bolImport = False
                        Exit Do
                    End If
                    varFound = DLookup(&quot;[strdebtorname]&quot;, TABLENAME, &quot;[strdebtorname]=&quot; & DblQuote & _
                                strDebtorName & DblQuote)
                    If Not IsNull(varFound) Then
                        bolImport = False
                        Exit Do
                    End If
                Else
                    bolImport = True
'                    Stop
                End If
            End If
            If Len(strLine) = 0 Then strLine = &quot; &quot;
            If Len(strMemoLine) > 0 Then
                strMemoLine = strMemoLine & vbCrLf & strLine
            Else
                strMemoLine = strLine
            End If
        Loop
        If bolImport = True Then
            With rs
                .AddNew
                .Fields(&quot;cltr&quot;) = strCLTR
                .Fields(&quot;strDebtorName&quot;) = strDebtorName
                .Fields(&quot;strFile&quot;) = strFileName
                .Fields(&quot;datDate&quot;) = datDate
                .Fields(&quot;memReport&quot;) = strMemoLine
                .Update
            End With
        End If
        Close
        strFileName = Dir
    Loop
    rs.close: Set rs = Nothing
    db.close: Set db = Nothing
End Sub

GComyn

p.s. If anyone can see an improvement to this code, let me know...

 

[snoopy75]

Ben, try this. Change this line

If Left(strData, 20) = "Completion Criteria" Then

to

If Left(strData, 20) = "Completion Criteria:" Then

It looks like that missing colon is making your code not recognize the "keyword" that ends copying, so you're getting the whole text file.

--Ryan

 

[btj]

That worked...it is always that one little thing.

For multiple records, the first record is fine but the records after that record what is requested plus the previous information.

For example:
Record 1
Completion Criteria
this is for record 1

Record 2
Completion Criteria
this is for record 1 this is for record 2

Does this problem make sense? What do you think?

-Ben

 

[GComyn]

at just a glance, it looks like you are not re-initializing the variable that holds that information. When you get the 'Completion Criteria', just initialize the variable you use to hold 'this is for record 1'.

GComyn

 

[btj]

GComyn -
Thanks for the input. Unfortunately, I am novice enough that I don't understand what you are saying completely.

How would you recommend initializing the variable that I use to hold the information for record 1?

-Ben

 

[GComyn]

Ok.. first.. which variable holds the string 'This is for record #'? and are you using the last function that you sent above?

GComyn

 

[btj]

If I understand you...

which variable holds the string 'This is for record #'?
I am not using a variable that specifies that the string is for record "x." Instead, my code adds a new line intially, runs through the document. After that, it loops again and creates a new record upon repeat.

Is that clear? Here is the code I used to use:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTestEC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
Dim strData As String

Close #1
Open "c:\projects\test\textEC.txt" For Input As #1 'data file for import

Do Until EOF(1)

Line Input #1, strData 'grab a line
'check for which
If Left(strData, 2) = "TC" Then
rs.AddNew
rs!tcDesc = Mid(strData, 10)
End If
If Left(strData, 16) = "Test Case Number" Then
'rs.AddNew
rs!tcID = Trim(Mid(strData, 17))
End If
If Left(strData, 18) = "Requirement Number" Then
'rs.AddNew
rs!ProductRequirement = Trim(Mid(strData, 20))
End If
If Left(strData, 10) = "Max Access" Then
'rs.AddNew
rs!MaxAccess = Trim(Mid(strData, 11))
End If
If Left(strData, 6) = "Syntax" Then
'rs.AddNew
rs!Syntax = Trim(Mid(strData, 7))
End If
If Left(strData, 15) = "Test Objective:" Then
'rs.AddNew
rs!TestObjective = Trim(Mid(strData, 16))
End If
If Left(strData, 17) = "Related Test Case" Then
'rs.AddNew
rs!RelatedTestCase = Trim(Mid(strData, 19))
End If
If Left(strData, 9) = "Technique" Then
'rs.AddNew
rs!Procedure = Trim(Mid(strData, 10))
End If
If Left(strData, 19) = "Completion Criteria" Then
'rs.AddNew
rs!CompletionCriteria = Trim(Mid(strData, 21))
End If
If Left(strData, 9) = "Pass/Fail" Then
'rs.AddNew
rs!tcStatus = Trim(Mid(strData, 10))
End If
If Left(strData, 10) = "Bug Number" Then
'rs.AddNew
rs!bugID = Trim(Mid(strData, 11))
End If
If Left(strData, 5) = "Notes" Then
'rs.AddNew
rs!Comments = Trim(Mid(strData, 6))
rs.Update
End If

Loop

Close #1
'rs.Close
Set rs = Nothing
End Function

Here is the code I am using currently:
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "tblTestEC", CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable
Dim strData As String

Dim boolCopy1 As Boolean
Dim boolCopy2 As Boolean
Dim strCopy1 As String
Dim strCopy2 As String

boolCopy1 = False
boolCopy2 = False
strCopy1 = ""
strCopy2 = ""

Close #1
Open "c:\projects\test\textEC.txt" For Input As #1 'data file for import

Do Until EOF(1)

Line Input #1, strData 'grab a line
'check for which
If Left(strData, 16) = "Test Case Number" Then
rs.AddNew
rs!tcID = Trim(Mid(strData, 18))
End If

If Left(strData, 10) = "Max Access" Then
'rs.AddNew
rs!MaxAccess = Trim(Mid(strData, 13))
End If

If Left(strData, 10) = "Technique:" Then
boolCopy1 = True
'rs.AddNew
End If
If boolCopy1 Then
strCopy1 = strCopy1 & strData & " "
rs!ProductRequirement = strCopy1
'rs.Update
End If
If Left(strData, 20) = "Completion Criteria:" Then
boolCopy1 = False
End If

If Left(strData, 15) = "Test Objective:" Then
boolCopy2 = True
'rs.AddNew
End If
If boolCopy2 Then
strCopy2 = strCopy2 & strData & " "
rs!TestObjective = strCopy2
rs.Update
End If
If Left(strData, 17) = "Related Test Case" Then
boolCopy2 = False
End If

'still duplicating data in records past entry 1

Loop

Close #1
'rs.Close
Set rs = Nothing
End Sub

I hope this clarifies my problem. Please let me know if you need anything else.

Thanks so much for your help and advice. As you can see, it is both much needed and appreciated.

- Ben

 

[GComyn]

I still don't see where the string 'this is for record 1' comes from... is it in the file?

What I would do, as I posted above, would to create a variable for every field in the recordset, set the variables until you are read to add a new record, then add the record with rs.addnew, then reinitialize all of the variables (i.e., set them to 0 or " " [I wouldn't use "", because, unless you have the table set to allow zero length strings, this will crash] before you go through the loop again.

GComyn

 

[btj]

No, the string 'this is for record 1' is not in the file. The file is just a .txt file - it will have multiple records per each file.

Example: 2 records, one txt file

TC-ES0001      memTotal


Test Case Number TC-ES0001
Requirement Number
E7
Max Access: read-only
Syntax
Integer32
Test Objective:
To verify that the memTotal object is a valid SNMP request
To verify that the proper results are returned upon issuing the memTotal request

Related Test Case:


Technique: From the product...
Initiate a session to the product using the string. Then issue get memTotal and verify the proper value is returned for the total memory on the product.
Initiate a session to the productusing the R/W community string. Then issue set memTotal and verify the product rejects the request as invalid.

Completion Criteria: Verify that the memTotal object performs as defined by the MIB.
Verify that a proper error message is displayed upon an invalid request using the memTotal object.

Pass/Fail:

Bug Number:

Notes:





TC-ES0002      memUsed


Test Case Number TC-ES0002
Requirement Number
E7
Max Access: read-only
Syntax
Integer32
Test Objective:
To verify that the memUsed object is a valid SNMP request
To verify that the proper results are returned upon issuing the memUsed request

Related Test Case:


Technique: From the product
Initiate a session to the product using the string. Then issue get memUsed and verify the proper value is returned for the amount of memory being used on the product.

Completion Criteria: Verify that the memUsed object performs as defined by the document.
Verify that a proper error message is displayed upon an invalid request using the memUsed object.

Pass/Fail:

Bug Number:

Notes:

The table I am importing to has all of its fields set to memo length with zero length strings enable to ensure that it doesn't crash.

Do you have any idea why my old code (using the complex IF statements) works and this new doesn't. Is there something I am missing. Sometimes another set of eyes is all that is needed.

I appreciate your continued help.

- Ben

 

[GComyn]

Try putting the following lines after the rs.update line:

 boolCopy1 = False
 boolCopy2 = False
 strCopy1 = ""
 strCopy2 = ""

That will reinitialize (empty) the variables, so you will have a clean slate for the next set...

Gcomyn

 

[btj]

Final question (I think)!

Adding the lines, as recommended by GComyn, worked. Now, I am trying to some touch up work.

The boolCopy method copies both the start and end words. Does anyone know how to trim the end of the string that is returned? I am using Trim(Mid(strData,[x])) to trim the start word, but can't figure how to remove the end of the phrase.

For example, after running the function using Technique (start) and Completion (end), this is my result:

Technique To verify that the memTotal object is a valid SNMP request To verify that the proper results are returned upon issuing the memTotal request Completion

Any thoughts...

- Ben