Import txt file with vertical fields into access db | Newbie | | Join Date: Feb 2008
Posts: 1
| | |
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
|  | Newbie | | Join Date: Feb 2008 Location: Spain
Posts: 5
| | | re: Import txt file with vertical fields into access db
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
| | Moderator | | Join Date: Oct 2006 Location: Australia
Posts: 7,748
| | | re: Import txt file with vertical fields into access db
Quote:
Originally Posted by Roberto Mora ...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.
| | Moderator | | Join Date: Oct 2006 Location: Australia
Posts: 7,748
| | | re: Import txt file with vertical fields into access db
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?
| | Newbie | | Join Date: Feb 2008 Location: Spain
Posts: 5
| | | re: Import txt file with vertical fields into access db
Quote:
Originally Posted by 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?
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.
|  |
Similar Visual Basic 4 / 5 / 6 bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
