Thread: spaces and codes don't get along

Hi All, I had this following import code to take in text files from emails
and convert them to a columnized excel spreadsheet. It works for emails from
only a certain time period because the email formats change once in a while.
So I am Forced to manipulate the text file first to fit the code. I would
like some changes on this code to adjust to the new email style.

Below the code are examples of how the text files use to look like and how
they look now. Please be careful to note the presence of certain spaces
before and after each line and after each block of information. (the old
format has an empty line with a space in it between each block., the new text
file style has no empty line at all and spaces before each line and after
each line, there are also changes in the number of spaces in certain areas
within each block of information)



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 = 17000
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


Here below is the way it use to be...note the space in the empty line
between each block and no spaces after each line.

06979 Date : 26.11.04 13:18:02 LC : 0 IQ : 40
Lat1 : 65.607N Lon1 : 103.130W Lat2 : 66.861N Lon2 : 110.355W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -132 dB
Pass duration : 483s NOPC : 2
Calcul freq : 401 649301.8 Hz Altitude : 144 m
00 2991 00 11

06979 Date : 26.11.04 14:09:13 LC : B IQ : 00
Lat1 : 65.503N Lon1 : 103.237W Lat2 : 54.304N Lon2 : 41.614W
Nb mes : 002 Nb mes>-120dB : 000 Best level : -133 dB
Pass duration : 138s NOPC : 2
Calcul freq : 401 649301.8 Hz Altitude : 0 m
00 2465 00 11

06979 Date : 26.11.04 14:09:48 LC : 0 IQ : 46
Lat1 : 65.586N Lon1 : 102.939W Lat2 : 64.211N Lon2 : 95.011W
Nb mes : 007 Nb mes>-120dB : 000 Best level : -130 dB
Pass duration : 483s NOPC : 2
Calcul freq : 401 649352.1 Hz Altitude : 134 m
00 2352 00 11


Here below is how the emails are now. note the space diferrences before and
after each line, the lack of an empty line and an extra space before "Date",
as compared with that above.

06976 Date : 03.03.05 13:28:16 LC : 0 IQ : 50

Lat1 : 65.285N Lon1 : 100.524W Lat2 : 68.146N Lon2 : 115.915W

Nb mes : 004 Nb mes>-120dB : 000 Best level : -129 dB

Pass duration : 462s NOPC : 2

Calcul freq : 401 651572.3 Hz Altitude : 138 m

00 70 00 235

06976 Date : 03.03.05 14:12:16 LC : Z IQ : 00

Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????

Nb mes : 009 Nb mes>-120dB : 000 Best level : -128 dB

Pass duration : 594s NOPC : 0

Calcul freq : 401 650000.0 Hz Altitude : 0 m

00 54 55 235

06976 Date : 03.03.05 14:26:34 LC : 0 IQ : 53

Lat1 : 65.317N Lon1 : 100.425W Lat2 : 82.934N Lon2 : 141.492E

Nb mes : 006 Nb mes>-120dB : 000 Best level : -130 dB

Pass duration : 594s NOPC : 3

Calcul freq : 401 651187.5 Hz Altitude : 137 m

00 52 02 235


Any help that can be had immediately will be a lifesaver. Much appreciated.

Eskimo

Please note on the second email type example that there is extra lines that
should not be there. (due to the limitations of the width of this newsgroup
window. the window created extra lines which are empty. please remove each
empty line and move the number of spaces to the end of each line)

Eskimo

"Eskimo" wrote:

> Hi All, I had this following import code to take in text files from emails
> and convert them to a columnized excel spreadsheet. It works for emails from
> only a certain time period because the email formats change once in a while.
> So I am Forced to manipulate the text file first to fit the code. I would
> like some changes on this code to adjust to the new email style.
>
> Below the code are examples of how the text files use to look like and how
> they look now. Please be careful to note the presence of certain spaces
> before and after each line and after each block of information. (the old
> format has an empty line with a space in it between each block., the new text
> file style has no empty line at all and spaces before each line and after
> each line, there are also changes in the number of spaces in certain areas
> within each block of information)
>
>
>
> 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 = 17000
> 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
>
>
> Here below is the way it use to be...note the space in the empty line
> between each block and no spaces after each line.
>
> 06979 Date : 26.11.04 13:18:02 LC : 0 IQ : 40
> Lat1 : 65.607N Lon1 : 103.130W Lat2 : 66.861N Lon2 : 110.355W
> Nb mes : 006 Nb mes>-120dB : 000 Best level : -132 dB
> Pass duration : 483s NOPC : 2
> Calcul freq : 401 649301.8 Hz Altitude : 144 m
> 00 2991 00 11
>
> 06979 Date : 26.11.04 14:09:13 LC : B IQ : 00
> Lat1 : 65.503N Lon1 : 103.237W Lat2 : 54.304N Lon2 : 41.614W
> Nb mes : 002 Nb mes>-120dB : 000 Best level : -133 dB
> Pass duration : 138s NOPC : 2
> Calcul freq : 401 649301.8 Hz Altitude : 0 m
> 00 2465 00 11
>
> 06979 Date : 26.11.04 14:09:48 LC : 0 IQ : 46
> Lat1 : 65.586N Lon1 : 102.939W Lat2 : 64.211N Lon2 : 95.011W
> Nb mes : 007 Nb mes>-120dB : 000 Best level : -130 dB
> Pass duration : 483s NOPC : 2
> Calcul freq : 401 649352.1 Hz Altitude : 134 m
> 00 2352 00 11
>
>
> Here below is how the emails are now. note the space diferrences before and
> after each line, the lack of an empty line and an extra space before "Date",
> as compared with that above.
>
> 06976 Date : 03.03.05 13:28:16 LC : 0 IQ : 50
>
> Lat1 : 65.285N Lon1 : 100.524W Lat2 : 68.146N Lon2 : 115.915W
>
> Nb mes : 004 Nb mes>-120dB : 000 Best level : -129 dB
>
> Pass duration : 462s NOPC : 2
>
> Calcul freq : 401 651572.3 Hz Altitude : 138 m
>
> 00 70 00 235
>
> 06976 Date : 03.03.05 14:12:16 LC : Z IQ : 00
>
> Lat1 : ??????? Lon1 : ???????? Lat2 : ??????? Lon2 : ????????
>
> Nb mes : 009 Nb mes>-120dB : 000 Best level : -128 dB
>
> Pass duration : 594s NOPC : 0
>
> Calcul freq : 401 650000.0 Hz Altitude : 0 m
>
> 00 54 55 235
>
> 06976 Date : 03.03.05 14:26:34 LC : 0 IQ : 53
>
> Lat1 : 65.317N Lon1 : 100.425W Lat2 : 82.934N Lon2 : 141.492E
>
> Nb mes : 006 Nb mes>-120dB : 000 Best level : -130 dB
>
> Pass duration : 594s NOPC : 3
>
> Calcul freq : 401 651187.5 Hz Altitude : 137 m
>
> 00 52 02 235
>
>
> Any help that can be had immediately will be a lifesaver. Much appreciated.
>
> Eskimo