PDA

View Full Version : get text from txt to excel table



enfapro7
07-13-2017, 10:15 AM
Dear All Suhu's,

im indra near from bali,
need help i have a txt file with vetical data, can i move itu to excel table into horizontal?
19731
19729
DSP UCELLCHK:CHECKSCOPE=CELLID,CELLID=30401;
RNGTL01
+++ RNGTL01 2017-07-13 15:08:05
O&M #2454444
%%/*4690319*/DSP UCELLCHK: CHECKSCOPE=CELLID,CELLID=30401;%%
RETCODE = 0 Execution succeeded.


Cell health information
-----------------------
Cell ID = 30401
Cell is activated or not = Yes
Cell is unblocked or not = Yes
Cell is setup or not = Yes
Cell barred indicator in idle mode = Access allowed
Cell barred indicator in connected mode = Access allowed
Cell is available or not = Yes
(Number of results = 1)
--- END


DSP UCELLCHK:CHECKSCOPE=CELLID,CELLID=30401;
RNGTL01
+++ RNGTL01 2017-07-13 15:08:05
O&M #2454444
%%/*4690319*/DSP UCELLCHK: CHECKSCOPE=CELLID,CELLID=30401;%%
RETCODE = 0 Execution succeeded.


Cell health information
-----------------------
Cell ID = 30401
Cell is activated or not = Yes
Cell is unblocked or not = Yes
Cell is setup or not = Yes
Cell barred indicator in idle mode = Access allowed
Cell barred indicator in connected mode = Access allowed
Cell is available or not = Yes
(Number of results = 1)
--- END

results
19730

Leith Ross
07-13-2017, 02:04 PM
Hello enfapro7,

The macro below should import the file as you requested. If not, let me know what needs to be changed. The attached workbook has the macro installed and button to run to it.



Sub ImportLogFile()


Dim Chars() As Byte
Dim Data() As Variant
Dim File As String
Dim Line As Variant
Dim LineCnt As Long
Dim Lines As Variant
Dim n As Long
Dim Rng As Range
Dim row As Long
Dim Text As String
Dim Wks As Worksheet

Set Wks = ActiveSheet
Set Rng = Wks.Range("A2:J2")

ReDim Data(1 To Rng.Columns.Count, 1 To 1)

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
If .Show = -1 Then
File = .SelectedItems(1)
Else
Exit Sub
End If
End With

Intersect(Wks.UsedRange, Wks.UsedRange.Offset(1, 0)).ClearContents

Open File For Binary Access Read As #1
ReDim Chars(LOF(1))
Get #1, , Chars
Close #1

Text = StrConv(Chars, vbUnicode)

Lines = Split(Text, vbCrLf)

For LineCnt = 0 To UBound(Lines) - 1
row = UBound(Data, 2)
Line = Lines(LineCnt)

Select Case Left(Line, 5)
Case Is = "+++ "
Line = Application.Trim(Line)
Line = Split(Line, " ")
Data(1, row) = Line(1)
Data(2, row) = Line(2)
Data(3, row) = Line(3)
Case Is = "Cell "
For n = 2 To 8
Line = Application.Trim(Lines(LineCnt + n))
Data(n + 2, row) = Split(Line, "=")(1)
Next n

ReDim Preserve Data(1 To Rng.Columns.Count, 1 To row + 1)
LineCnt = LineCnt + 10
End Select
Next LineCnt

On Error Resume Next
Data = Application.Transpose(Data)
Rng.Resize(UBound(Data, 1), UBound(Data, 2)).Value = Data

End Sub

enfapro7
07-14-2017, 01:35 AM
Dear Leith Ross,

sorry for late respons, its all work for my case.. many thanks..
:clap::clap::clap:

Leith Ross
07-14-2017, 01:49 AM
Hello enfapro7,

Thanks for the feedback. Nice to know it worked the first time for you. You're welcome.