By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,241 Members | 760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,241 IT Pros & Developers. It's quick & easy.

Import txt file with vertical fields into access db

P: 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
Feb 29 '08 #1
Share this Question
Share on Google+
4 Replies


cardei
P: 5
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.



Expand|Select|Wrap|Line Numbers
  1. Public Function ReplaceText(ByVal txt As String, ByVal from_str As String, ByVal to_str As String) As String
  2. Dim result As String
  3. Dim from_len As Integer
  4. Dim pos As Integer
  5.  
  6.     from_len = Len(from_str)
  7.     Do While Len(txt) > 0
  8.         ' Find from_str.
  9.         pos = InStr(txt, from_str)
  10.         If pos = 0 Then
  11.             ' No more occurrences.
  12.             result = result & txt
  13.             txt = ""
  14.         Else
  15.             ' Make the replacement.
  16.             result = result & Left$(txt, pos - 1) & to_str
  17.             txt = Mid$(txt, pos + from_len)
  18.         End If
  19.     Loop
  20.  
  21.     ReplaceText = result
  22. End Function
  23.  
  24.  
  25. dim data as string
  26. dim aux as string
  27. dim output as string
  28.  
  29. dim progress as integer
  30.  
  31. Open "c:/pathtoyourfile/log111.txt" for input as #1 'open file
  32.  
  33. do until eof(1)
  34.  
  35.  
  36.     line input #1, data ' read log line by line
  37.  
  38.  
  39.     'here you can try more conditions 
  40.  
  41.     if left(data,1) <> "-" or mid(data,2,7) = "[MP M]" then     
  42.  
  43.  
  44.         aux = ReplaceText ( data, "6 [MP M] Last file decoded - " to "") ' replace some text
  45.  
  46.         ' if you whant to replace more text
  47.         ' aux = replacetext ( aux , moretext, "") 
  48.         ' try to make several replacments until your logfile get clean format.
  49.  
  50.         output = output & aux & chr(13)
  51.  
  52.     end if
  53.  
  54.  
  55. loop
  56.  
  57. close #1
  58.  
  59. Open "c:/pathtoyourfile/log111-aux.txt" for output as #1 'open aux file
  60.  
  61. print #1, output
  62.  
  63. close #1
  64.  
  65.  
  66. '################################################ 
  67.  
  68. progress = 50
  69.  
  70. dim GroupData ' store the number of records in uour future database
  71.  
  72. dim varTitle, varFilename, varNode_id, varFileBitRate, varInstantDowload, varAverageDownload
  73. dim varNumOftimrebuff, varPlaybackRebuf, varDownloadRebuff
  74.  
  75. Open "c:/pathtoyourfile/log111.txt-aux" for input as #1 'open aux file
  76.  
  77. do until eof(1)
  78.  
  79.  
  80.     line input #1, data ' read log line by line
  81.  
  82.     if left(data,5) <> "Title" then 
  83.  
  84.         varTitle = mid(data,9,len(data))
  85.         GroupData = GroupData + 1
  86.  
  87.     end if
  88.  
  89.     if left(data,8) <> "Filename" then varFilename = mid(data,12,len(data))
  90.     if left(data,7) <> "node_id" then  varNode_id = mid(data,9,len(data))
  91.  
  92.     '.... complete conditiond for all the fields
  93.  
  94.     'now you can use an recordset to update database
  95.     'point your recordset to GroupData value (data1.recordset.row = GroupData)
  96.     'navigate trow the recordset and update fields with values from the above variables
  97.  
  98.  
  99. loop
  100.  
  101. progress = 99
  102.  
  103. close #1
  104.  
  105. progress = 100
Mar 1 '08 #2

Expert 5K+
P: 8,434
...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...

Expand|Select|Wrap|Line Numbers
  1. SplitPos = Instr(Text, "=")
  2. If SplitPos = 0 Then ' No "=" found.
  3.   Skip to next line
  4. End If
  5. TheField = Trim(Left(Text, SplitPos - 1))
  6. Details = Trim(Mid(Text, SplitPos + 1))
  7. Select Case TheField
  8.   Case "Title"
  9.     ' Save any existing entry, and start a new entry.
  10.     MyRecordSet("Title") = Details
  11.   Case  "Filename"
  12.     MyRecordSet("Filename") = Details
  13.   ...
  14. End Select
  15.  
This is just off the top of my head of course, so don't take it as Gospel.
Mar 3 '08 #3

Expert 5K+
P: 8,434
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...

Expand|Select|Wrap|Line Numbers
  1. If Left(data,5) <> "Title" Then varTitle = ...
  2. If Left(data,8) <> "Filename" then varFilename = ...
  3. If Left(data,7) <> "node_id" then  varNode_id = ...
  4.  
Doesn't it seem likely that in many cases, all of these tests would be true?
Mar 3 '08 #4

cardei
P: 5
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...

Expand|Select|Wrap|Line Numbers
  1. If Left(data,5) <> "Title" Then varTitle = ...
  2. If Left(data,8) <> "Filename" then varFilename = ...
  3. If Left(data,7) <> "node_id" then  varNode_id = ...
  4.  
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.
Mar 7 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.