If you record a macro when you open your .txt file, you can step through the
wizard. But it sounds like you could use | as the delimiter--since you're going
to be tossing the records that begin with +.

I'd bring my data in the first field (column A) as text so that the the records
that start with + will be treated like text--not formulas.

Then after I recorded my import the data macro, I'd add a little bit of code
that cleans up the headers.



Option Explicit
Sub CleanPlusses()
Dim FoundCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet
With wks
With .Range("a:a")
Do
Set FoundCell = .Cells.Find(what:="+*", _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
If FoundCell Is Nothing Then
Exit Do
Else
FoundCell.EntireRow.Delete
End If
Loop
End With

'add your own headers--I used 6 columns.
.Rows(1).Insert
With .Range("a1").Resize(1, 6)
.Value _
= Array("header1", "header" & "2", "header" & vbLf & "3", _
"h4", "h5", "H6")
.WrapText = True
End With
End With

End Sub

Mariano wrote:

Thank you for the suggestions...
I am still learning visual basic, I can follow code, but not neccessarily
write it. I basically learn as I go.
I am going to play with all these suggestions and hopefully drag a buddy of
mine into this to help me with VB.

I kept playing with the text file, and I was incorrect about the delimiter.
The "+" delimiter is actually only included in the column header. The body
of the report uses a "|" as a delimiter. I would need to do something that
would find all the lines that begin with a "+" and only use the data in
between that and the next line that begins with a "+", but also delimit all
the rows at the same place the "+" is in the column header. I hope that makes
some sense.
Tony, you were correct the reports does have page breaks with the headers
and footers, which i would also need to get rid of.

man, i got to learn VB asap!

"tony h" wrote:


Just summarising the issue : you get a text version of a report which is
a good old fashined report.
The report has page breaks and page headers and footers and section
headers in it.

In this case I usually find it easiest to parse the file before
importing it. I do this using the "Open file for input" and the input
line statement. A few well planned tests (looking for blank line, the
text of the headers etc) can soon determine whether you want to keep
the line or not.

Dim TextLine As String
Open "inFILE.TXT" For Input As #1 ' Open file.
Open "outFILE.TXT" For Output As #2
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
If Mid(TextLine, 5, 6) < "Page :" Then
Print #2, TextLine
End If
Loop
Close #1, #2

The resultant file should import with no difficulty.

If the structure is complex it may be worth parsing each line for data
and placing it directly into the spreadsheet. without seeing your text
file it is difficult to say what is best.

Good luck


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=505339



--

Dave Peterson