exllearner
New Member
plz provide good example when text file open in vb to processed
i want to know how line input function works when open text file....
- Thread starter exllearner
- Start date
SirJB7
Excel Rōnin
Hi, exllearner!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
Particularly in this case, would you please elaborate a bit more and explain what do you want to do? Even better upload en example file. Give a look to the green sticky posts at this forums main page for uploading guidelines.
Regards!
First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.
As a starting point I'd recommend you to read the green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).
Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.
Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.
And about your question...
If you haven't performed yet the search herein, try going to the topmost right zone of this page (Custom Search), type the keywords used in Tags field when creating the topic or other proper words and press Search button. You'd retrieve many links from this website, like the following one(s) -if any posted below-, maybe you find useful information and even the solution. If not please advise so as people who read it could get back to you as soon as possible.
Particularly in this case, would you please elaborate a bit more and explain what do you want to do? Even better upload en example file. Give a look to the green sticky posts at this forums main page for uploading guidelines.
Regards!
Exllearner
As SirJB7 mentioned a brief of what your trying to achieve and a sample file will be the best way to assist you
Lineinput reads one line of a text file into a variable
There are also other techniques which allow an entire file to be read into a variant array in a single pass, which is a lot quicker than doing it line by line using lineinput.
So understanding what your trying to do is important.
As SirJB7 mentioned a brief of what your trying to achieve and a sample file will be the best way to assist you
Lineinput reads one line of a text file into a variable
There are also other techniques which allow an entire file to be read into a variant array in a single pass, which is a lot quicker than doing it line by line using lineinput.
So understanding what your trying to do is important.
exllearner
New Member
Dim MyString, MyNumber
Dim I, J, K, S As String
Dim f As Variant
file = Application.GetOpenFilename("", 0, _
"Select ZEEI Log file", "", 0)
Open file For Input As #1
Sheets("ZEEI").Select
Cells.Clear
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Cells(1, 1) = "BCF ID"
Cells(1, 2) = "TYPE"
Cells(1, 3) = "LAC"
Cells(1, 4) = "CI"
Cells(1, 5) = "BTS ID"
Cells(1, 6) = "SEG NAME"
Cells(1, 7) = "HOP"
Cells(1, 8) = "TRX"
Cells(1, 9) = "FREQ"
Cells(1, 10) = "ET-PCM"
Cells(1, 11) = "BCCH/CBCH"
Cells(1, 12) = "PREF"
Cells(1, 13) = "ADMIN STATE"
Cells(1, 14) = "OPER. STATE"
I = 2
A = False
B = False
C = False
Do While Not EOF(1)
On Error GoTo P
Line Input #1, S
If (InStr(S, "BCF-") <> 0) Then
A = True
Cells(I, 1) = Trim(Mid(S, 5, 4))
Cells(I, 2) = Trim(Mid(S, 11, 10))
End If
If (InStr(S, "BTS-") <> 0) Then
B = True
Cells(I, 3) = Trim(Mid(S, 1, 6))
Cells(I, 4) = Trim(Mid(S, 8, 5))
Cells(I, 5) = Trim(Mid(S, InStr(S, "BTS-") + 4, 4))
Line Input #1, S
Cells(I, 6) = Trim(Mid(S, 1, 15))
Cells(I, 7) = Trim(Mid(S, 19, 2))
End If
If (InStr(S, "TRX-") <> 0) Then
C = True
Cells(I, 8) = Trim(Mid(S, 19, 3))
Cells(I, 9) = Trim(Mid(S, 34, 5))
Cells(I, 10) = Trim(Mid(S, 42, 4))
Cells(I, 11) = Trim(Mid(S, 47, 10))
Cells(I, 12) = Trim(Mid(S, 61, 1))
Cells(I, 13) = Trim(Mid(S, 24, 1))
Cells(I, 14) = Trim(Mid(S, 26, 6))
End If
If (C = True) Then
I = I + 1
A = False
B = False
C = False
End If
"how line input works in this"
"text format is like this"
MAIN LEVEL COMMAND <___>
< ZEEI:BTS
LOADING PROGRAM VERSION 28.17-0
/* IDENTIFY BASE TRANSCEIVER STATION (BTS):
DECIMAL NUMBER 1 ... 660 ( &, && )
BTS, BCF, SEG, SEGNAME AND NAME PARAMETERS ARE ALTERNATIVE */
EEI:BTS=1&&11;
BSC3i BD009 2013-04-10 09:51:27
RADIO NETWORK CONFIGURATION IN BSC:
E P B
F T R C D-CHANNEL BUSY
AD OP R ET- BCCH/CBCH/ R E S O&M LINK HR FR
LAC CI HOP ST STATE FREQ T PCM ERACH X F U NAME ST
/GP
===================== == ====== ==== == ==== =========== = = == ===== == === ===
BCF-0001 FLEXI EDGE U WO 5 OMU01 WO
00950 13531 BTS-0001 U WO 1 5
VASANTKUNMR1 RF/-
8
TRX-001 U WO 818 0 123 MBCCH+CBCH P 5
TRX-002 U WO 827 0 123 5
TRX-003 U WO 525 0 123 5
TRX-004 U WO 537 0 123 0
00950 13532 BTS-0002 U WO 0 4
VASANTKUNMR2 RF/-
5
TRX-005 U WO 816 0 123 MBCCH+CBCH P 5
TRX-006 U WO 535 0 123 P 3
TRX-007 U WO 524 0 123 3
00950 13533 BTS-0003 U WO 0 6
VASANTKUNMR3 RF/-
5
TRX-008 U WO 541 0 190 3
TRX-009 U WO 823 0 123 MBCCH+CBCH P 4
TRX-010 U WO 539 0 123 4
"and i want data in this format of excel"
BCF ID TYPE LAC CI BTS ID SEG NAME HOP TRX FREQ ET-PCM BCCH/CBCH PREF ADMIN STATE OPER. STATE
1 FLEXI EDGE 950 13531 1 VASANTKUNMR1 RF 1 818 123 BCCH+CBCH U WO
1 FLEXI EDGE 950 13531 1 VASANTKUNMR1 RF 2 827 123 U WO
Dim I, J, K, S As String
Dim f As Variant
file = Application.GetOpenFilename("", 0, _
"Select ZEEI Log file", "", 0)
Open file For Input As #1
Sheets("ZEEI").Select
Cells.Clear
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Cells(1, 1) = "BCF ID"
Cells(1, 2) = "TYPE"
Cells(1, 3) = "LAC"
Cells(1, 4) = "CI"
Cells(1, 5) = "BTS ID"
Cells(1, 6) = "SEG NAME"
Cells(1, 7) = "HOP"
Cells(1, 8) = "TRX"
Cells(1, 9) = "FREQ"
Cells(1, 10) = "ET-PCM"
Cells(1, 11) = "BCCH/CBCH"
Cells(1, 12) = "PREF"
Cells(1, 13) = "ADMIN STATE"
Cells(1, 14) = "OPER. STATE"
I = 2
A = False
B = False
C = False
Do While Not EOF(1)
On Error GoTo P
Line Input #1, S
If (InStr(S, "BCF-") <> 0) Then
A = True
Cells(I, 1) = Trim(Mid(S, 5, 4))
Cells(I, 2) = Trim(Mid(S, 11, 10))
End If
If (InStr(S, "BTS-") <> 0) Then
B = True
Cells(I, 3) = Trim(Mid(S, 1, 6))
Cells(I, 4) = Trim(Mid(S, 8, 5))
Cells(I, 5) = Trim(Mid(S, InStr(S, "BTS-") + 4, 4))
Line Input #1, S
Cells(I, 6) = Trim(Mid(S, 1, 15))
Cells(I, 7) = Trim(Mid(S, 19, 2))
End If
If (InStr(S, "TRX-") <> 0) Then
C = True
Cells(I, 8) = Trim(Mid(S, 19, 3))
Cells(I, 9) = Trim(Mid(S, 34, 5))
Cells(I, 10) = Trim(Mid(S, 42, 4))
Cells(I, 11) = Trim(Mid(S, 47, 10))
Cells(I, 12) = Trim(Mid(S, 61, 1))
Cells(I, 13) = Trim(Mid(S, 24, 1))
Cells(I, 14) = Trim(Mid(S, 26, 6))
End If
If (C = True) Then
I = I + 1
A = False
B = False
C = False
End If
"how line input works in this"
"text format is like this"
MAIN LEVEL COMMAND <___>
< ZEEI:BTS
LOADING PROGRAM VERSION 28.17-0
/* IDENTIFY BASE TRANSCEIVER STATION (BTS):
DECIMAL NUMBER 1 ... 660 ( &, && )
BTS, BCF, SEG, SEGNAME AND NAME PARAMETERS ARE ALTERNATIVE */
EEI:BTS=1&&11;
BSC3i BD009 2013-04-10 09:51:27
RADIO NETWORK CONFIGURATION IN BSC:
E P B
F T R C D-CHANNEL BUSY
AD OP R ET- BCCH/CBCH/ R E S O&M LINK HR FR
LAC CI HOP ST STATE FREQ T PCM ERACH X F U NAME ST
/GP
===================== == ====== ==== == ==== =========== = = == ===== == === ===
BCF-0001 FLEXI EDGE U WO 5 OMU01 WO
00950 13531 BTS-0001 U WO 1 5
VASANTKUNMR1 RF/-
8
TRX-001 U WO 818 0 123 MBCCH+CBCH P 5
TRX-002 U WO 827 0 123 5
TRX-003 U WO 525 0 123 5
TRX-004 U WO 537 0 123 0
00950 13532 BTS-0002 U WO 0 4
VASANTKUNMR2 RF/-
5
TRX-005 U WO 816 0 123 MBCCH+CBCH P 5
TRX-006 U WO 535 0 123 P 3
TRX-007 U WO 524 0 123 3
00950 13533 BTS-0003 U WO 0 6
VASANTKUNMR3 RF/-
5
TRX-008 U WO 541 0 190 3
TRX-009 U WO 823 0 123 MBCCH+CBCH P 4
TRX-010 U WO 539 0 123 4
"and i want data in this format of excel"
BCF ID TYPE LAC CI BTS ID SEG NAME HOP TRX FREQ ET-PCM BCCH/CBCH PREF ADMIN STATE OPER. STATE
1 FLEXI EDGE 950 13531 1 VASANTKUNMR1 RF 1 818 123 BCCH+CBCH U WO
1 FLEXI EDGE 950 13531 1 VASANTKUNMR1 RF 2 827 123 U WO
SirJB7
Excel Rōnin
Hi, exllearner!
Regarding your question...
If I didn't understand bad your requirement is about how LINE INPUT instruction works. If so, LINE INPUT reads a whole line from an open text file identified by 1st parameter (#x, where x is the buffer number assigned internally) and places it content into the variable (string or variant) specified by 2nd parameter.
Hope it helps.
Regarding your desired output...
Well, the code provided is incomplete and so it doesn't work. Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
Regarding your question...
If I didn't understand bad your requirement is about how LINE INPUT instruction works. If so, LINE INPUT reads a whole line from an open text file identified by 1st parameter (#x, where x is the buffer number assigned internally) and places it content into the variable (string or variant) specified by 2nd parameter.
Hope it helps.
Regarding your desired output...
Well, the code provided is incomplete and so it doesn't work. Consider uploading a sample file (including manual examples of desired output), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.
Regards!
SirJB7
Excel Rōnin
Hi, exllearner!
Updating your code to this:
-----
[pre]
[/pre]
-----
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/i%20want%20to%20know%20how%20line%20input%20function%20works%20when%20open%20text%20file....%20%28for%20exllearner%20at%20chandoo.org%29.xlsm
Do you think you can manage to handle the modifications to the VBA code now that you know how does LINE INPUT instruction works?
Regards!
Updating your code to this:
-----
[pre]
Code:
Option Explicit
Sub X()
Dim MyString, MyNumber
Dim I, J, K, S As String
Dim file As Variant
Dim A, B, C As Boolean
file = Application.GetOpenFilename("", 0, "Select ZEEI Log file", "", 0)
Open file For Input As #1
Sheets("ZEEI").Select
Cells.Clear
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
Cells(1, 1) = "BCF ID"
Cells(1, 2) = "TYPE"
Cells(1, 3) = "LAC"
Cells(1, 4) = "CI"
Cells(1, 5) = "BTS ID"
Cells(1, 6) = "SEG NAME"
Cells(1, 7) = "HOP"
Cells(1, 8) = "TRX"
Cells(1, 9) = "FREQ"
Cells(1, 10) = "ET-PCM"
Cells(1, 11) = "BCCH/CBCH"
Cells(1, 12) = "PREF"
Cells(1, 13) = "ADMIN STATE"
Cells(1, 14) = "OPER. STATE"
I = 2
A = False
B = False
C = False
Do While Not EOF(1)
On Error GoTo P
Line Input #1, S
If (InStr(S, "BCF-") <> 0) Then
A = True
Cells(I, 1) = Trim(Mid(S, 5, 4))
Cells(I, 2) = Trim(Mid(S, 11, 10))
End If
If (InStr(S, "BTS-") <> 0) Then
B = True
Cells(I, 3) = Trim(Mid(S, 1, 6))
Cells(I, 4) = Trim(Mid(S, 8, 5))
Cells(I, 5) = Trim(Mid(S, InStr(S, "BTS-") + 4, 4))
Line Input #1, S
Cells(I, 6) = Trim(Mid(S, 1, 15))
Cells(I, 7) = Trim(Mid(S, 19, 2))
End If
If (InStr(S, "TRX-") <> 0) Then
C = True
Cells(I, 8) = Trim(Mid(S, 19, 3))
Cells(I, 9) = Trim(Mid(S, 34, 5))
Cells(I, 10) = Trim(Mid(S, 42, 4))
Cells(I, 11) = Trim(Mid(S, 47, 10))
Cells(I, 12) = Trim(Mid(S, 61, 1))
Cells(I, 13) = Trim(Mid(S, 24, 1))
Cells(I, 14) = Trim(Mid(S, 26, 6))
End If
If (C = True) Then
I = I + 1
A = False
B = False
C = False
End If
Loop
Close #1
Application.Calculation = xlCalculationAutomatic
GoTo Q
P:
' and now what?
Q:
End Sub
-----
I get a result output as this, which is different than what you posted:
-----
BCF ID TYPE LAC CI BTS ID SEG NAME HOP TRX FREQ ET-PCM BCCH/CBCH PREF ADMIN STATE OPER. STATE
1 LEXI EDGE 950 3531 1 VASANTKUNMR1 RF 12 P 5 M CCH+CB
12 5
12 5
12 0
950 3532 2 VASANTKUNMR2 RF 12 P 5 M CCH+CB
12 P 3
12 3
950 3533 3 VASANTKUNMR3 RF 19 3
12 P 4 M CCH+CB
12 4-----
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/i%20want%20to%20know%20how%20line%20input%20function%20works%20when%20open%20text%20file....%20%28for%20exllearner%20at%20chandoo.org%29.xlsm
Do you think you can manage to handle the modifications to the VBA code now that you know how does LINE INPUT instruction works?
Regards!