Importing not-flat & partially delimited text file 2

[thevillageinn]

Is this even possible? I would figure with a little VBA and RegEx it can be done, but I have no idea where to begin.

Anyone that's done it before care to share a little advice, or snipets?

If it helps, I'm attempting to import a Web Server log into Access for some processing, but the configuration of the file isn't under my control, so I can't make changes, I can only work with what I'm given. Supposedly, it's in Common Log Format (CLF) and looks something like this example from apache.org:

127.0.0.1 - frank [10/Oct/2000:13:55:36 -0700] "GET /apache_pb.gif HTTP/1.0" 200 2326

 

[sha76]

Should be possible. My first step on things like this is to sit down with pen & paper (sometimes lots of paper!) & figure out the exact structure of the data I've got & what fields I need to feed it into. I then write pseudo-code for the process as comments in vba so that I can work through & code it a bit at a time. I think if you did something similar then it would highlight which bits you do know how to do & which bits you need help on, then you'll be able to come back with more specicfic questions.

Good luck!

"Your rock is eroding wrong." -Dogbert

 

[thevillageinn]

thanks for the initial response. I realize I was a bit vague, and I think your suggestion is a good one. Unfortunately, it's not the answer I wanted, I'd of course wanted to hear: "yes, it's possible, just use the following function." However, this will force me to grow as a programmer, and analytical thinker.

 

[BobStubbs]

This is a general-purpose function which I use in various places to parse a string into fields, where I may have a mixture of delimiters. This may give you some ideas.

The function takes three parameters:

strOneLine - string to be parsed
strSeparator - character to treat as the field separator
iFieldNum - which field to extract

Example:

msgbox FieldExtract("Fred; George; Harry",";",2)

... displays a message box containing the value George

Using this, you could break your string at the first square bracket, the first hyphen character, etc.

Note that you may need to do something about the double-quote " characters in the strings before you parse them.

Function FieldExtract(strOneLine As String, strSeparator As String, iFieldNum As Integer) As String
 
Dim iFieldStart As Integer
Dim iFieldLength As Integer
Dim J As Integer
Dim strOneField As String
Dim iPos As Integer
 
iPos = 0
 
'----------------------------------------------------
'- Check field number.  If this is less than 1,     -
'- return an error message.                         -
'----------------------------------------------------

If iFieldNum < 1 Then
    FieldExtract = "<Field Extract Error>"
    Exit Function
End If
 
'----------------------------------------------------
'- Find the starting position of the required field -
'----------------------------------------------------
If iFieldNum = 1 Then
    iFieldStart = 1
Else
    For J = 1 To iFieldNum - 1
        iPos = InStr(iPos + 1, strOneLine, strSeparator)
        If iPos = 0 Then
            FieldExtract = "<Field Extract Error>"
            Exit Function
        End If
    Next J
    iFieldStart = iPos + Len(strSeparator)
End If
 
'----------------------------------------------------
'- Find the length of the required field            -
'----------------------------------------------------
iPos = InStr(iPos + 1, strOneLine, strSeparator)
If iPos > 0 Then
    iFieldLength = iPos - iFieldStart
Else
    iFieldLength = Len(strOneLine) - iFieldStart + 1
End If
 
'----------------------------------------------------
'- Extract the field                                -
'----------------------------------------------------
strOneField = Mid$(strOneLine, iFieldStart, iFieldLength)
 
'----------------------------------------------------
'- For CSV data, remove double quotes round field   -
'- contents                                         -
'----------------------------------------------------
If Left$(strOneField, 1) = Chr$(34) Then
    strOneField = Right$(strOneField, Len(strOneField) - 1)
End If
If Right$(strOneField, 1) = Chr$(34) Then
    strOneField = Left$(strOneField, Len(strOneField) - 1)
End If
 
'----------------------------------------------------
'- Return the required field                        -
'----------------------------------------------------
FieldExtract = Trim(strOneField)
 
End Function

Bob Stubbs