Use Instr to pull image names out of Memo field

brentokc
brentokc used Ask the Experts™
on
I have a table containing an id column and a content column (memo field).  Each row may contain imbedded links to images, there may be more than 1 link in each row.  I need to extract the image name, it is either in jpg, bmp or gif format.  Here is an example of the material.  

Determine which of the following is worth the most money.
<img border="0" src="/pictures/clip_quarter.gif">   <img border="0" src="/pictures/clip_dimes.gif">    <img border="0" src="/pictures/clip_nickel.gif">
<img border="0" src="/pictures/clip_penny.gif">
The result needs to be a table with the id filed, which will not be unique, and 1 row for each image.  Any help will be sincerely appreciated.  This level of query is beyond my expertise.  Thanks Brent
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
Take a look at this preliminary solution and give me a feed back.

Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
    Dim TempStr As String
    TempStr =fnPicStr(rs!MemoField, "gif") & ""
    If TempStr ="" Then
       TempStr =fnPicStr(rs!MemoField, "bmp") & ""
       If TempStr ="" Then
          fnPictureFileName="Link doesn't contain any gif or bmp file."
       Else
         fnPictureFileName=TempStr
       End If
    Else
        fnPictureFileName=TempStr
    End If
fnPictureFileName=

rs.MoveNext
Loop
End If
rs.Close

End If

Function fnPicStr(MemoVal As String, FormatVal As String) As String
' this function is being worked at.  It is not done.
If FormatVal ="gif" Then
   fnPicStr="Has Located a gif"
Elseif FormatVal ="bmp" Then
  fnPicStr="Has Located a bmp"
Else
  fnPicStr=""
End IF

End Function

Mike
Mike EghtebasDatabase and Application Developer

Commented:
Opps...

Public Function fnPictureFileName(IdVal As Long) As String
.
.
.
End Function '<---- *********this line is missing in my last post****************

Function fnPicStr(MemoVal As String, FormatVal As String) As String
.
.
.
End Function

Commented:
Mike,

Correct me if I'm wrong but your not actually searching the memo field for any images (I know your just playing with an idea at the moment).

In the below I am assuming that all images start with a /.

You would need to change your function to:

Function fnPicStr(MemoVal As String, FormatVal As String) As String
' this function is being worked at.  It is not done.

If instr(1, FormatVal. ".gif") > 0 Then
  fnPicStr = mid(FormatVal, instr(1, FormatVal. "/"), instr(1, FormatVal. "gif") +3) 'plus three as the instr gives you the first value (could be 4 though)

'AND THE SAME FOR THE OTHERS
Elseif FormatVal ="bmp" Then
 fnPicStr="Has Located a bmp"
Else
 fnPicStr=""
End IF

End Function


I haven't thrown in looping through to find more than one in a memo field.
I just thought I would throw in a quick comment.

Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Mike EghtebasDatabase and Application Developer

Commented:
Hi Drizzt95,

You are right.  Function fnPicStr(MemoVal As String, FormatVal As String) As String is not ready yet.

Do you want do a teamwork on it?  I was plannig to write it this evening, unless you choose to coordinate it with my code to help out brentokc,

Regards,

Mike

Commented:
Mike,

I'm happy to let you have the lead on this one, I'll just poke my head in occassionaly and see how your going.

I've just moved house so I don't have the net connected yet (it should have been a priority) so I'm stuck just using the net at work, which means I'm out for the weekend.

Thanks
Ian
Mike EghtebasDatabase and Application Developer

Commented:
Revised...

Assumption: All file names starts with word "Clip" and end with either .gif or .bmp
There is a table called tImageFilesName with id (Number/Long) and ImageFile (text)
------------
Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim gif As Integer, bmp As Integer, leftChar As Integer, i As Integer
       TempStr = rs!MemoField & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"Clip")>0 then
           'remove all leading string before first "Clip"
           TempStr =Mid(TempStr,Instr(TempStr,"Clip"))
           ' check to see either .gif or .bmp exists in the remaining string
           gif = InStr(TempStr,".gif")+4: bmp = InStr(TempStr,".bmp")+4
           If gif >4 or bmp>4 then
               leftChar =IIF(gif>bmp,bmp,gif)
               TempStr = Left(TempStr,leftChar)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,leftChar+1) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function

This should do it.

Mike

Commented:
I wanted to mess with regular expressions

Public Function ExtractPicture()
    'assumes that src is at the end of the tag
    'returns the whole path
    'Needs Regular Expressions  Library
    Dim lObjRegexp As New regexp
    Dim lObjMatches As MatchCollection
    Dim lobjMatch As Match
    Dim rs As DAO.Recordset
    Dim strPath As String
   
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
   
    Do While Not rs.EOF
   
        lObjRegexp.Global = True
        lObjRegexp.Pattern = "src=[^>]+"
        lObjRegexp.IgnoreCase = True
       
        Set lObjMatches = lObjRegexp.Execute(rs!Content)
       
        For Each lobjMatch In lObjMatches
            If Len(Trim(lobjMatch.Value)) > 0 Then
                strPath = Right(lobjMatch.Value, Len(lobjMatch.Value) - 4)
                Call CurrentDb.Execute("INSERT INTO Table3 (ID, PicturePath) SELECT " & rs!ID & ", " & strPath)
                'Debug.Print "INSERT INTO Table3 (ID, PicturePath) SELECT " & rs!ID & ", " & strPath
            End If
        Next
    rs.MoveNext
    Loop
End Function

Chris

Author

Commented:
eghtebas - Sorry, guess I used a bad example, all the images do not start with clip and there are also jpg images in the memo field <img border="0" src="pictures/10long2411.gif">.  The table name is QuestionContent and the column is also QuestionContent, the id column is QuestionID.  I think I made the substitutions in the right places.  I created the table as you noted.  I pasted your code into the SQL window and got and "invalid SQL statement".  Added the Select and then received a "The SELECT statement includes a reserved word or argument name that is misspelled or missing, or the punctuation is incorrect."  When I choose OK String is highlighted.  You should probably assume I am ignorant of Access, since what you are doing is way beyond what I understand.  I'm not sure a vowel will help, believe I need the whole alphabet.  Also have just been told I have to leave town in the morning and won't be able to be on line until Sunday night.  Guess I should add some points to this deal, huh.  Thanks Brent

SELECT Public Function fnQuestionContent(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From QuestionContent Where QuestionID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim gif As Integer, bmp As Integer, leftChar As Integer, i As Integer
       TempStr = rs!QuestionContent.QuestionContent & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"Clip")>0 then
           'remove all leading string before first "Clip"
           TempStr =Mid(TempStr,Instr(TempStr,"Clip"))
           ' check to see either .gif or .bmp exists in the remaining string
           gif = InStr(TempStr,".gif")+4: bmp = InStr(TempStr,".bmp")+4
           If gif >4 or bmp>4 then
               leftChar =IIF(gif>bmp,bmp,gif)
               TempStr = Left(TempStr,leftChar)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,leftChar+1) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function
Mike EghtebasDatabase and Application Developer

Commented:
Could we assume they are all in a folder <pictures/>?   I will serach for pictures/ string knowing that file name starts after this string.

Don't worry about the points.  First we need to get it resoved.  I will modify to include any jpg and post it over the weekend.

Have a nice trip,

Mike

Author

Commented:
Yes, that assumption is correct.

Thanks, the trip is to a park with no phone, no tv and no internet, its either heaven or hell - take your choice.  Actually it will be nice, good time of year to go, fall in southeast Oklahoma is nice.

Brent
Mike EghtebasDatabase and Application Developer

Commented:
That is exciting.  Good for you.

Mike

Author

Commented:
Mike -

Does it look like you will have time to help me with this?

Thanks
Brent
Mike EghtebasDatabase and Application Developer

Commented:
I will work at it.  Thank you for reminding me.  Mike
Mike EghtebasDatabase and Application Developer

Commented:
Assumptions:

1. All file names are in folder called: pictures/
2. Always "pictures/" is followed by a file name (any extention type)
3. And, file formats (any kind) all have three letter extention after .

like:  .gif   .bmp   .pcx  and so on.

There is a table called tImageFilesName with id (Number/Long) and ImageFile (text)
------------
Public Function fnPictureFileName(IdVal As Long) As String
Dim rs 'As DOA.RecordSet
Set rs=CurrentDB.OpenRecordSet("Select * From MyTable Where PictureID=" & IdVal)
If rs.RecordCOunt>0 Then
rs.MoveFirst
Do Until rs.EOF
   
        Dim TempStr As String
        Dim leftChar As Integer, i As Integer,FileName As String
       TempStr = rs!MemoField & ""
10: For i=1 to 10 '<----  **** 10 file names per memo**********
        If Instr(TempStr,"pictures/")>0 then
           'remove all leading string to the end of first "pictures/"
           TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
           ' locate first dot after pictures/
           FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i
rs.MoveNext
Loop
End If
rs.Close

End If

End Function

Sorry for the delay.

Mike

Author

Commented:
Mike -

Delay, I don't care about any delay; I was potentially looking at a cut and paste operation - this is a life saver.  I will start using it as soon as I get in the office tomorrow.  Will get back to you then.

Thanks,
Brent
Mike EghtebasDatabase and Application Developer

Commented:
correction... replace:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr

with:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & FileName

Mike

Author

Commented:
Mike -
I have copied the code into a module - when I try and compile I get an "else without if" error at the following else.
GoTo 10
        Else
           Exit For

After a successful compile I assume I can run the module by clicking on !run in the database window and it will populate the tImageFilesName table.  I substituted QuestionContent for MyTable and QuestionID for PictureID, where those the only changes I needed to make?

I have never worked with modules before so your guidance is much appreciated.
Brent
Mike EghtebasDatabase and Application Developer

Commented:
After creating a new table called tImageFilesName with id (Number/Long) and ImageFile (text), add a new button on your form.  In OnClick event of this button include.
(Note: In this code, change Table1 which has MemeoFildName and ID as required):

Dim rst 'As DOA.RecordSet
CurrentDB.Execute "Delete * From tImageFilesName"
Set rst=CurrentDB.OpenRecordSet("Select ID, MemeoFildName From Table1")
If rst.RecordCount>0 Then
rst.movefirst
Do Until rst.EOF
    PictureFileName rst!ID, rst!MemeoFildName
rst.MoveNext
Loop
End If
rst.close
'------------------
And, in a standard module, under module tab, paste:

Public Sub PictureFileName(IdVal As Long, MemoVar As String)
       Dim TempStr As String, i As Integer,FileName As String
       TempStr = MemoVar
10:  For i=1 to 10 '<----  **** 10 file names per memo**********
           If Instr(TempStr,"pictures/")>0 then
              'remove all leading string to the end of first "pictures/"
               TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
              ' locate first dot after pictures/
              FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
           End If
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i

End Sub

'---------
Now, when you click on the button discussed in the beginning of this post, the existing content of tImageFilesName will be refreshed.

Mike

Author

Commented:
Mike -
Have done all you indicated in last post - button on form seems to work - but still am receiving the compile error - else without if.  
"GoTo 10
        Else
           Exit For
End If
      Next i" - the else is highlighted.
Brent
Mike EghtebasDatabase and Application Developer

Commented:
use:

Public Sub PictureFileName(IdVal As Long, MemoVar As String)
       Dim TempStr As String, i As Integer,FileName As String
       TempStr = MemoVar
10:  For i=1 to 10 '<----  **** 10 file names per memo**********
           If Instr(TempStr,"pictures/")>0 then
              'remove all leading string to the end of first "pictures/"
               TempStr =Mid(TempStr,Instr(TempStr,"pictures/")+9)
              ' locate first dot after pictures/
              FileName = Left(TempStr,InStr(TempStr,".")+3)
               'add image file name along with id value to table tImageFilesName
               CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
              TempStr=Mid(TempStr,InStr(TempStr,".")+4) & ""
              GoTo 10
        Else
           Exit For
        End IF
      Next i

End Sub

sorry,

Author

Commented:
Mike -
The change returns the first instance of the images, but it also returns the following error:  Runtime error '3075": Syntax error (missing operator) in query expression 'clip_quarter.gif"><img border="0" src="/pictures/clip_dimes.gif"><img border="0" src="/pictures/clip_nickel.gif"><img border="0" src="/pictures'clip_penny.gif">'.  When I choose debug the following line is highlighted: "CurrentDb.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr".

The info referenced in the runtime error dialog is from questionid 54, the first question that contains any images, and it contains the 4 gifs listed, in the memo field(QuestionContent.QuestionContent).  That is, all in the same field.
Brent
Mike EghtebasDatabase and Application Developer

Commented:
Change:
               
.
.
CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr
TempStr=Mid(TempStr,InStr(TempStr,".")+3) & ""
GoTo 10
.
.

If still didn't work, send a zipped version to eghtebas at hotmail dot com

Mike


Author

Commented:
Mike -

Change didn't make any difference - removed extraneous material and sent you the zipped copy - it is in Access 2003, let me know if that is not alright.
Thanks
Brent
Database and Application Developer
Commented:
Change:

CurrentDB.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", " & TempStr

To:

CurrentDb.Execute "Insert Into tImageFilesName (Id, ImageFile) Select " & IdVal & ", '" & FileName & "'"

Mike

Author

Commented:
Outstanding - it works great - the amount of time saved is extraordinary.

Thanks
Brent

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial