Import txt file with vertical fields into access db

[Roberto Mora]

I have not done programming in a very long time and what is worst, I never learned VB. Although my job does not require this knowledge, I cam across a problem that although it seemed simple it has become a nightmare.

There is a log that gets generated in a regular basis and need to put most , but not all its contents in a DB (new or existent, it doesn't matter). because the fields that I need to cover are vertically and there is junk in the file, I the wizards nor the Macros in Access or Excel 2003 seem to be much help. The log file looks like this (log111.txt)

---------------------------------------------------------------------------------------------------------------
:¿G 6 [SIG2] sigmaInit() initialized RUA by calling avis_init()
6 [CLST] Can't open semaphore: No such file or directory
7 [OPRA] Registered TVCSYS/AVIS callback
6 [MP M]
6 [MP M] ********************** File Playback Statistics **************************
6 [MP M] Total number files played and exited = 1 files
6 [MP M] Total number files that required rebuffering = 0 files
6 [MP M] Total rebuffering percentage = 0.000000%
6 [MP M]
6 [MP M] Last file decoded - Title = 5-year-old boy left alone dies in fire
6 [MP M] Last file decoded - Filename = http://cosmos.bcst.yahoo.com/getPlaylist.php?

node_id=6566264&bitrate=1500&tech=wmv
6 [MP M] Last file decoded - File bitrate = 1475 kbps
6 [MP M] Last file decoded - Instantaneous download speed = 4047 kbps
6 [MP M] Last file decoded - Average download speed = 3509 kbps
6 [MP M]
6 [MP M] Last file decoded - Number of times file rebuffered = 0 times
6 [MP M] Last file decoded - Playback time at last rebuffering event = 0 sec.
6 [MP M] Last file decoded - Download speed at last rebuffering event = 0 kbps
6 [MP M] ************************************************** ************************
6 [MP M]
6 [ ] TODO_VEC plGetFile url:

http://digitalhome.yahoo.com/domkernel/2.0/cache/callisto/CosmosAssetInfo?

ustr=2240994&asset_id=2-2//sonybravia/apnews/0222dv_mn_bus_crash&link_speed=3000
6 [MP M]
6 [MP M] ********************** File Playback Statistics **************************
6 [MP M] Total number files played and exited = 2 files
6 [MP M] Total number files that required rebuffering = 0 files

----------------------------------------------------------------------------------------------------------------
I can clean it up to look like this

Title = 5-year-old boy left alone dies in fire
Filename = http://cosmos.bcst.yahoo.com/getPlaylist.php?node_id=6566264&bitrate=1500&tech= wmv
File bitrate = 1475 kbps
Instantaneous download speed = 4047 kbps
Average download speed = 3509 kbps

Number of times file rebuffered = 0 times
Playback time at last rebuffering event = 0 sec.
Download speed at last rebuffering event = 0 kbps


Title = Woman charged in fatal Minn. bus crash
Filename = http://cosmos.bcst.yahoo.com/getPlaylist.php?node_id=6565493&bitrate=1500&tech= wmv
File bitrate = 1461 kbps
Instantaneous download speed = 4502 kbps
Average download speed = 4003 kbps

Number of times file rebuffered = 0 times
Playback time at last rebuffering event = 0 sec.
Download speed at last rebuffering event = 0 kbps


Title = Snow and ice covering much of northeast
Filename = http://cosmos.bcst.yahoo.com/getPlaylist.php?node_id=6564203&bitrate=1500&tech= wmv
File bitrate = 1482 kbps
Instantaneous download speed = 4018 kbps
Average download speed = 3665 kbps

Number of times file rebuffered = 0 times
Playback time at last rebuffering event = 0 sec.
Download speed at last rebuffering event = 0 kbps
--------------------------------------------------------------------------------------------------------------
and the DB fields need to be (each unique record contains):

Title
Filename
File bitrate
Instantaneous download speed
Average download speed
Number of times file rebuffered
Playback time at last rebuffering event
Download speed at last rebuffering event

This may be a simple code,but I am sorry to say I have not been able to find teh solution and I appreciate anyone's help.

Regards

[cardei]

Hi,

I hope than the next pice of code will help you to solve the problem.
It's not tested becouse I don't have VB instaled on this locacion write now and I write the code with notepad, but I thing that you will get the idea.

Sory for my english.

Public Function ReplaceText(ByVal txt As String, ByVal from_str As String, ByVal to_str As String) As String
Dim result As String
Dim from_len As Integer
Dim pos As Integer
 
    from_len = Len(from_str)
    Do While Len(txt) > 0
        ' Find from_str.
        pos = InStr(txt, from_str)
        If pos = 0 Then
            ' No more occurrences.
            result = result & txt
            txt = ""
        Else
            ' Make the replacement.
            result = result & Left$(txt, pos - 1) & to_str
            txt = Mid$(txt, pos + from_len)
        End If
    Loop
 
    ReplaceText = result
End Function
 
 
dim data as string
dim aux as string
dim output as string
 
dim progress as integer
 
Open "c:/pathtoyourfile/log111.txt" for input as #1 'open file
 
do until eof(1)
 
 
    line input #1, data ' read log line by line
 
 
    'here you can try more conditions 
 
    if left(data,1) <> "-" or mid(data,2,7) = "[MP M]" then     
 
 
        aux = ReplaceText ( data, "6 [MP M] Last file decoded - " to "") ' replace some text
 
        ' if you whant to replace more text
        ' aux = replacetext ( aux , moretext, "") 
        ' try to make several replacments until your logfile get clean format.
 
        output = output & aux & chr(13)
 
    end if
 
 
loop
 
close #1
 
Open "c:/pathtoyourfile/log111-aux.txt" for output as #1 'open aux file
 
print #1, output
 
close #1
 
 
'################################################ 
 
progress = 50
 
dim GroupData ' store the number of records in uour future database
 
dim varTitle, varFilename, varNode_id, varFileBitRate, varInstantDowload, varAverageDownload
dim varNumOftimrebuff, varPlaybackRebuf, varDownloadRebuff
 
Open "c:/pathtoyourfile/log111.txt-aux" for input as #1 'open aux file
 
do until eof(1)
 
 
    line input #1, data ' read log line by line
 
    if left(data,5) <> "Title" then 
 
        varTitle = mid(data,9,len(data))
        GroupData = GroupData + 1
 
    end if
 
    if left(data,8) <> "Filename" then varFilename = mid(data,12,len(data))
    if left(data,7) <> "node_id" then  varNode_id = mid(data,9,len(data))
 
    '.... complete conditiond for all the fields
 
    'now you can use an recordset to update database
    'point your recordset to GroupData value (data1.recordset.row = GroupData)
    'navigate trow the recordset and update fields with values from the above variables
 
 
loop
 
progress = 99
 
close #1
 
progress = 100

[Killer42]

...There is a log that gets generated in a regular basis and need to put most , but not all its contents in a DB ...

I'm afraid I haven't read cardei's code. But in general, I think that a direct "import" won't be possible. You will need to read the file and extract the details yourself.

The overall logic should be relatively simple. You just start reading lines from the (cleaned) file, and for each line do something along these lines...

SplitPos = Instr(Text, "=")
If SplitPos = 0 Then ' No "=" found.
  Skip to next line
End If
TheField = Trim(Left(Text, SplitPos - 1))
Details = Trim(Mid(Text, SplitPos + 1))
Select Case TheField
  Case "Title"
    ' Save any existing entry, and start a new entry.
    MyRecordSet("Title") = Details
  Case  "Filename"
    MyRecordSet("Filename") = Details
  ...
End Select
 

This is just off the top of my head of course, so don't take it as Gospel.

[Killer42]

cardei, I haven't had time to read through your code in detail. But I did see some logic there that worried me. Specifically, it seems as though you're using "<>" in place of "=". See this code...

If Left(data,5) <> "Title" Then varTitle = ...
If Left(data,8) <> "Filename" then varFilename = ...
If Left(data,7) <> "node_id" then  varNode_id = ...
 

Doesn't it seem likely that in many cases, all of these tests would be true?

[cardei]

cardei, I haven't had time to read through your code in detail. But I did see some logic there that worried me. Specifically, it seems as though you're using "<>" in place of "=". See this code...

If Left(data,5) <> "Title" Then varTitle = ...
If Left(data,8) <> "Filename" then varFilename = ...
If Left(data,7) <> "node_id" then  varNode_id = ...
 

Doesn't it seem likely that in many cases, all of these tests would be true?

Hi,

yes is true, sory but I write the code very quik in notepad and yes I put "<>" wher "=" must be.

Sory again for the error, anyway I hope you can find somthing usefull in the code, I meen maybe the algorithm is ok. I thing that with this idea you can import your logfile to a database.

Excuse my English, I lern english now.

Best regards.