Need Help in importing text file

S

Sudhendra

Hi

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 (Note: for easy understanding i
have added the linenumbers as "L01:" which are not part
of data)

L01:1BOSTON

1
L02:-

2002 CALENDAR YEAR SALES
L03:- SHOWING PRODUCE WHO
SOLD INTO ACTON MARKET
AREA 12B202
L04:0PURCHASER TOWN/
BOOSTING DEALER
CHRISTMAS HOTCAKE
L05: POST OFFICE SLR DEALER
NAME DEALER MARKET AREA
L06: COMMUNITY
NO LOCATIONS
L07: 12B202 Acton MERCY-KRANK In
Acton MA 57 704 5 13 41
116 23 622 10 34
L08:
TOTAL IN MARKET 57 704 5 13 41 116
23 622 10 34
L09:0 12B073 Clark & White Inc
Newton MA 1 12 1 2
L10: 12B386 Sentry HEART-FORD S
Medford MA 1 12 2
3 3 81 1 1 5
L11: 12B491 North Shore D-T Inc
Peabody MA 1 1
L12:1BOSTON

2
L13:-

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

377
L22:-

2002 CALENDAR YEAR SALES
L23:- SHOWING PRODUCE WHO
SOLD INTO AL-SCH-TRY MARKET
AREA 14B981
L24:0PURCHASER TOWN/
BOOSTING DEALER
CHRISTMAS HOTCAKE
L25: POST OFFICE sLR DEALER
NAME DEALER MARKET AREA
L26: COMMUNITY
NO LOCATIONS
L27: 14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
L28: 14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
L29: 14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113
L30:
TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177
L31:1NEW
YORK

377
L32:-

2002 CALENDAR YEAR SALES
L33:- SHOWING PRODUCE WHO
SOLD INTO AL-SCH-TRY MARKET
AREA 14B981
L34:0PURCHASER TOWN/
BOOSING DEALER
CHRISTMAS HOTCAKE
L35: POST OFFICE SLR DEALER
NAME DEALER MARKET AREA
L36: COMMUNITY NO
LOCATIONS
L37: 14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
L38: 14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
L39: 14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113
L40:
TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177

Here I need to import the line numbers L01 thru L21
for "1BOSTON" data at the same time need to parse into
each colums.
Thanks very much
 
K

Kevin McCartney

To save you time and effort import your text file into an
Access database table. Then write a simple query to
extract the required records. Just use the import wizard
to get you data in, then use the query wizard to select
the records that you are after. From the query you can
copy and paste into excel. If you don't have Access,
suggest you ask the source of where your data came from
and get them to separate the data for you. Excel is
limited to 65536 rows.

regards
KM
-----Original Message-----
Hi

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 (Note: for easy understanding i
have added the linenumbers as "L01:" which are not part
of data)

L01:1BOSTON

1
L02:-

2002 CALENDAR YEAR SALES
L03:- SHOWING PRODUCE WHO
SOLD INTO ACTON MARKET
AREA 12B202
L04:0PURCHASER TOWN/
BOOSTING DEALER
CHRISTMAS HOTCAKE
L05: POST OFFICE SLR DEALER
NAME DEALER MARKET AREA
L06: COMMUNITY
NO LOCATIONS
L07: 12B202 Acton MERCY-KRANK In
Acton MA 57 704 5 13 41
116 23 622 10 34
L08:
TOTAL IN MARKET 57 704 5 13 41 116
23 622 10 34
L09:0 12B073 Clark & White Inc
Newton MA 1 12 1 2
L10: 12B386 Sentry HEART-FORD S
Medford MA 1 12 2
3 3 81 1 1 5
L11: 12B491 North Shore D-T Inc
Peabody MA 1 1
L12:1BOSTON

2
L13:-

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

377
L22:-

2002 CALENDAR YEAR SALES
L23:- SHOWING PRODUCE WHO
SOLD INTO AL-SCH-TRY MARKET
AREA 14B981
L24:0PURCHASER TOWN/
BOOSTING DEALER
CHRISTMAS HOTCAKE
L25: POST OFFICE sLR DEALER
NAME DEALER MARKET AREA
L26: COMMUNITY
NO LOCATIONS
L27: 14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
L28: 14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
L29: 14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113
L30:
TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177
L31:1NEW
YORK

377
L32:-

2002 CALENDAR YEAR SALES
L33:- SHOWING PRODUCE WHO
SOLD INTO AL-SCH-TRY MARKET
AREA 14B981
L34:0PURCHASER TOWN/
BOOSING DEALER
CHRISTMAS HOTCAKE
L35: POST OFFICE SLR DEALER
NAME DEALER MARKET AREA
L36: COMMUNITY NO
LOCATIONS
L37: 14B402 Nemith CHOLO Corp
Latham NY 60 150 10 13 83
37 219 1 7 48
L38: 14B539 Fuccillo HUMBLE-MET
Schenectady NY 58 145 2 6 7 73
13 77 3 16
L39: 14B561 Lazare DETPLUB I
Albany NY 76 190 16 50 142
92 544 18 113
L40:
TOTAL IN MARKET 194 485 2 32 70 298
142 840 1 28 177

Here I need to import the line numbers L01 thru L21
for "1BOSTON" data at the same time need to parse into
each colums.
Thanks very much
.
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

Need some help with in coding!!!! text file importing 4
Help with data importing from txt file to excel programmatically 2
Need Help Importing Text File Using Two or More Spaces as the Delimiter 11
I need help with wrapping text in excel 2
Brain dump... 1

Top