Data Manipulation XLS or MDB ~ Substring Extraction

Can you please clarify how far you have gotton on this on your own and what your specific issue is here?

Thanks

JeffCoachman

Sorry,

My post should have been:

I am having trouble determining what your question is here?
How to extract the data?
How to extract the image?
How to import the data?
...etc

Can you please clarify how far you have gotten on this on your own and what your specific issue is here?

Thanks

JeffCoachman

Answer #1  Yes, 2 data types.

Answer #2 The objective is to extract just the MPG file name from both XLS dumps. One is a log file; another is a content catalog. Both will be imported into MS Access for in order to generate a "Number of Plays" query. I had this in place, but the source data structures changed slightly fouling the reports. Reiterating, it would be nice to be able to boil both XLSes down to the point where only the MPG file name is listed. Anything else is extraneous including the CCS1! & CCS2!. Thanks again.      

first, import the .xls file in an access table
create a field (MPG) to hold the extracted .mpg file

to populate the field, use an update query

Update tableX set [MPG]=ExtractMPG([FieldName])

paste this codes in a regular module

Function ExtractMPG(sFld As String)
Dim sDel As String
If Len(sFld & "") = 0 Then ExtractMPG = "": Exit Function

If InStr(sFld, " ") Then sDel = " "
If InStr(sFld, "!") Then sDel = "!"

If Len(sDel & "") > 0 Then
   
    ExtractMPG = Mid(sFld, InStr(sFld, sDel) + 1)
    Else
    If Right(sFld, 4) = ".mpg" Then
        ExtractMPG = sFld
        Else
        ExtractMPG = ""
    End If
End If

End Function

Data is imported, MPG field created. Could you offer additional detail with respect to the steps required  to create the update query and enter the function in MS Access? Thanks Capricorn1.  

from the database objects window
select Modules > new

in the VBA window that will open copy and paste this

'*****
Function ExtractMPG(sFld As String)
Dim sDel As String
If Len(sFld & "") = 0 Then ExtractMPG = "": Exit Function

If InStr(sFld, " ") Then sDel = " "
If InStr(sFld, "!") Then sDel = "!"

If Len(sDel & "") > 0 Then
   
    ExtractMPG = Mid(sFld, InStr(sFld, sDel) + 1)
    Else
    If Right(sFld, 4) = ".mpg" Then
        ExtractMPG = sFld
        Else
        ExtractMPG = ""
    End If
End If

End Function
'*******


to create an update query
select Queries > New > Design View >Ok

select the Table then click Add, Close
see the image below

Getting Closer; Function Error when running query...please advise. Thanks!

first, make sure that you did not save the module as ExtractMPG, if you did rename it to something different than the function name, like modExtractMPG

second, in your query
change this

ExtractMPG("Asset_Namempg")

to

ExtractMPG([Asset_Namempg])

Success with the first data set; testing second XLS data set. Thanks Capricorn1  

Seemed to miss under the following conditions:

* When the MPG file began with "0" and did not have an accompanying JPG
* When both an MPG and JPG existed and the JPG presented second in the cell.

Thoughts? Thanks!!

modify the function like this


Function ExtractMPG(sFld As String)
Dim sDel As String

sFld = Trim(sFld)    '<< ADD THIS LINE

If Len(sFld & "") = 0 Then ExtractMPG = "": Exit Function

If InStr(sFld, " ") Then sDel = " "
If InStr(sFld, "!") Then sDel = "!"

If Len(sDel & "") > 0 Then
   
    ExtractMPG = Mid(sFld, InStr(sFld, sDel) + 1)
    Else
    If Right(sFld, 4) = ".mpg" Then
        ExtractMPG = sFld
        Else
        ExtractMPG = ""
    End If
End If

End Function

No Change. Hmmm....

post the excel file. upload it here

Data Set #2 Upload; Thanks for your diligence.

see this sample db

open table VOD_Content_Report, close table

run Query1, open table VOD_Content_Report again

Capricorn1, did you notice a few JPGs were returned instead of the MPGs? Thanks again, your assistance is really appreciated!

now, that is the reason i ask in my first post about the data format.

Sorry if I was not clear earlier. The JPG / MPG order and formats are determined by the application.