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]

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

No, I am sorry, but I did not recieve your email (at least I do not seem to have any unattended to emails in my inbox). Please send it to me again (and remember to mention the name of this thread in your message so I know what it is for).

 

[bclor3591]

Hello Rick,

Just checking in to see if you got the email with code and email samples? I sent it from my GMAIL account as that doesn't have any security checks on it like our corporate email accounts does.
Bob Loring bclor3591

 

[Guy A R]

Hi Rick,

Thanks for this code! I've been trying to work it out how to adapt it to my needs all morning however I am pretty much completely new to VBA so I'm struggling! Basically what I'm trying to do is:
Search through a .log file (that's opened in notepad). I have x- and y- co-ordinate data stored in the file that has been dumped from a piece of equipment. The data format is like the following example extract:

"...Device_1\1114124_1_15000um_3150um_20120905...", where "15000um" would be the x-coordinate and "3150um" would be the y-coordinate for a specific 2D location.

The data can be found in the format "x-coordinate info, y-coordinate info, (next line) x-coordinate info, y-coordinate info (next line)...." (and so on) throughout the text file. The coordinates are always split apart using a "_" character however they are described sometimes by using a 5-digit coordinate and sometimes with a 4-digit.


A final twist to my puzzle is that there are many devices being tested in the same .log file and I'd like to have them specified in the same sheet to their x- and y- coordinates. A device name can be found in the example extract as "device_1", and usually take on the name "Device_X" where X is a certain integer. The device name is found in each line.

There are many lines of x- and y- data for each device, where ideally what I'd like to do is to have all the respective x- data lined up in a single row from start to finish for each device, and then all the y- data lined up in the row beneath it.

Thanks in advance if you can help!

 

[Rick Rothstein]

Hi Rick,

Thanks for this code! I've been trying to work it out how to adapt it to my needs all morning however I am pretty much completely new to VBA so I'm struggling! Basically what I'm trying to do is:
Search through a .log file (that's opened in notepad). I have x- and y- co-ordinate data stored in the file that has been dumped from a piece of equipment. The data format is like the following example extract:

"...Device_1\1114124_1_15000um_3150um_20120905...", where "15000um" would be the x-coordinate and "3150um" would be the y-coordinate for a specific 2D location.

The data can be found in the format "x-coordinate info, y-coordinate info, (next line) x-coordinate info, y-coordinate info (next line)...." (and so on) throughout the text file. The coordinates are always split apart using a "_" character however they are described sometimes by using a 5-digit coordinate and sometimes with a 4-digit.


A final twist to my puzzle is that there are many devices being tested in the same .log file and I'd like to have them specified in the same sheet to their x- and y- coordinates. A device name can be found in the example extract as "device_1", and usually take on the name "Device_X" where X is a certain integer. The device name is found in each line.

There are many lines of x- and y- data for each device, where ideally what I'd like to do is to have all the respective x- data lined up in a single row from start to finish for each device, and then all the y- data lined up in the row beneath it.

Thanks in advance if you can help!

Can you send me a copy of one of your .log files so I can see what each line actually looks like (those ... hide too much needed information) plus what header information I need to skip over (if any)? My email address is...

rick DOT news AT verizon DOT net

Please put the title of this thread in your message so I can relate it back to here.