multi-line text file import to excel
 

I need some guidance on how to handle a multi-line text file import to
excel.

I have a multi-line text file i am attempting to parse into Excel2003.
Here is an example of the file:


<rlnrp:cell=all;
NEIGHBOUR RELATION DATA


CELL
WA32300


CELLR DIR CAND CS
WA31340 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELLR DIR CAND CS
WA30780 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELL
WA30433


CELLR DIR CAND CS
WA31960 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


END


---------------------------------------------------------------
Details of where i am trying to go with this:


The format of the source file is not modifiable so i am left to deal
with it as is. I had created a macro to handle the basic import a space

delimited and then another macro to sort it the way i wanted. This
worked until the text file exceeded 65000 lines in excel. Now it has
become an issue. What i ultimately need to achieve is a way to define a

Start and Stop where all data between Start and Stop is read in as 1
line. It would identify CELL as the Start and read in to 1 line until
it comes across CELL (Stop) again. Then continues on. I just need the
CELL and CELLR data. Thus looking like so:


CELL CELLR
WA32300 WA31340 WA30780
WA30433 WA31960


......and so on down the file.


So, either some sort of Start and Stop trigger to define when to start
writing a new line into Excel or a means of sending a string to a
specific range (column) within Excel. So, any help or guidance you can
offer will be much appreciated.


Thank you

multi-line text file import to excel
 

I would use VBA to read the text file and populate Excel. I think the below
should give you an idea where to start. The below code reads the text file
and puts the references into a new book. Where I have hard coded the file
name you should link this to a cell value. The code runs from a button put
into an empty sheet


Private Sub CommandButton1_Click()
Dim Fn, r, c As Long
Dim LineOfText As String
Dim xlBook As Excel.Workbook

r = 1
c = 1
Set xlBook = Application.Workbooks.Add

Fn = FreeFile()
Open "c:\cell.txt" For Input As Fn
While Not EOF(Fn)
Line Input #Fn, LineOfText

If Left$(LineOfText, 5) = "CELLR" Then
c = c + 1
Line Input #Fn, LineOfText
ActiveSheet.Cells(r, c).Value = Left$(LineOfText, 7)
ElseIf Left$(LineOfText, 4) = "CELL" Then
r = r + 1
c = 1
Line Input #Fn, LineOfText
ActiveSheet.Cells(r, c).Value = Left$(LineOfText, 7)
End If
Wend

Close #Fn
End Sub
--
Tony Green


"Waxaholic" wrote:

I need some guidance on how to handle a multi-line text file import to
excel.

I have a multi-line text file i am attempting to parse into Excel2003.
Here is an example of the file:


<rlnrp:cell=all;
NEIGHBOUR RELATION DATA


CELL
WA32300


CELLR DIR CAND CS
WA31340 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELLR DIR CAND CS
WA30780 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


CELL
WA30433


CELLR DIR CAND CS
WA31960 MUTUAL BOTH NO


KHYST KOFFSETP KOFFSETN LHYST LOFFSETP LOFFSETN
3 0 3 0


TRHYST TROFFSETP TROFFSETN AWOFFSET BQOFFSET
2 0 5 3


HIHYST LOHYST OFFSETP OFFSETN BQOFFSETAFR
5 3 0 3


END


---------------------------------------------------------------
Details of where i am trying to go with this:


The format of the source file is not modifiable so i am left to deal
with it as is. I had created a macro to handle the basic import a space

delimited and then another macro to sort it the way i wanted. This
worked until the text file exceeded 65000 lines in excel. Now it has
become an issue. What i ultimately need to achieve is a way to define a

Start and Stop where all data between Start and Stop is read in as 1
line. It would identify CELL as the Start and read in to 1 line until
it comes across CELL (Stop) again. Then continues on. I just need the
CELL and CELLR data. Thus looking like so:


CELL CELLR
WA32300 WA31340 WA30780
WA30433 WA31960


......and so on down the file.


So, either some sort of Start and Stop trigger to define when to start
writing a new line into Excel or a means of sending a string to a
specific range (column) within Excel. So, any help or guidance you can
offer will be much appreciated.


Thank you

multi-line text file import to excel
 

Awesome. Thank you very much. This will certainly save me some time.