VBA, MS Access - Parse Text file and input to MS Access Table

Good Afternoon Professionals,

I have a notepad text file that needs to be parsed and then various fields to be input into an access table.  I've been able to code the steps of gathering the 'Source Code' of the web site in question and saving it in a folder location as a text file.  However, I'm having issues, breaking down the various fields within the text file to import into MS Access.  A sample text file is attached to this question.  The number of lines for each text file will vary.  The table that is going to be used to house the input-ed data has not been created yet, so there is creative latitude as I can use a query to "splice" the new data with other database data objects.  One last issue is how it is that the "word wrap" feature in notepad can be turned off to assure proper line data placement, if that matters.
Thank you in advance.
File Attached
TestParsing---Copy.txt
MyroccoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
What would you want the resulting table to be like?
0
Dale FyeOwner, Developing Solutions LLCCommented:
start out by looking at the Input # or Line Input # statements in Access help.  These will provide sample code for opening  a text file and reading data from that file.

Then you will want to look at the PlainText() function, which removes HTML and RichText markups from the text.
0
MyroccoAuthor Commented:
Good Afternoon and thank you for such quick responses:  Idealistically, the table would be structured like:
username entering data  --  Date of input  --  Time of input  --  Actual Comments  --  Email Template  --  Recipients
The DB is set up for DAO type input.
Thanks again!
I just escapes me on how to separate those fields in the text file and assure that notepad word wrap is turned off, or not, if that does not matter.
Thanks!
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MyroccoAuthor Commented:
Thanks Fyed, I'll look into those functions and see what I can muster.
0
MyroccoAuthor Commented:
Good Afternoon,

I think I was able to find something and change it around to do the job.  The question with this method would be this.  Is it possible to change the 'new field' criteria for subsequent fields?

CODE START:
Dim DB As DAO.Database, RS As DAO.Recordset, RS2 As DAO.Recordset
   Dim str1 As String, str2() As String, i As Integer
 
   DoCmd.SetWarnings False
   DoCmd.RunSQL "Delete * From tblA1"
   DoCmd.RunSQL "Delete * From tblA"
   Set DB = CurrentDb
   Set RS = DB.OpenRecordset("tblA1")
 
   Close #1
   Open "c:\here\TestParsing.txt" For Input As #1
   i = 0
   Do While Not EOF(1)
      Line Input #1, str1
      If i > 1 Then
         RS.AddNew
         RS(0) = str1
         RS.Update
      End If
      i = i + 1
   Loop
   Close #1
     
   RS.MoveFirst
   Set RS2 = DB.OpenRecordset("tblA")
   Do While Not RS.EOF
      str2 = Split(RS(0), "e>")
      RS2.AddNew
      For i = 0 To UBound(str2)
         RS2(i) = str2(i)
      Next
      RS2.Update
      RS.MoveNext
   Loop
   DoCmd.SetWarnings True

END CODE:
0
Dale FyeOwner, Developing Solutions LLCCommented:
I opened the text file and copied the first line of text.

I then set a variable in the immediate window = that line of text:

strText = "<DIV class=commentBlock><SPAN class=commentTitle>admin admin on 10/14/2010 10:58 AM </SPAN><SPAN>An email has been sent because the state of the task 'Deployment Plan' belonging to service 'Account - Request Elevated Privileges' has been changed to 'Completed'.<BR>Email Template: Service Completed Notification <BR>Email Recipient: kevin.l.Person@Company.com;kevin.l.Person@Company.com;System@Company.com<BR></SPAN></DIV>"

Then used PlainText to get:

?plaintext(strText)
admin admin on 10/14/2010 10:58 AMAn email has been sent because the state of the task 'Deployment Plan' belonging to service 'Account - Request Elevated Privileges' has been changed to 'Completed'.
Email Template: Service Completed Notification
Email Recipient: kevin.l.Person@Company.com;kevin.l.Person@Company.com;System@Company.com

To break this into three segments, you could use the Split() function, something like:

myArray() = Split(plaintexts(strText), chr$(10))

You will then need to use the Instr() function to search for the AM/PM indicator in the first element of the array to identify the breakpoint between the time and the comments.  

And then use instr with a ":" as the delimiter to extract the email template and recipients
0
MyroccoAuthor Commented:
Good Afternoon Fyed,

Umm, OK.  Before I try and tackle the problem from that direction: Question: How would one compensate for multiple records within the txt file?  And, based on your knowledge, is the code I put together viable, or do you think I should scrap that line of logic?

Thanks!
0
Dale FyeOwner, Developing Solutions LLCCommented:
I think your technique is sound, although I would not write the entire str1 into a single field in your table.

Rather than performing two looping operations, I would read the first row of text from the file, parse it using the Split() function and then extract the appropriate portions of that record into the appropriate fields of your table.  Then I'd read the next line of text , insert a new row, ...

You might also want to consider a separate table (recipients) and parse the multiple recipients into separate records in that table (rather then putting multiple recipients into a single text (or memo) field.
0
MyroccoAuthor Commented:
Good Afternoon Fyed,

I'm totally with you on the Multiple recipient issue.  I plan to eventually put that into a separate table then combine the records using an autonum via a form, subform.

In reference to using the Split() function.  How would I use that function and NOT HAVE each space between the words in the actual comments section become a separate field?

Thank you,
0
MyroccoAuthor Commented:
Good Afternoon Fyed,

OR, is it possible to just write a query that will do the parsing once the data is imported from the txt file?

Thank you,
0
Dale FyeOwner, Developing Solutions LLCCommented:
It's possible, but why add all that text to a single field, and then parse it into individual fields.

Some of the fields will be easier to do via a query than others, but the fact that you have to parse the data based on the line breaks (carraige returns) then extract only part of the text from each segment would make that query very complex.

0
MyroccoAuthor Commented:

Good Afternoon Fyed,

I guess I am just not understanding the appropriate usage (syntax) of the Split() function to break up each line in the txt file to create fields needed:

username entering data  --  Date of input  --  Time of input  --  Actual Comments  --  Email Template  --  Recipients

Thank you,
0
MyroccoAuthor Commented:
Good Afternoon,

Nobody has any idea's?
0
Dale FyeOwner, Developing Solutions LLCCommented:
Been really busy.  I'll try to take a look at this over lunch today.
0
MyroccoAuthor Commented:
Good Afternoon,

I understand.  I think I may have found a solution, to one piece of the problem.  The piece of replacing the invalid data.  I found and customized some code that completes a replace function to the text file.  The code is below.  I'm wondering if this code would hold water if a Do While loop could be incorporated to it:

CODE START:
Private Sub Command2_Click()
Dim fso As FileSystemObject
Dim fso_folder As Folder
Dim fso_file As File
Dim InStream As TextStream
Dim OutStream As TextStream
Dim InputData As String

'first
Set fso = New FileSystemObject

Set fso_folder = fso.GetFolder("C:\here")

For Each fso_file In fso_folder.Files

    Set InStream = fso.OpenTextFile(fso_file, ForReading)
    InputData = InStream.ReadAll
    InStream.Close
    Set OutStream = fso.OpenTextFile(fso_file, ForWriting, True)
    OutStream.Write Replace(InputData, "<BR></SPAN></DIV>", "")
    OutStream.Write Replace(InputData, "<H4>", "")
    OutStream.Close
Next fso_file

'second
Set fso = New FileSystemObject

Set fso_folder = fso.GetFolder("C:\here")

For Each fso_file In fso_folder.Files

    Set InStream = fso.OpenTextFile(fso_file, ForReading)
    InputData = InStream.ReadAll
    InStream.Close
    Set OutStream = fso.OpenTextFile(fso_file, ForWriting, True)
    OutStream.Write Replace(InputData, "<DIV class=commentBlock><SPAN class=commentTitle>", "User  ")
    OutStream.Write Replace(InputData, "</SPAN><SPAN>", "Comments  ")
    OutStream.Close
Next fso_file



Set InStream = Nothing
Set OutStream = Nothing
Set fso_folder = Nothing
Set fso_file = Nothing
Set fso = Nothing

CODE END:

Thank you,
0
Dale FyeOwner, Developing Solutions LLCCommented:
I would not try to replace HTML/Rich text tags using the Replace function.  I would use the PlainText function to remove those.

Dale
0
MyroccoAuthor Commented:
Good Afternoon,

I think it's quite obvious by now that I'm no VBA coding guru, Can you throw out a little bit more of a bone on that?

Thank you,
0
MyroccoAuthor Commented:
Good Afternoon,

This question is still open, any assistance would be greatly appreciated!
0
MyroccoAuthor Commented:
Good Afternoon,

Well a MAJOR DISAPPOINTMENT!! With the exception of Mr. FYED.  Mr. FYED Thank you for your sole input on this.  The early comments, put me in a general direction.  However, by and large I solved this one myself.  And, I would like to award you all the points based on your sole effort. Most disappointed in the lack of input from the remainder of the site.  Would have like to have seen more participation.
In any event, the problem has found resolution.  The code is in the code area.  Still a bit messy but most functional.  Again, the purpose of the code is to input source code data from a secure web site to a table using VBA via MS Access 2010.  Thank you Fyed for your participation, much gratitude. There are two modules that go with the code, if interested, I'll be happy to forward.
Thank you.
Private Sub cmdData_Click()
 
'*********************************
'Code created by Richard T. Txxxxxxxx
 
 
 
 
 
 
'Dim Initial Arrays
Dim PCText As String
Dim fName As String
Dim sdump As String
Dim ReqNum As String
 
'********************************************
'Main Looping process Initialization
Dim rs8 As dao.Database
Dim rs9 As dao.Recordset
Dim strsql9 As String
Dim i9 As Integer
 
'On Error GoTo errorhandler
 
strsql9 = "status2"
Set rs8 = CurrentDb
Set rs9 = rs8.OpenRecordset(strsql9, dbOpenDynaset)
 
 
 
 
With rs9
    Do Until .EOF
        ReqNum = !ReqId
 
MsgBox ReqNum
'********************************************
 
MsgBox "start of the CommentsImport_click event"
 
 
PCText = PageContents("http://wsso-support.web.boeing.com:2015/redirect.html?URL=https://myservices.web.boeing.com/RequestCenter/myservices/navigate.do?reqid=" & ReqNum & "&query=requisitionstatus&performerID=" & ReqNum)
sdump = BetweenHmm(PCText, "usercomments", "Add Comment")
 
MsgBox "just after the opening link line "
 
        'dump source code to text file and put request number in the begining of the text data
 
MsgBox "just before the decision tree"
 
    'If sdump <> "" Then
   
MsgBox "just after the decision tree <> line"
 
Dim txtFile As String
fName = "C:\here\TestParsingxxxx.txt"
Dim fHandle
fHandle = FreeFile
 
Open fName For Output As fHandle
Print #fHandle, sdump2 & sdump
Close fHandle
 
MsgBox "start the parsing part"
 
 
 
         ' parsing part
   
    Dim iFile As Integer
    Dim sBuffer As String
   
         ' Read the entire file
   
    iFile = FreeFile
    Open "C:\here\TestParsingxxxx.txt" For Input As iFile
    sBuffer = Input$(LOF(iFile), iFile)
    Close iFile
 
    MsgBox "Parsing Start of text file"
   
    sBuffer = Replace$(sBuffer, """", "")
    sBuffer = Replace(sBuffer, "<DIV style=HEIGHT: 150px class=commentContainer>", "")
    sBuffer = Replace(sBuffer, "id=usrCommBlock name=", "")
    sBuffer = Replace(sBuffer, "usrCommBlock>", "")
    sBuffer = Replace(sBuffer, ",</DIV></TD>", "")
    sBuffer = Replace(sBuffer, "<TD rowSpan=2><B>System History</B>", ",")
    sBuffer = Replace(sBuffer, ",</DIV></TD>", ",")
    sBuffer = Replace(sBuffer, "usercomments><B>User Comments</B>", ",")
    sBuffer = Replace(sBuffer, "<DIV style=HEIGHT: 150px class=commentContainer>", "")
    sBuffer = Replace(sBuffer, "id=usrCommBlock name=", ",")
    sBuffer = Replace(sBuffer, "</SPAN><SPAN", "")
    sBuffer = Replace(sBuffer, "<DIV class=commentBlock><SPAN class=commentTitle>", ",")
    sBuffer = Replace(sBuffer, "</SPAN><SPAN>", ",")
    sBuffer = Replace(sBuffer, "<BR></SPAN></DIV>", "")
    sBuffer = Replace(sBuffer, "</SPAN></DIV>", "")
    sBuffer = Replace(sBuffer, "<DIV style=HEIGHT: 280px class=commentContainer", "")
    sBuffer = Replace(sBuffer, "<TR>", "")
    sBuffer = Replace(sBuffer, "<TD>", "")
    sBuffer = Replace(sBuffer, "<DIV class=longHeader>", ",")
    sBuffer = Replace(sBuffer, "<DIV>", "")
    sBuffer = Replace(sBuffer, "<H4>", "")
    sBuffer = Replace(sBuffer, ">", "")
    sBuffer = Replace(sBuffer, "</DIV</TD</TR", "")
    'for field between
    sBuffer = Replace(sBuffer, " on ", ",")
   
      MsgBox "end the parsing part"
           
            
      MsgBox "Rewrite the file for import"
     
        ' Rewrite the file for import
   
    iFile = FreeFile
    Open "C:\here\TestParsingxxxx.txt" For Output As iFile
    Print #iFile, sBuffer,
    Close iFile
        
        ' Routine to copy TestParsingxxxx to C:\here\TestParsingAAAAAA.txt
       Dim Fs As New Scripting.filesystemobject
       Dim text As Scripting.textstream
       Dim s As String
            
       Set text = Fs.opentextfile("c:\here\TestParsingxxxx.txt")
       s = text.readall
       s = s + vbCrLf
       text.Close
       Set text = Fs.createtextfile("C:\here\TestParsing123456.txt")
       text.write s
       text.Close
   
        ' Import the TestParsingxxxx.txt file
          MsgBox " import to table part start"
    DoCmd.TransferText acImportDelim, ParsingImport, "TestParsing", "c:\here\TestParsingxxxx.txt"
 
  
MsgBox "start updateing requid field"
 
        ' update TestParsing table,
 
Dim dbs As Database
Dim rst As Recordset
Dim strTable As String
Dim intcount As Integer
Dim i As Integer
 
strTable = "TestParsing"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable, dbOpenTable)
intcount = rst.RecordCount - 0
 
With rst
    For i = 1 To intcount
        .Edit
        !ReqId = ReqNum
        .Update
        Debug.Print "changed " & !ReqId & " record"
        .MoveNext
        Next i
        End With
       
         ' Get rid of the null values in the TestParsing table using the above Dimmed values
         MsgBox "about tyo run delete null query"
        
         
CurrentDb.Execute "Delete * from TestParsing WHERE f2 is null"
 
       
         ' update/insert values to the comments table
 
MsgBox "about to run the update comments table query"
 
CurrentDb.Execute "insert into CommentsTbl (REQID, f2, f3, f4, f5, f6, f7) Select REQID, f2, f3, f4, f5, f6, f7 from TestParsing"
 
        ' Delete the contents of the TestParsing Table
 
CurrentDb.Execute "delete * from TestParsing"
 
        ' Delete the the TestParsingxxxx file
 
'Kill "c:\here\TestParsingxxxx.txt"
  
          
           MsgBox "queries are done"
          
 .MoveNext
   Loop
  End With
 
        'Else
            'MsgBox "Please try the operation again"
 
       
'End If
 
 
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MyroccoAuthor Commented:
Solution self created.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Sorry I couldn't be more help, and that no other experts chimed in.  Did you ever upgrade this to "Neglected Question" status?  If not, many of the experts will look to see if another expert is engaged and if so, bypass the question until someone changes the status by clicking the "Request Attention" hyperlink in the bottom right corner of the original post.

I still believe you could have eliminated many of the replace() function calls by using:

sBuffer = PlainText(sBuffer)

I would be interested in seeing your PageContents function.

I'm also a bit confused.  You closed the question with "Solution self created", but in your final post, you indicated "I would like to award you all the points".
0
MyroccoAuthor Commented:
Good Afternoon Fyed,

Yes, I'm quite "ignorant" as of the use of the "completion" phase of the site. Never had much success asking questions (no input), so I mainly just browse the known solutions and incorporate those into mine.  What do I need to do to get you those points?

Thank you.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Don't worry about the points.

I find that if most people would spend any time searching the solutions, as you describe above, they would find the answer to their question.  I think many just figure it is quicker to ask the question again than to do the research.

But if you post a question and don't get an answer that works.  Click the "Request attention" hyperlink and ask the moderators to expand the list of zones that the question is posted in, or mark the question as neglected, or both.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.