THANKS
-----Original Message-----
Sudhendra

Create an array with all of the starting points for the
fields, like this

Dim arrStart As Variant

arrStart = Array(1, 59, 86, 115, 150, 169, 201, 450,
650, 850, 932)

Make the last element in the array the length of the
string. So in this
example the string is 932 characters long and the fields
start at positions
1, 59, 86, ... 850 for ten total fields. Then you can
loop through the
array and parse out the data like

For i = LBound(arrStart) To UBound(arrStart) - 1
Sheet1.Range("a1").Offset(0, i).Value = _
Mid(y, arrStart(i), arrStart(i + 1) - arrStart
(i))
Next i

to put each field in a separate column.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"SUDHENDRA" wrote
in message
...
I have a text file which has data(sample data below), i
cannot import data directly into the excel sheet
because
its a large file and it limits the excel rows for
import.

I need to import from the text file phase by phase, the
data required.

Presume I have in the first line '1BOSTON', i need to
get
all the data until again i hit the other string '1NEW
YORK' in the row and this data has to be parsed on
fixedlenth or each of the rows between '1BOSTON'
to '1BOSTON' and looping thru and parsed imported.
Then the string'1NEWYORK' to string '1NEWYORK' untill
it
hits another new string...
Can any one help me with the code please!!!!


I started the coding but could not figure out where i
should go...

Sub MetroDetail()

Dim oCell As Range, xCell As Range
Dim x As Integer, Count As Integer, z As Integer
Dim qt, MetConn As String
Dim sinput_path As String
Dim REGIONFILE As String

'Part of the code

Application.DisplayAlerts = False
Application.ScreenUpdating = False
'Windows("Main.xls").Activate
sinput_path = "C:\DATA\"
REGIONFILE = "LCSD02R.TXT"

InFile = FreeFile()

Open sinput_path & REGIONFILE For Input Access Read
As InFile
Do While Not EOF(InFile)
Line Input #InFile, y
If (InStr(1, y, "1BOSTON", vbTextCompare) 0)
Then
ActiveCell.Value = y

'need to write the code for extracting and parsing

End If
Loop
Close #InFile
[A1].Select

End sub

Sample Data for Review

1BOSTON

1
-
2002 CALENDAR YEAR SALES
- SHOWING PRODUCE WHO SOLD
INTO ACTON MARKET
AREA 12B202
0PURCHASER TOWN/ BOOSTING
DEALER CHRISTMAS
HOTCAKE
POST OFFICE SLR DEALER NAME
DEALER MARKET AREA
COMMUNITY NO
LOCATIONS
12B202 Acton MERCY-KRANK In
Acton MA 57 704 5 13 41
116 23 622 10 34

TOTAL IN MARKET 57 704 5 13 41 116
23 622 10 34
0 12B073 Clark & White Inc
Newton MA 1 12 1 2
12B386 Sentry HEART-FORD S
Medford MA 1 12 2
3 3 81 1 1 5
12B491 North Shore D-T Inc
Peabody MA 1 1
1BOSTON

2
-
2002 CALENDAR YEAR SALES
- SHOWING PRODUCE WHO SOLD
INTO ACTON MARKET
AREA 12B202
0PURCHASER TOWN/ BOOSTING
DEALER
CHRISTMAS HOTCAKE
POST OFFICE SLR DEALER NAME
DEALER MARKET AREA
COMMUNITY NO
LOCATIONS
12D487 Gervais MERCY-KRANK
Lowell
MA 2 500
2
999999
UNKNOWN
1 250 1
TOTAL POST
OFFICE PURCHASES 21000 1 3
41000 4
1NEW
YORK

377
-
2002 CALENDAR YEAR SALES
- SHOWING PRODUCE WHO SOLD
INTO AL-SCH-TRY MARKET
AREA 14B981
0PURCHASER TOWN/ BOOSTING
DEALER
CHRISTMAS HOTCAKE
POST OFFICE sLR DEALER NAME
DEALER MARKET AREA
COMMUNITY NO
LOCATIONS
14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113

TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177
1NEW
YORK

378
-
2002 CALENDAR YEAR SALES
- SHOWING PRODUCE WHO SOLD
INTO AL-SCH-TRY MARKET
AREA 14B981
0PURCHASER TOWN/ BOOSING
DEALER
CHRISTMAS HOTCAKE
POST OFFICE SLR DEALER NAME
DEALER MARKET AREA
COMMUNITY NO
LOCATIONS
14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113

TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177




.