Need some help with in coding!!!! text file importing

S

SUDHENDRA

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
 
D

Dick Kusleika

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 said:
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
Click to expand...
 
S

SUDHENDRA

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.

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
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with data importing from txt file to excel programmatically 2
Need Help in importing text file 1

Top