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

What would you want the resulting table to be like?

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.

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!

Thanks Fyed, I'll look into those functions and see what I can muster.

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:

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

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!

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.

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,

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,

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.

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,

Good Afternoon,

Nobody has any idea's?

Been really busy.  I'll try to take a look at this over lunch today.

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,

I would not try to replace HTML/Rich text tags using the Replace function.  I would use the PlainText function to remove those.

Dale

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,

Good Afternoon,

This question is still open, any assistance would be greatly appreciated!

Solution self created.

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

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.

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.