Help with data importing from txt file to excel programmatically

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
 
L

losmac

Here You have an example.
But remember, I really need to have your txt file to test
on it and more information how Ypu want to extract data
from this file. I don't think your copied and pasted data
have the same structur as is in txt file. What is a sign
to recognize column (tab, 4 spaces, ";", "-")?

Sub MetroDatail()
'ExtractDetail "1BOSTON"
ExtractDetail "1NEWYORK"
End Sub

Sub ExtractDetail(sFindText As String)
Dim strTemp As String, sinput_path As String
Dim REGIONFILE As String
Dim i As Long 'number of row
Dim InFile As Long 'number of file

sinput_path = "C:\"
REGIONFILE = "test.TXT"

InFile = FreeFile()

Open sinput_path & REGIONFILE For Input Access Read As
InFile
Do While Not EOF(InFile)
Line Input #InFile, strTemp
If (InStr(1, strTemp, sFindText) > 0) Then
i = i + 1
ThisWorkbook.Worksheets(3).Range("A" & i) =
strTemp
End If
Loop
Close #InFile

End Sub


-----Original 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


.
Click to expand...
 
S

SUDHENDRA

THANKS
-----Original Message-----
Here You have an example.
But remember, I really need to have your txt file to test
on it and more information how Ypu want to extract data
from this file. I don't think your copied and pasted data
have the same structur as is in txt file. What is a sign
to recognize column (tab, 4 spaces, ";", "-")?

Sub MetroDatail()
'ExtractDetail "1BOSTON"
ExtractDetail "1NEWYORK"
End Sub

Sub ExtractDetail(sFindText As String)
Dim strTemp As String, sinput_path As String
Dim REGIONFILE As String
Dim i As Long 'number of row
Dim InFile As Long 'number of file

sinput_path = "C:\"
REGIONFILE = "test.TXT"

InFile = FreeFile()

Open sinput_path & REGIONFILE For Input Access Read As
InFile
Do While Not EOF(InFile)
Line Input #InFile, strTemp
If (InStr(1, strTemp, sFindText) > 0) Then
i = i + 1
ThisWorkbook.Worksheets(3).Range("A" & i) =
strTemp
End If
Loop
Close #InFile

End Sub


-----Original 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


.
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

Need some help with in coding!!!! text file importing 4
Need Help in importing text file 1

Top