Import Record with Multiple Rows

[nerkalyn]

Hi,

I receive a .txt file daily with about 20 records in the following format:



Prog 05001


104857 Date : 17.02.14 00:10:13 LC : 1 IQ : 50
Lat1 : 51.583N Lon1 : 112.324W Lat2 : 51.583N Lon2 : 112.324W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -126 dB
Pass duration : 360s NOPC : 3
Calcul freq : 401 677423.9 Hz Altitude : 947 m
69 198 12


104857 Date : 17.02.14 01:52:44 LC : B IQ : 00
Lat1 : 51.583N Lon1 : 112.318W Lat2 : 51.583N Lon2 : 112.318W
Nb mes : 002 Nb mes>-120dB : 000 Best level : -136 dB
Pass duration : 180s NOPC : 3
Calcul freq : 401 677438.0 Hz Altitude : 1210 m
24 39 02


104857 Date : 17.02.14 01:51:54 LC : 2 IQ : 57
Lat1 : 51.583N Lon1 : 112.317W Lat2 : 51.583N Lon2 : 112.317W
Nb mes : 006 Nb mes>-120dB : 000 Best level : -125 dB
Pass duration : 360s NOPC : 3


Calcul freq : 401 677451.6 Hz Altitude : 996 m
225 175 163

I need to transmogrify it into something like this (in a table):

AID Date LC IQ Lat1 Lon1 Lat2 Lon2 Nb Mes Nbmes>-120db Best Level Pass duration NOPC Calcul freq Altitude A B C
104587 17.02.14 00:10:13 1 50 51.583N 112.324W 51.583N 112.324W 006 000 -126 db 360s 3 401 677423.9 hz 947 m 69 198 12
104857 17.02.14 01:52:44 B 00 51.583N 112.318W 51.583N 112.318W 002 000 -136 db 180s 3 401 677438.0 hz 1210 m 24 39 02
104857 17.02.14 01:51:54 2 57 51.853N 112.317W 51.583N 112.317W 006 000 -125 db 360s 3 401 677451.6 hz 996 m 225 175 163

I have imported my share of data and dabbled with vba when I have a detailed example to follow - but have no idea where to start on this one. Did some searching but nothing I have found seems to fit well enough to cover my data format.

Any help would be appreciated.

Thanks

[rpeare]

can you upload an actual file? or can I safely assume that if I cut and paste this text into a text file it's going to be exactly as it appears to you under normal circumstances?

[nerkalyn]

can you upload an actual file? or can I safely assume that if I cut and paste this text into a text file it's going to be exactly as it appears to you under normal circumstances?

Ok, here is one of the files... (I think) I don't see it attached anywhere.

Thanks

[rpeare]

Here's a simple example:

You'll have to adapt it if you want to process multiple files in a directory etc but this will process one file with a static file name with the same format you posted in your DL.txt (didn't do a full check but it looks like it will)

EDIT:
One other thing, I made it tilde delimited because some of your field names have spaces in them as does some of your data so you don't want that to mess up any other processes that rely on this. You can insert a tab or other delimiter if you don't like the tilde.

Dim fs
Dim fInput
Dim fOutput
Dim sInputFile As String
Dim sOutputFile As String
Dim sOutputString As String
Dim aArray

sInputFile = "c:\test\dl.txt"
sOutputFile = "c:\test\dl_output.txt"

Set fs = CreateObject("scripting.filesystemobject")
Set fInput = fs.opentextfile(sInputFile)
Set fOutput = fs.createtextfile(sOutputFile, True)

sOutputString = "AID~Date~LC~IQ~Lat1~Lon1~Lat2~Lon2~Nb mes>=120dB~Best Level~Pass Duration~NOPC~Calcul Freq~Altitude~A~B~C"
fOutput.writeline sOutputString
'***** INPUT *****
'104857 Date : 17.02.14 00:10:13 LC : 1 IQ : 50
'Lat1 : 51.583N Lon1 : 112.324W Lat2 : 51.583N Lon2 : 112.324W
'Nb mes : 006 Nb mes>-120dB : 000 Best level : -126 dB
'Pass duration : 360s NOPC : 3
'Calcul freq : 401 677423.9 Hz Altitude : 947 m
'69 198 12
'***** OUTPUT *****
'AID Date LC IQ Lat1 Lon1 Lat2 Lon2 Nb Mes Nbmes>-120db Best Level Pass duration NOPC Calcul freq Altitude A B C
'104587 17.02.14 00:10:13 1 50 51.583N 112.324W 51.583N 112.324W 006 000 -126 db 360s 3 401 677423.9 hz 947 m 69 198 12

Do While fInput.atendofstream <> True
    sline = fInput.readline
    If InStr(sline, "Date :") > 0 Then
        sOutputString = Trim(Left(sline, InStr(sline, "Date") - 1)) & "~" 'This Gets the AID
        sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, "Date : ") + 6, 18)) & "~" 'This gets the Date
        sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, "LC : ") + 4, InStr(sline, "IQ : ") - (InStr(sline, "LC : ") + 4))) & "~" 'This gets the IQ value if it's longer than one digit
        sOutputString = sOutputString & Trim(Right(sline, Len(Trim(sline)) - (InStrRev(Trim(sline), ":") + 1))) & "~" 'This gets the IQ value
        For i = 1 To 5
            sline = fInput.readline
            If i = 1 Then
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, ":") + 1, InStr(sline, "Lon1") - 1 - InStr(sline, ":"))) & "~" 'This gets Lat1
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, "Lon1") + 7, (InStr(sline, "Lat2") - 1) - (InStr(sline, "Lon1") + 6))) & "~" 'This gets Lon1
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, "Lat2") + 7, (InStr(sline, "Lon2") - 1) - (InStr(sline, "Lat2") + 6))) & "~" 'This gets Lat2
                sOutputString = sOutputString & Right(Trim(sline), Len(Trim(sline)) - (InStrRev(Trim(sline), ":") + 1)) & "~" 'This gets Lon2
            ElseIf i = 2 Then
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, ":") + 1, (InStr(sline, "nb mes>") - 1) - (InStr(sline, ":") + 1))) & "~" 'This gets NB Mes
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, "nb mes>") + 15, (InStr(sline, "Best") - 1) - (InStr(sline, "nb mes>") + 15))) & "~" 'This gets NB Mes>=120dB
                sOutputString = sOutputString & Trim(Right(Trim(sline), Len(Trim(sline)) - (InStrRev(Trim(sline), ":") + 1))) & "~" 'This gets Best Level
            ElseIf i = 3 Then
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, ":") + 1, (InStr(sline, "NOPC") - 1) - (InStr(sline, ":") + 1))) & "~" 'This gets Pass Duration
                sOutputString = sOutputString & Right(Trim(sline), Len(Trim(sline)) - (InStrRev(Trim(sline), ":") + 1)) & "~" 'This gets NOPC
            ElseIf i = 4 Then
                sOutputString = sOutputString & Trim(Mid(sline, InStr(sline, ":") + 1, (InStr(sline, "Altit") - 1) - (InStr(sline, ":") + 1))) & "~" 'This gets Calcul Freq
                sOutputString = sOutputString & Right(Trim(sline), Len(Trim(sline)) - (InStrRev(Trim(sline), ":") + 1)) & "~" 'This gets Altitude
            ElseIf i = 5 Then
                aArray = Split(sline, " ")
                For j = 0 To UBound(aArray)
                    If Len(Trim(aArray(j))) > 0 Then
                        sOutputString = sOutputString & aArray(j) & "~"
                    End If
                Next j
            End If
        Next i
        sOutputString = left(sOutputString, len(sOutputString) - 1) 'gets rid of right hand tilde
        Debug.Print sOutputString
        fOutput.writeline sOutputString
    End If
Loop
fInput.Close
fOutput.Close
Set fs = Nothing

[ssanfu]

@ rpeare,

Nice bit of code!!

Too bad the OP didn't provide the table name, field names and data types. The data could have been dumped directly into the dB.......

[rpeare]

I just assumed he needed it for another reason other than dumping it into an access table, but yeah, could just append records to a table as you go through it just as easily.

[nerkalyn]

WOW! Thanks a TON rpeare!

I am going to spend some time studying it to make sure I understand it and can use the techniques on other stuff moving forward.

The data will probably be stored in an access or sql server table, maybe even mySql - but I haven't identified or created that table yet. I will take a stab at modifying the code when I do.