Import txt file with vertical fields into access db

**cardei** · Mar 1 '08, 02:28 AM

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.

Code:

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** · Mar 3 '08, 01:46 AM

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...

[CODE=vb]
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("Ti tle") = Details
Case "Filename"
MyRecordSet("Fi lename") = Details
...
End Select
[/CODE]This is just off the top of my head of course, so don't take it as Gospel.

**Killer42** · Mar 3 '08, 01:55 AM

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...

Code:

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

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

**cardei** · Mar 7 '08, 03:45 PM

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...

Code:

If [B]Left(data,5) <> "Title"[/B] Then varTitle = ...
If [B]Left(data,8) <> "Filename"[/B] then varFilename = ...
If [B]Left(data,7) <> "node_id"[/B] 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.

Import txt file with vertical fields into access db

Import txt file with vertical fields into access db

Comment

Comment

Comment

Comment