Extracting certain data from a text file to Excel using VBA or a Macro?

[bclor3591]

I am trying to extract certain data from a text file into an Excel spreadsheet. I'm using MS Office 2010 and Outlook. I saved the emails as TEXT formatted files and now I need to pull information from the text files. I was trying to use MID & FIND to get the information that I need for my report. Here is a list of the information I need to extract from the text file:
REPORT # (the number immediately after the REPORT #)
DATE (this is the reported date)
STORE# (Store xx)
ISSUE (ADDITIONAL COMMENTS)

I will cut/paste it in here:

This is an actual email that I receive on a daily basis: (the names were xxxx out to protect their identity!)




-----------------------------------------------------------------------------------------------------------------------


From: xxxxxxxxxx.net
Sent: Sunday, September 09, 2012 6:58 PM
To: Smith, John
Subject: Location xxxx - PROBLEM Report xxxxxx - Guest xxx xxx


Store xx
Guest Relations - Web Entry
INCIDENT REPORT




REPORT #: 7795242
--------------------


REPORTED: Sunday, Sep 09, 2012 05:39 PM EST
----------------------------------------------------


ATTENTION:
----------
xxx..


IMPORTANT INSTRUCTIONS:
--------- ------------


Store XX's`s standard is to respond back to the guest within 24-48 hours and close the report
within 3 days.


Guest Information:
--------------------------
xxxx xxxx
xx xxxxxx Rd.
xxxx, xxxxx 99999
Daytime Phone: xxx-xxx-xxxx
Evening Phone: Not Provided
Email: xxxx@gmail.com


CONTACT HISTORY:
*Complaints = Emergency + Problem Reports Prior Contacts Prior Complaints*
---------------------------------------------------- -------------- -----------------
Store xx: None None


Store Information
----------------------------------------------------
0320 - STORE Y - ONLINE ORDER
Store xx
xx xxxxxx blvd
xxxx, xxxxx 99999
STORE MGR: xxxx xxxx
Store xx CORPORATE (TRUE CORP)
Corporate-RVP-xxxx xxxx
REPORT DETAILS:
---------------
Occurred: Sunday, Sep 09, 2012 04:45 PM
----------------------------------------------------


* This report contains one or more chargeable categories.




* Problem - Service Related - Speed of Service








ADDITIONAL COMMENTS:
--------------------
*** Report details were entered by Guest *** I ordered subs online to be ready for 4:45pm
pickup I arrived to pick up and no one was available (i.e. "punched
in") that could process my order. I had to wait over 10 minutes just to pay for food that I
had ordered hours ago (BTW it was not "busy") Poor customer service & unacceptable.






QUESTIONS:
----------
xxxx,xxxxxx xxxxxxx xxxx xxxxxxx xxx xx xxxx xx xx x ?
How much do you normally spend each visit?
How many people were in your party?
Did you order a sub on this visit ? No




FULFILLMENT:
------------


CONTACT xxxxx xxxxx xx xx x xxxxxxxx xx x xxx xxx




-----------------------------------------------------------------------------------------------------------------------

 

[Rick Rothstein]

Something like this might work for you...

Sub GetEmailInfo()
  Dim X As Long, FilePathAndName As String, TotalFile As String, FileNum As Long, Arr() As String
  Dim ReportDate As String, ReportNumber As String, StoreNumber As String, Issue As String
    
  ' Get path and filename by whatever means you do now
  FilePathAndName = "c:\temp\Email Text.txt"
  
  ' Read entire file into TotalFile variable
  FileNum = FreeFile
  Open FilePathAndName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  ' Get Report Number
  Arr = Split(TotalFile, "REPORT #: ")
  ReportNumber = Val(Arr(1))
  
  ' Get Report Date
  Arr = Split(TotalFile, "REPORTED: ")
  ReportDate = Split(Arr(1), vbCrLf)(0)
  
  ' Get Store Number
  Arr = Split(TotalFile, vbCrLf & "Store ")
  StoreNumber = Val(Arr(1))
  
  ' Get Issue
  Arr = Split(TotalFile, "ADDITIONAL COMMENTS:")
  Arr = Split(Arr(1), vbCrLf, 3)
  Arr = Split(Arr(2), vbCrLf & vbCrLf)
  Issue = Arr(0)
  
  ' Let's see what we got
  MsgBox "Report #: " & ReportNumber & vbLf & vbLf & _
         "Report Date: " & ReportDate & vbLf & vbLf & _
         "Stort #: " & StoreNumber & vbLf & vbLf & _
         "Issue: " & Issue
End Sub

 

[bclor3591]

Thank you Rick for the quick response. That is just what I needed. The only thing I have to figure out now is how to do this directly from Outlook. Right now it works if I copy the email to a text file then using your code it extracts the information that I want. I'll keep plugging away it and I should be able to tweak your code so I can pull right from the Outlook Inbox.

 

[Rick Rothstein]

Thank you Rick for the quick response. That is just what I needed. The only thing I have to figure out now is how to do this directly from Outlook. Right now it works if I copy the email to a text file then using your code it extracts the information that I want. I'll keep plugging away it and I should be able to tweak your code so I can pull right from the Outlook Inbox.

You are quite welcome... unfortunately, I know absolutely nothing about Outlook, so I am unable to help you with that part of your project.

 

[Eisasuarez]

Hi Rick,

Firstly i would like to say you are awesome.

can you please explain or do have a link to describe and explain how to work with text files. I.e filenum, input, output, append, #, etc....
many thanks

 

[Rick Rothstein]

Firstly i would like to say you are awesome.

You are being much too kind, but thank you.

can you please explain or do have a link to describe and explain how to work with text files. I.e filenum, input, output, append, #, etc....
many thanks

I have been programming in BASIC (the predecessor to Visual Basic) and VB since 1981, so most of what I know has been acquired from years of programming experience; so I don't really have a good way of transmitting to others what has become automatic to me many years ago; however, a quick Google search turned up these two links which appear to be quite detailed, so maybe they will prove helpful to you...

File Handling In Visual Basic 6 Part 1 - Sequential Files - Visual Basic Tutorials | Dream.In.Code

File Handling In Visual Basic 6 Part 2 - Binary File Handling - Visual Basic Tutorials | Dream.In.Code

 

[Eisasuarez]

Thank You

 

[bclor3591]

Hello Rick,

I finally got my work plate cleared so I could try the code you suggested. I created a module in EXCEL and cut/pasted your code into that module. I added the file name location information and tried to run the macro, but I'm getting a "Run Time Error 9 Subscript out of range" error and I tried to debug the code to see where the problem was it is pointing to this statement:

Sub GetEmailInfo()
Dim x As Long, FilePathAndName As String, TotalFile As String, FileNum As Long, Arr() As String
Dim ReportDate As String, ReportNumber As String, GuestName As String, StoreNumber As String, Issue As String

' Get path and filename by whatever means you do now
FilePathAndName = "c:\Users\bloring\My Documents\#ServiceCheck\EMAILS.txt"

' Read entire file into TotalFile variable
FileNum = FreeFile
Open FilePathAndName For Binary As #FileNum
TotalFile = Space(LOF(FileNum))
Get #FileNum, , TotalFile
Close #FileNum
' Get Report Number
Arr = Split(TotalFile, "REPORT #: ")
ReportNumber = Val(Arr(1))

' Get Report Date
Arr = Split(TotalFile, "REPORTED: ")
ReportDate = Split(Arr(1), vbCrLf)(0)

' Get Guest Name
Arr = Split(TotalFile, vbCrLf & "Guest Information: ")
Arr = Split(Arr(1), vbCrLf, 1)
GuestName = Val(Arr(1))

' Get Store Number
Arr = Split(TotalFile, vbCrLf & "Store ")
StoreNumber = Val(Arr(1))

' Get Issue
Arr = Split(TotalFile, "ADDITIONAL COMMENTS:")
Arr = Split(Arr(1), vbCrLf, 3)
Arr = Split(Arr(2), vbCrLf & vbCrLf)
Issue = Arr(0)

' Let's see what we got
MsgBox "Report #: " & ReportNumber & vbLf & vbLf & _
"Report Date: " & ReportDate & vbLf & vbLf & _
"Guest Name: " & GuestName & vbLf & _
"Stort #: " & StoreNumber & vbLf & vbLf & _
"Issue: " & Issue
End Sub

I'm not sure where to go from here? I copied all the emails to a EMAIL.txt file and I referenced that correctly in the FilePathandName field, but I'm at a loss as to why is is blowing up. Any assistance you could give me would be greatly appreciated.
Bob Loring bclor3591

 

[Rick Rothstein]

Hello Rick,

I finally got my work plate cleared so I could try the code you suggested. I created a module in EXCEL and cut/pasted your code into that module. I added the file name location information and tried to run the macro, but I'm getting a "Run Time Error 9 Subscript out of range" error....

Can you send the text file you are reading in that this is happening to so I can test/debug it first-hand? If so, my email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbols they spell out).

 

[bclor3591]

Hello Rick,

I was just checking in to see if you 1) you got my email with the data I'm trying to work with and 2) if you have had any luck in solving this problem I'm having? Thanks and Happy Holidays!
Bob