How to parse data in text file

[sifar]

Hi,

I am trying to read data from a no of Text files (usually of same
format, i.e. 1 table of 2 columns.)into an excel sheet.

e.g. The Data in TEXT file looks like:

Fill Across dotted lines.....
Blank
Mail Subject: FlashLite Order
Blank
Blank
Agent Name: | Smokey Joe
Teams Name: | Mavericks
Date : | 07/10/2005
Time : | 10:00 PM GMT
Blank
Blank
Subscriber#:| FS2212WE
Work ID : | 1234

So, if you see, the Label & Value seperator is a ":" (except for Time).
Also, there are blanks in between the lines.


Now, i have a worksheet with Similar Labels (except Mail Subject which
will be the name of the worksheet to Activate)for Column Headers
starting from "A1".

What i am trying to do is to read each line into a variable (Except
First Line "Fill across dotted lines..."). Discard any Blank lines.
So variable will contain both Label & Value. Then Split & Trim this
variable into 2 parts at delimiter ":", & then get it into an Array.
So Array will contain Label first & then Value e.g. & will not contain
any Blank values.

MyArray(0)=Label1
MyArray(1)=Value1
MyArray(2)=Label2
MyArray(3)=Value2
etc....

Once Array is created, Activate the worksheet with name "FlashLite
Order" from MailSubject value in TEXT File, Compare Labels in Array
with Column Headers in Excel Sheet, & then dump corresponding values
for text file labels under the Excel Column Headers.

Please Help!

 

[Robert Mulroney]

This will open a text file and read the data. If you want to process multiple
files then have a look at my post in microsoft.public.excel.programming - RE:
Writing directory info to individual cells. Some combination of the two will
do this for you.


Public Sub outputTextFile()

Dim upto As Integer

'Create and object that can access the file system.
Set filesys = CreateObject("Scripting.FileSystemObject")
Set readfile = filesys.OpenTextFile("c:\1.TXT", 1, False)

upto = 0

'loop through each line
Do While Not readfile.AtEndOfStream
upto = upto + 1
'this call returns a string of the next line in the file.
'Do what you want with the info here.
Range("A" & upto) = readfile.Readline
Loop

End Sub



- Rm

 

[sifar]

hi Robert,

thanks for your reply.

This code does not split each line into 2 lines & nor inserts them into
an array.
It only gets the data into excel sheet under a column & that too
without comparing the column headers in excel with the labels in text
line (on left hand-side).

Rgds,

Sifar

 

[Robert Mulroney]

The Readline function returns a string. I've just output it to a worksheet
so you can see how to read info from a file. It should be a simple matter to
manipulate that string into the format that you want using the left, right,
and instr functions.

I could just as easily have done this but that wouldn't have displayed a
result

dim myarray(1 to 100) as string
dim working as string

Do While Not readfile.AtEndOfStream And upto < 100
upto = upto + 1

'I meant that you should manipulate you strings, skip blank lines,
'all that stuff right here. This isn't all of it but something like:

working = readfile.Readline
working = right(working, len(working) - instr(working, ":",
vbTextCompare))

myarray(upto) = working
Loop


I haven't written a complete working solution for you because it's very
difficult to do with seeing the file.

- Rm

 

[sifar]

Hi Robert,

I could send you a copy of the excel workbook what i am trying to
create, if you

provide me with your email address. Also, could send you the Mail
Message from

which i extract data & create the text file for Reading into Excel
Sheet.

Here's the code:
'====================================
Option Explicit


Sub ProcessAll()
Dim rngSubject
Dim retVal

Set rngSubject = shtSetup.Range("A14")
Do While rngSubject.Value <> ""
retVal = ProcessOutlookMessages(Trim(rngSubject.Value), _
Trim(rngSubject.Offset(0, 1).Value),
_
Trim(rngSubject.Offset(0, 2).Value))

If retVal >= 0 Then
rngSubject.Offset(0, 3).Value = rngSubject.Offset(0, 3).Value +
retVal

Else
'negative return value means error
MsgBox "Could not process mails"
Exit Sub
End If

Set rngSubject = rngSubject.Offset(1, 0)
Loop

End Sub


' Extract information from mail items with defined subject and
attachment
Function ProcessOutlookMessages(MailSubject As String, DataSheet As
String, _
ProcessedFolder As String)

Dim olApp As Outlook.Application
Dim fInbox As MAPIFolder
Dim olFolderArchive As Object
Dim olInboxCollection As Object
Dim olInboxItem As Object

Dim TempPath As String
Dim itemCount, i, n, iCount

'temp files saved here
TempPath = ThisWorkbook.Path & "\TempFile.txt"

'If this is the first time calling the sub - get a ref to Outlook

' (must be already running)
If olApp Is Nothing Then
Set olApp = GetOutlook()
End If

'should have it by now....
If olApp Is Nothing Then
ProcessOutlookMessages = -1
Exit Function
End If

'Get the inbox folder
Set fInbox =
olApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
Set olInboxCollection = fInbox.Items
itemCount = olInboxCollection.Count

iCount = 0
For n = itemCount To 1 Step -1
Set olInboxItem = olInboxCollection(n)
'check it's an email and not something else
If TypeName(olInboxItem) = "MailItem" Then

If StrComp(Trim(olInboxItem.Subject), MailSubject) = 0 Then

'Look for archive folder and create if doesn't exist.
Set olFolderArchive = EnsureInboxFolder(fInbox,
ProcessedFolder)

olInboxItem.SaveAs TempPath, olTXT
ProcessFile TempPath, DataSheet
'move the mail to the archive folder
olInboxItem.Move olFolderArchive
iCount = iCount + 1
End If
End If
Next n

ProcessOutlookMessages = iCount
Exit Function

haveError:
If Err <> 0 Then MsgBox "Error:" & vbCrLf & Err.Description

End Function


Sub ProcessFile(TempFilePath As String, WorkSheetName As String)
'#######################
'code goes here to process the temp file and extract the contents. NEED

CODE FOR THIS SUB...
'#######################
End Sub


Function GetOutlook() As Object
Dim olApp As Object
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
On Error GoTo 0
If olApp Is Nothing Then
MsgBox "Outlook is not running: please open the application first"

End If

Set GetOutlook = olApp
End Function

Function EnsureInboxFolder(oInbox, FolderName) As Object
Dim oFold As Object

'Look for archive folder and create if doesn't exist.
On Error Resume Next 'ignore error
Set oFold = oInbox.Folders(FolderName)
If Err.Number <> 0 Then Err.Clear

If oFold Is Nothing Then
Set oFold = oInbox.Folders.Add(FolderName, olFolderInbox)
End If

Set EnsureInboxFolder = oFold
End Function
'=================================

Also, Here's the Source code of the MS Outlook Mail Message in html.
Copy this

into Frontpage & then SaveAs Modem Order.Msg file.

'===================================

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>

<META content="MSHTML 6.00.2800.1515" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT face=Arial size=2>
<DIV class=style1 align=center><STRONG>
<DIV class=style1 align=center><FONT color=#ff0000>New Modem Escalation

Form</FONT></DIV>
<DIV class=style1 align=center>&nbsp;</DIV></STRONG><FONT face=Arial
size=2></FONT></DIV></FONT></DIV>
<DIV id=Layer1>
<TABLE width=476 align=center border=4>
<TBODY>
<TR>
<TD width=277>CSA NAME: </TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>James

Roy</SPAN></FONT></TD></TR>
<TR>
<TD>Team Leader's name:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>Cyrus

Broacha</SPAN></FONT></TD></TR>
<TR>
<TD>Date:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>29/09/2005</SPAN></FONT></TD></TR>
<TR>
<TD>Time:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>12:00
pm</SPAN></FONT></TD></TR>
<TR>
<TD>Customer Forename:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>raj</SPAN></FONT></TD></TR>
<TR>
<TD>Customer Surname:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>jutsi</SPAN></FONT></TD></TR>
<TR>
<TD>Customer Email Address:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005><A

href="mailto:[email protected]">[email protected]</A></SPAN></FONT></TD></TR>
<TR>
<TD>Address 1 (House Number/Name):</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>abc</SPAN></FONT></TD></TR>
<TR>
<TD>Address 2 (Road):</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>def</SPAN></FONT></TD></TR>
<TR>
<TD>Address 3 (Town/City):</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>jkl</SPAN></FONT></TD></TR>
<TR>
<TD>Address 4 (Country):</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>mno</SPAN></FONT></TD></TR>
<TR>
<TD>Postcode:</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>be 23
of1</SPAN></FONT></TD></TR>
<TR>
<TD>FSBB Number:</TD>
<TD width=150><FONT face=Arial size=2><SPAN


class=538401720-28092005>FSBB1234</SPAN></FONT></TD></TR></TBODY></TABLE></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV id=Layer2>
<TABLE height=51 cellSpacing=0 cellPadding=0>
<TBODY>
<TR>
<TD width=711 bgColor=#cccccc height=42>
<H3 align=center>The following part has to be pasted in VV as a
part of
the resolution before the modem escalation. Answer Applicable
questions in
Y/N Format. </H3>
<DIV>
<TABLE width=661 align=center border=4>
<TBODY>
<TR>
<TD width=645>Modem light status &amp; Error No :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>y</SPAN></FONT></TD></TR>
<TR>
<TD>Powercycled the modem :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>y</SPAN></FONT></TD></TR>
<TR>
<TD>Reinstalled the modem software :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>n</SPAN></FONT></TD></TR>
<TR>
<TD>Swapped the USB ports :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>y</SPAN></FONT></TD></TR>
<TR>
<TD>Tried other USB devices in the same port :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>y</SPAN></FONT></TD></TR>
<TR>
<TD>Unplugged all other USB devices and then solely plugged
the USB
modem :</TD>
<TD width=150><FONT face=Arial size=2><SPAN
class=538401720-28092005>y</SPAN></FONT></TD></TR>
<TR>
<TD>Made a manual connection and selected the USB modem (if
applicable) :</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>Checked for the BB setup (if applicable) :</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>Connected the modem to the master socket (if applicable)
:</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>Swapped the filters (if applicable)</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>n</FONT></SPAN></TD></TR>
<TR>
<TD>Escalated for a woosh, If yes, does it suggest a Faulty
Equipment Issue:</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>n</FONT></SPAN></TD></TR>
<TR>
<TD>Did a BT Escalation?, If yes, then does the Fault Report
Suggest
a Modem Fault:</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>n</FONT></SPAN></TD></TR>
<TR>
<TD>Checked for Power Management System: </TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>Checked Device Manager for USB chipset (IF status says
"VIA" or
"SYS", cmr needs to upgrade chipset)</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>Disabled Firewall :</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR>
<TR>
<TD>If all the above steps are satisfied only then a modem
replacement can be sent for. :</TD>
<TD width=150><SPAN class=538401720-28092005><FONT face=Arial

size=2>y</FONT></SPAN></TD></TR></TBODY></TABLE></DIV><SPAN

class=538401720-28092005><FONT face=Arial
size=2></FONT></SPAN></TD></TR></TBODY></TABLE></DIV><BR>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV></BODY></HTML>

'=========================================

Now, Here's the text file which gets generated!
'=========================================




'=========================================

Hope This Helps!

Rgds,

Sifar