E
Eskimo
I have a neat VB code that I use in Excel. This code imports data from a
text file into columns in an excel spreadsheet.
The excel file has two worksheets, one is to input the .txt filename with a
button for the VB code to do it's magic and convert everything I have in the
text file, which usually has the same layout (in each txt file) in terms of
what data I want columnized in excel. The code goes as follows....
Sub ConvertFile()
Dim LCStart As Long
Dim IQStart As Long
Dim Lat1Start As Long
Dim Lon1Start As Long
Dim Lat2Start As Long
Dim Lon2Start As Long
Dim NbmesStart As Long
Dim Nbmes2Start As Long
Dim BestLevelStart As Long
Dim PassDurStart As Long
Dim NOPCStart As Long
Dim CalcFreqStart As Long
Dim AltStart As Long
Dim Num1Start As Long
Dim Num2Start As Long
Dim Num3Start As Long
Dim Num4Start As Long
Dim LineIn As String
Dim RecNo As Long
Dim RecLine As Long
Dim aryData As Variant
Dim maxRecords As Long
Dim recColumns As Long
maxRecords = 999
recColumns = 20
Worksheets("Raw Data").UsedRange.Offset(1, 0).Resize(maxRecords,
recColumns).EntireRow.Delete
aryData = Worksheets("Raw Data").UsedRange.Offset(0, 0).Resize(maxRecords,
recColumns)
ChDrive Left(ActiveWorkbook.Path, 1)
ChDir ActiveWorkbook.Path
'FName = Range("FName").Value
Open Range("FName") For Input As #1
RecNo = 2
RecLine = 0
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, LineIn ' Read line into variable.
RecLine = RecLine + 1
Select Case RecLine
Case 1
LCStart = WorksheetFunction.Find("LC :", LineIn)
IQStart = WorksheetFunction.Find("IQ :", LineIn)
aryData(RecNo, 1) = Mid(LineIn, 2, 5)
aryData(RecNo, 2) = WorksheetFunction.Substitute(Mid(LineIn, 15, 8),
".", "/")
aryData(RecNo, 3) = TimeValue(Mid(LineIn, 24, 8))
aryData(RecNo, 4) = Trim(Mid(LineIn, LCStart + 4, IQStart - (LCStart
+ 4)))
aryData(RecNo, 5) = Trim(Mid(LineIn, IQStart + 4))
Case 2
Lat1Start = WorksheetFunction.Find("Lat1 :", LineIn)
Lat2Start = WorksheetFunction.Find("Lat2 :", LineIn)
Lon1Start = WorksheetFunction.Find("Lon1 :", LineIn)
Lon2Start = WorksheetFunction.Find("Lon2 :", LineIn)
aryData(RecNo, 6) = Trim(Mid(LineIn, Lat1Start + 6, Lon1Start -
(Lat1Start + 6)))
aryData(RecNo, 7) = Trim(Mid(LineIn, Lon1Start + 6, Lat2Start -
(Lon1Start + 6)))
aryData(RecNo, 8) = Trim(Mid(LineIn, Lat2Start + 6, Lon2Start -
(Lat2Start + 6)))
aryData(RecNo, 9) = Trim(Mid(LineIn, Lon2Start + 6))
Case 3
NbmesStart = WorksheetFunction.Find("Nb mes :", LineIn)
Nbmes2Start = WorksheetFunction.Find("Nb mes>-120dB :", LineIn)
BestLevelStart = WorksheetFunction.Find("Best level :", LineIn)
aryData(RecNo, 10) = Trim(Mid(LineIn, NbmesStart + 8, Nbmes2Start -
(NbmesStart + 8)))
aryData(RecNo, 11) = Trim(Mid(LineIn, Nbmes2Start + 15,
BestLevelStart - (Nbmes2Start + 15)))
aryData(RecNo, 12) = Trim(Mid(LineIn, BestLevelStart + 12))
Case 4
PassDurStart = WorksheetFunction.Find("Pass duration :", LineIn)
NOPCStart = WorksheetFunction.Find("NOPC :", LineIn)
aryData(RecNo, 13) = Trim(Mid(LineIn, PassDurStart + 15, NOPCStart -
(PassDurStart + 15)))
aryData(RecNo, 14) = Trim(Mid(LineIn, NOPCStart + 6))
Case 5
CalcFreqStart = WorksheetFunction.Find("Calcul freq :", LineIn)
AltStart = WorksheetFunction.Find("Altitude :", LineIn)
aryData(RecNo, 15) = Trim(Mid(LineIn, CalcFreqStart + 13, AltStart -
(CalcFreqStart + 13)))
aryData(RecNo, 16) = Trim(Mid(LineIn, AltStart + 10))
Case 6
Num1Start = WorksheetFunction.Find(" ", LineIn, 2)
Num2Start = WorksheetFunction.Find(" ", LineIn, Num1Start + 1)
Num3Start = WorksheetFunction.Find(" ", LineIn, Num2Start + 1)
Num4Start = WorksheetFunction.Find(" ", LineIn, Num3Start + 1)
aryData(RecNo, 17) = Trim(Mid(LineIn, Num1Start + 1, Num2Start -
(Num1Start + 1)))
aryData(RecNo, 18) = Trim(Mid(LineIn, Num2Start + 1, Num3Start -
(Num2Start + 1)))
aryData(RecNo, 19) = Trim(Mid(LineIn, Num3Start + 1, Num4Start -
(Num3Start + 1)))
aryData(RecNo, 20) = Trim(Mid(LineIn, Num4Start + 1))
Case Else
If LineIn = " " Then
RecLine = 0
RecNo = RecNo + 1
Else
MsgBox "Record number " & RecNo & " (collar number " &
aryData(RecNo, 1) & _
")" & Chr(10) & "has this additional line:" & _
Chr(10) & Chr(10) & LineIn, vbInformation, "Please note"
End If
End Select
Loop
Close
Worksheets("Raw Data").UsedRange.Offset(0, 0).Resize(maxRecords, recColumns)
= aryData
Worksheets("Raw Data").UsedRange.Offset(RecNo, 0).Resize(maxRecords + 1 -
RecNo).EntireRow.Delete
End Sub
Now, the txt files normally looks like this...(as an example, I have pasted
three data blocks, each data block would represent one row in excel when
converted. There are normally 450-500 data blocks).....
06979 Date : 18.08.04 13:30:35 LC : 3 IQ : 60
Lat1 : 68.747N Lon1 : 93.642W Lat2 : 83.784N Lon2 : 178.549E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -130 dB
Pass duration : 345s NOPC : 2
Calcul freq : 401 649660.3 Hz Altitude : 0 m
00 5866 00 06
06979 Date : 18.08.04 13:31:10 LC : 2 IQ : 56
Lat1 : 68.753N Lon1 : 93.656W Lat2 : 66.044N Lon2 : 78.199W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -128 dB
Pass duration : 414s NOPC : 3
Calcul freq : 401 649660.6 Hz Altitude : 0 m
00 5853 00 06
06979 Date : 18.08.04 15:09:30 LC : 3 IQ : 68
Lat1 : 68.737N Lon1 : 93.627W Lat2 : 86.767N Lon2 : 25.778E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 483s NOPC : 4
Calcul freq : 401 649651.0 Hz Altitude : 0 m
00 5822 00 06
Note above that there are spaces in certain areas. (one space before the
first block and the first line, 2 spaces before the second line and so on,
AND ONE SPACE AFTER EACH BLOCK).
I can work with this data and it's layout fine and easily converts to
columnized excel file.
However, my problem is this...Once in a while, the data comes in a different
layout and it is very time consuming to make changes to the text file and
adding spaces where they should be in data that comes in which have this
layout....
06976 Date : 07.07.04 13:05:56 LC : 1 IQ : 50
Lat1 : 64.913N Lon1 : 90.657W Lat2 : 81.105N Lon2 : 167.394E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -131 dB
Pass duration : 396s NOPC : 3
Calcul freq : 401 651625.3 Hz Altitude : 0 m
00 169 00 65
06976 Date : 07.07.04 13:08:08 LC : 2 IQ : 66
Lat1 : 64.897N Lon1 : 90.663W Lat2 : 70.048N Lon2 : 119.597W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 263s NOPC : 3
Calcul freq : 401 651621.5 Hz Altitude : 0 m
00 170 01 65
06976 Date : 07.07.04 13:24:05 LC : 0 IQ : 56
Lat1 : 64.887N Lon1 : 90.551W Lat2 : 63.387N Lon2 : 82.438W
Nb mes : 005 Nb mes>-120dB : 000 Best level : -126 dB
Pass duration : 461s NOPC : 2
Calcul freq : 401 651611.8 Hz Altitude : 0 m
00 172 01 65
Note above that there is the one space before the first line, which is fine,
but there is only one space before the second and rest lines where there
should be two, AND THERE IS NO SPACE BETWEEN EACH BLOCK).
Can I make a simple change to the VB code to take into consideration the
missing spaces in the second txt file example shown above? if so, I could
just marry whoever provides an answer if your a girl, or take you in as a
brother in law if your a guy. <sorry, it would just save so much time>.
Eskimo
text file into columns in an excel spreadsheet.
The excel file has two worksheets, one is to input the .txt filename with a
button for the VB code to do it's magic and convert everything I have in the
text file, which usually has the same layout (in each txt file) in terms of
what data I want columnized in excel. The code goes as follows....
Sub ConvertFile()
Dim LCStart As Long
Dim IQStart As Long
Dim Lat1Start As Long
Dim Lon1Start As Long
Dim Lat2Start As Long
Dim Lon2Start As Long
Dim NbmesStart As Long
Dim Nbmes2Start As Long
Dim BestLevelStart As Long
Dim PassDurStart As Long
Dim NOPCStart As Long
Dim CalcFreqStart As Long
Dim AltStart As Long
Dim Num1Start As Long
Dim Num2Start As Long
Dim Num3Start As Long
Dim Num4Start As Long
Dim LineIn As String
Dim RecNo As Long
Dim RecLine As Long
Dim aryData As Variant
Dim maxRecords As Long
Dim recColumns As Long
maxRecords = 999
recColumns = 20
Worksheets("Raw Data").UsedRange.Offset(1, 0).Resize(maxRecords,
recColumns).EntireRow.Delete
aryData = Worksheets("Raw Data").UsedRange.Offset(0, 0).Resize(maxRecords,
recColumns)
ChDrive Left(ActiveWorkbook.Path, 1)
ChDir ActiveWorkbook.Path
'FName = Range("FName").Value
Open Range("FName") For Input As #1
RecNo = 2
RecLine = 0
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, LineIn ' Read line into variable.
RecLine = RecLine + 1
Select Case RecLine
Case 1
LCStart = WorksheetFunction.Find("LC :", LineIn)
IQStart = WorksheetFunction.Find("IQ :", LineIn)
aryData(RecNo, 1) = Mid(LineIn, 2, 5)
aryData(RecNo, 2) = WorksheetFunction.Substitute(Mid(LineIn, 15, 8),
".", "/")
aryData(RecNo, 3) = TimeValue(Mid(LineIn, 24, 8))
aryData(RecNo, 4) = Trim(Mid(LineIn, LCStart + 4, IQStart - (LCStart
+ 4)))
aryData(RecNo, 5) = Trim(Mid(LineIn, IQStart + 4))
Case 2
Lat1Start = WorksheetFunction.Find("Lat1 :", LineIn)
Lat2Start = WorksheetFunction.Find("Lat2 :", LineIn)
Lon1Start = WorksheetFunction.Find("Lon1 :", LineIn)
Lon2Start = WorksheetFunction.Find("Lon2 :", LineIn)
aryData(RecNo, 6) = Trim(Mid(LineIn, Lat1Start + 6, Lon1Start -
(Lat1Start + 6)))
aryData(RecNo, 7) = Trim(Mid(LineIn, Lon1Start + 6, Lat2Start -
(Lon1Start + 6)))
aryData(RecNo, 8) = Trim(Mid(LineIn, Lat2Start + 6, Lon2Start -
(Lat2Start + 6)))
aryData(RecNo, 9) = Trim(Mid(LineIn, Lon2Start + 6))
Case 3
NbmesStart = WorksheetFunction.Find("Nb mes :", LineIn)
Nbmes2Start = WorksheetFunction.Find("Nb mes>-120dB :", LineIn)
BestLevelStart = WorksheetFunction.Find("Best level :", LineIn)
aryData(RecNo, 10) = Trim(Mid(LineIn, NbmesStart + 8, Nbmes2Start -
(NbmesStart + 8)))
aryData(RecNo, 11) = Trim(Mid(LineIn, Nbmes2Start + 15,
BestLevelStart - (Nbmes2Start + 15)))
aryData(RecNo, 12) = Trim(Mid(LineIn, BestLevelStart + 12))
Case 4
PassDurStart = WorksheetFunction.Find("Pass duration :", LineIn)
NOPCStart = WorksheetFunction.Find("NOPC :", LineIn)
aryData(RecNo, 13) = Trim(Mid(LineIn, PassDurStart + 15, NOPCStart -
(PassDurStart + 15)))
aryData(RecNo, 14) = Trim(Mid(LineIn, NOPCStart + 6))
Case 5
CalcFreqStart = WorksheetFunction.Find("Calcul freq :", LineIn)
AltStart = WorksheetFunction.Find("Altitude :", LineIn)
aryData(RecNo, 15) = Trim(Mid(LineIn, CalcFreqStart + 13, AltStart -
(CalcFreqStart + 13)))
aryData(RecNo, 16) = Trim(Mid(LineIn, AltStart + 10))
Case 6
Num1Start = WorksheetFunction.Find(" ", LineIn, 2)
Num2Start = WorksheetFunction.Find(" ", LineIn, Num1Start + 1)
Num3Start = WorksheetFunction.Find(" ", LineIn, Num2Start + 1)
Num4Start = WorksheetFunction.Find(" ", LineIn, Num3Start + 1)
aryData(RecNo, 17) = Trim(Mid(LineIn, Num1Start + 1, Num2Start -
(Num1Start + 1)))
aryData(RecNo, 18) = Trim(Mid(LineIn, Num2Start + 1, Num3Start -
(Num2Start + 1)))
aryData(RecNo, 19) = Trim(Mid(LineIn, Num3Start + 1, Num4Start -
(Num3Start + 1)))
aryData(RecNo, 20) = Trim(Mid(LineIn, Num4Start + 1))
Case Else
If LineIn = " " Then
RecLine = 0
RecNo = RecNo + 1
Else
MsgBox "Record number " & RecNo & " (collar number " &
aryData(RecNo, 1) & _
")" & Chr(10) & "has this additional line:" & _
Chr(10) & Chr(10) & LineIn, vbInformation, "Please note"
End If
End Select
Loop
Close
Worksheets("Raw Data").UsedRange.Offset(0, 0).Resize(maxRecords, recColumns)
= aryData
Worksheets("Raw Data").UsedRange.Offset(RecNo, 0).Resize(maxRecords + 1 -
RecNo).EntireRow.Delete
End Sub
Now, the txt files normally looks like this...(as an example, I have pasted
three data blocks, each data block would represent one row in excel when
converted. There are normally 450-500 data blocks).....
06979 Date : 18.08.04 13:30:35 LC : 3 IQ : 60
Lat1 : 68.747N Lon1 : 93.642W Lat2 : 83.784N Lon2 : 178.549E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -130 dB
Pass duration : 345s NOPC : 2
Calcul freq : 401 649660.3 Hz Altitude : 0 m
00 5866 00 06
06979 Date : 18.08.04 13:31:10 LC : 2 IQ : 56
Lat1 : 68.753N Lon1 : 93.656W Lat2 : 66.044N Lon2 : 78.199W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -128 dB
Pass duration : 414s NOPC : 3
Calcul freq : 401 649660.6 Hz Altitude : 0 m
00 5853 00 06
06979 Date : 18.08.04 15:09:30 LC : 3 IQ : 68
Lat1 : 68.737N Lon1 : 93.627W Lat2 : 86.767N Lon2 : 25.778E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 483s NOPC : 4
Calcul freq : 401 649651.0 Hz Altitude : 0 m
00 5822 00 06
Note above that there are spaces in certain areas. (one space before the
first block and the first line, 2 spaces before the second line and so on,
AND ONE SPACE AFTER EACH BLOCK).
I can work with this data and it's layout fine and easily converts to
columnized excel file.
However, my problem is this...Once in a while, the data comes in a different
layout and it is very time consuming to make changes to the text file and
adding spaces where they should be in data that comes in which have this
layout....
06976 Date : 07.07.04 13:05:56 LC : 1 IQ : 50
Lat1 : 64.913N Lon1 : 90.657W Lat2 : 81.105N Lon2 : 167.394E
Nb mes : 004 Nb mes>-120dB : 000 Best level : -131 dB
Pass duration : 396s NOPC : 3
Calcul freq : 401 651625.3 Hz Altitude : 0 m
00 169 00 65
06976 Date : 07.07.04 13:08:08 LC : 2 IQ : 66
Lat1 : 64.897N Lon1 : 90.663W Lat2 : 70.048N Lon2 : 119.597W
Nb mes : 004 Nb mes>-120dB : 000 Best level : -129 dB
Pass duration : 263s NOPC : 3
Calcul freq : 401 651621.5 Hz Altitude : 0 m
00 170 01 65
06976 Date : 07.07.04 13:24:05 LC : 0 IQ : 56
Lat1 : 64.887N Lon1 : 90.551W Lat2 : 63.387N Lon2 : 82.438W
Nb mes : 005 Nb mes>-120dB : 000 Best level : -126 dB
Pass duration : 461s NOPC : 2
Calcul freq : 401 651611.8 Hz Altitude : 0 m
00 172 01 65
Note above that there is the one space before the first line, which is fine,
but there is only one space before the second and rest lines where there
should be two, AND THERE IS NO SPACE BETWEEN EACH BLOCK).
Can I make a simple change to the VB code to take into consideration the
missing spaces in the second txt file example shown above? if so, I could
just marry whoever provides an answer if your a girl, or take you in as a
brother in law if your a guy. <sorry, it would just save so much time>.
Eskimo