Re: Importing a .txt file issues

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 Work***

Set wks = Active***
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 spread***. 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?action=getinfo&userid=21074
> > View this thread: http://www.excelforum.com/showthread.php?threadid=505339
> >
> >

--

Dave Peterson
.