Text edit, Excel, Automator or none of them?

nellbern · April 27, 2009, 7:10pm

I have an Excel document with a list of 3 letter code file names.
Based on that list I need to match the 3 letter code with the physical file .jpg in the server & delete them. One obstacle I have is that the file names in the server do not match exactly with the contents of the Excel document.

For example:
Excel document
ABC
BBD
MMN
GHY
JKO

Server File name
II_ABC1.jpg
II_ABC2.jpg
II_ABC3.jpg
II_ BBD1.jpg
II_ BBD2.jpg
II_ BBD3.jpg
II_ BBD4.jpg
II_ MMN1.jpg
II_ MMN2.jpg
II_GHY1.jpg
II_GHY2.jpg
II_GHY3.jpg
II_GHY4.jpg
II_GHY5.jpg
II_ JKO.jpg

I don’t know if is it better to copy the contents of the Excel paste it into Text Edit or Automator. That way I’ll avoid using Excel. Can Automator can do this or do I need to use applescript. I’m still learning applescript. Any suggestions?

Nellbern

Adam_Bell · April 27, 2009, 9:26pm

You don’t tell us enough to do the whole job, but this script will find the file names with the key letters in their names:

set Doc to "ABC
BBD
MMN
GHY
JKO" -- just return delimited text assuming export of Excel doc with names in columns.

set Serv to "II_ABC1.jpg
II_ABC2.jpg
II_ABC3.jpg
II_ BBD1.jpg
II_ BBD2.jpg
II_ BBD3.jpg
II_ BBD4.jpg
II_ MMN1.jpg
II_ MMN2.jpg
II_GHY1.jpg
II_GHY2.jpg
II_GHY3.jpg
II_GHY4.jpg
II_GHY5.jpg
II_ JKO.jpg" -- just a way to get the given list in. You would have to extract the names from the server as a list.

set SF to paragraphs of Serv -- i.e. a list of the file names on the Server; get SF from the server.

set F to {}
set R to paragraphs of Doc -- makes an AppleScript list
repeat with aP in R
	repeat with S in SF
		if aP is in S then set end of F to contents of S
	end repeat
end repeat

nellbern · April 27, 2009, 9:54pm

Thank You Adam for your reply. Basically I have a list of 3 letter codes in Excel that either I can paste the contents into Script Editor as a record {“ABC”, “BBD”, “MNN”} or a in a TextEdit document. I have a folder in the server containing thousands of .jpgs files. I need to compare the 3 letter code with the jpgs that are located in the server & if the 3 letter code matches with file name of the jpg containing the 3 letter code then move the jpg from the server to a different location.

I have around 550 items with a 3 letter code. I don’t want to do this manually because I can easily delete the incorrect .jpg from the server & obviously there is no need to perform a spotlight search 550 times. Believe me I’m trying to achieve this by myself but I’m throwing the towel because I know what i want but don’t know how

mikerickson · April 27, 2009, 10:15pm

If your problem is getting the data out of Excel and into the Chr(13) delimited string, could you give the workbook name, sheet name and cell addresses where the data is in Excel. If that is known a script can be written to get it out. Also are you using Excel 2004 or 2008?

nellbern · April 27, 2009, 10:30pm

mikerickson,
To be honest I just Excel to copy & paste the info to another document. That’s why I was thinking to paste the contents of the Excel document into a TextEdit document because I think (you will know better than me) is going to be easier to extract that the content.

The Excel info is:
Excel 2004 Mac version 11.5
child and appendix.xls
sheet 1
cells 1 to 544

mikerickson · April 27, 2009, 11:39pm

Since you are using 2004, you have VBA avaliable to you. Open the VB editor, insert a module (not a class module) and paste this into the window.

[code]Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
Dim i As Long, j As Long
With compareRange.Parent
Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range(“a1”)))
End With
If compareRange Is Nothing Then Exit Function
If stringsRange Is Nothing Then Set stringsRange = compareRange
Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
stringsRange.Column - compareRange.Column)

    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = mid(ConcatIf, Len(Delimiter) + 1)
End Function[/code]

Assuming that you mean cells A1:A144, select cell B1 and enter =ConcatIf(A:A,“<>”,A:A,CHAR(13)) into the formula bar. Press return and then the CR delimited string will be in cell B1.

nellbern · April 27, 2009, 11:43pm

mikerickson,
I’m working on a Macintosh & I don’t have VBA avaliable.

nellbern · April 28, 2009, 12:53am

This is what I have so far & it needs to be fine tuned if possible. I copied the contents of the Excel file into ScriptEditor in the section that says ThreeLetterCodeList. I don’t know how to make the script compare the file names of the jpg folder with the list of 3 letter code. Also I was thinking to delete the items that match but I decided to moved them.

set JPG_Str to "Please locate the folder that contains your JPG files..."
set JPG_Folder to (choose folder with prompt JPG_Str) as text
set JPG_Contents to list folder JPG_Folder without invisibles
set JPG_Roots to {}
repeat with ThisFile in JPG_Contents
   set end of JPG_Roots to GetTextItem(ThisFile as text, ".", 1)
end repeat

-->I need to compare the 3 letter code with the choosen folder of .jpgs file names in server. If the 3 letter code matches with part of the jpg file name then move the .jpg to folder in the desktop called Moved files. Below is the list of the 3 letter code names. Example : AAN     (Names of the jpg files that matches with the 3 letter code on the server) II_AAN1.jpg, II_AAN2.jpg

set ThreeLetterCodeList to {"AAN", "ABH", "ABT", "AFE", "AGP", "AHM", "AIL", "ALI", "ALV", "AME", "APE", "APP", "APS", "ARE", "ARM", "ART", "ARV", "ARY", "ASH", "ATL", "ATR", "AZR", "AZS", "BAR", "BBL", "BBO", "BBV", "BCC", "BCH", "BCN", "BDA", "BDD", "BDH", "BDY", "BEH", "BEV", "BGA", "BHG", "BHS", "BIO", "BKS", "BLK", "BLM", "BLQ", "BLR", "BLS", "BNT", "BOB", "BOC", "BOH", "BPC", "BRB", "BRY", "BSR", "BSS", "BTF", "BTV", "BVC", "BVP", "BWO", "BWR", "BZY", "CAI", "CBC", "CBF", "CBV", "CCC", "CCE", "CCG", "CCS", "CDP", "CEB", "CEC", "CEM", "CFA", "CFL", "CFZ", "CGH", "CHB", "CHO", "CHS", "CIB", "CIK", "CIM", "CIX", "CLD", "CLH", "CLR", "CMH", "CMO", "CNC", "COB", "COD", "COQ", "COT", "COX", "COZ", "CPE", "CPW", "CQP", "CRV", "CSE", "CTB", "CTC", "CTN", "CUV", "CVA", "CVE", "CVO", "CXM", "CYO", "DAS", "DBC", "DBR", "DEA", "DEL", "DFH", "DIV", "DKH", "DOU", "DPR", "DRR", "DVS", "EAG", "EBY", "ECO", "EDB", "EDG", "EG2", "EHA", "EHL", "EHN", "EHR", "EHS", "ELO", "EME", "ER1", "ERC", "ESS", "EXV", "FHS", "FLR", "FON", "FPC", "FRN", "FRR", "FSP", "FWB", "FWM", "FWT", "FWW", "FWY", "FXH", "FXT", "GBC", "GBO", "GCP", "GG1", "GHE", "GKA", "GMW", "GOH", "Gra", "GRB", "GSB", "GSL", "GTL", "GUV", "GVT", "HAB", "HAR", "HBE", "HBR", "HDL", "HDW", "HEM", "HFL", "HGI", "HGL", "HHP", "HHR", "HHS", "HHV", "HIF", "HII", "HLG", "HLS", "HMA", "HOB", "HOL", "HRF", "HSB", "HSJ", "HTL", "HUE", "IAK", "IDD", "IDP", "IGA", "IGP", "IGR", "ILK", "INB", "IND", "INV", "IOH", "IPI", "IPK", "IPL", "IPS", "IRH", "ISC", "ISL", "IST", "ITS", "JAM", "JGH", "JLH", "KAR", "KBC", "KBI", "KBL", "KBY", "KCE", "KCL", "KCT", "KFI", "KIH", "KJK", "KKS", "KOU", "KPA", "KUH", "LAC", "LAL", "LAS", "LAY", "LBC", "LBO", "LCQ", "LDH", "LEH", "LGS", "LGU", "LII", "LKG", "LKN", "LLE", "LLG", "LLR", "LMK", "LNS", "LOM", "LPQ", "LPT", "LRH", "LSM", "LSV", "LSW", "LVT", "LYC", "MAV", "MBB", "MBK", "MCL", "MEE", "MGK", "MIN", "MKC", "MLH", "MMD", "MNA", "MNH", "MNP", "MPC", "MPO", "MRL", "MRQ", "MRR", "MSC", "MSM", "MTG", "MTO", "MTT", "MUM", "NAE", "NAR", "NCC", "NHI", "NIR", "NOT", "NPP", "NPR", "NRJ", "NSA", "NVE", "NWB", "OAC", "OBC", "OBH", "OCH", "OCR", "OCS", "OCY", "OGT", "OHI", "OIB", "OLH", "ONP", "OTI", "OVL", "OVW", "PAL", "PAP", "PBN", "PBR", "PBS", "PCB", "PCE", "PCV", "PEB", "PES", "PFT", "PGA", "PHH", "PHR", "PIN", "PLE", "PLN", "PLP", "PLR", "PMA", "PMB", "POB", "POK", "POW", "PPE", "PRD", "PRG", "PRT", "PRU", "PSB", "PSD", "PTL", "PTR", "PTY", "PUH", "PVB", "PVO", "PWR", "PWT", "PYA", "PYB", "PZP", "RAM", "RBA", "RBM", "RD1", "REM", "REN", "RHA", "RIA", "RIP", "RMR", "RMT", "ROY", "ROZ", "RPB", "RPD", "RRS", "RRT", "RS2", "RUK", "RVE", "RVV", "RWF", "RWP", "RYH", "RYK", "RYR", "RYV", "SA2", "SAB", "SAG", "SAN", "SB3", "SBC", "SBM", "SBO", "SBQ", "SBS", "SC2", "SCG", "SCK", "SCM", "SCV", "SDG", "SDV", "SEI", "SES", "SET", "SEV", "SEW", "SFR", "SGT", "SHD", "SHE", "SHF", "SHI", "SHO", "SHX", "SIB", "SKD", "SKL", "SLT", "SLV", "SMA", "SMF", "SMQ", "SMX", "SMZ", "SNB", "SND", "SNH", "SNL", "SNO", "SNY", "SPK", "SPQ", "SPS", "SQA", "SRC", "SRE", "SRH", "SRO", "SSH", "SSL", "SSN", "SSP", "STG", "STK", "STQ", "STS", "STT", "SUR", "SUT", "SVB", "SVO", "SVR", "SWB", "SWC", "SWE", "SWT", "TAB", "TAC", "TAG", "TBV", "TCM", "TCS", "TCT", "TCX", "TDN", "TDU", "TJH", "TKR", "TLY", "TMC", "TOL", "TPS", "TPX", "TRI", "TRR", "TSV", "TVI", "TWP", "UCB", "UGA", "UGS", "UJG", "UMH", "VAI", "VAX", "VBG", "VBS", "VCO", "VCR", "VDB", "VDS", "VEN", "VKW", "VLA", "VLB", "VLD", "VLG", "VMT", "VOG", "VPI", "VPK", "VPR", "VSE", "VTF", "WAK", "WAM", "WAV", "WAW", "WBE", "WBR", "WCB", "WDU", "WEC", "WEK", "WEL", "WHA", "WJM", "WKN", "WLB", "WLC", "WLO", "WLP", "WLV", "WMA", "WMD", "WME", "WMM", "WMV", "WMW", "WOR", "WOS", "WPB", "WPC", "WPE", "WPK", "WRA", "WRN", "WSA", "WSC", "WSG", "WSH", "WSR", "WSS", "WTC", "WWA", "WWT", "YL1", "YL2"}

set Warning_Str to "The following operation will move files to a folder in a desktop called Moved Files from Resort Images!" & return & return & "Do you wish to proceed anyway?"
set DWarning to display dialog Warning_Str buttons {"No", "Yes"} default button 2

if button returned of DWarning is "NO" then
   beep 4
   display dialog "You just Cancel." with icon caution buttons {"Cancel"}
end if


if button returned of DWarning is "Yes" then
   tell application "Finder"
       if (folder "Moved Files from Resort Images") = false then
           make new folder at desktop with properties {name:"Moved Files from Resort Images"}
       end if
   end tell
end if




on GetTextItem(ThisString, ThisDelim, ThisItem)
   -- ThisString -> String to look in
   -- ThisDelim -> Text element that delimit the string
   -- ThisItem -> Number of the element to return
   copy the text item delimiters to OldDelims
   set the text item delimiters to ThisDelim
   if class of ThisItem is list then
       set fromItem to (item 1 of ThisItem) as integer
       set toitem to (item 2 of ThisItem) as integer
       set arrItem to (text items fromItem thru toitem of ThisString)
   else
       set arrItem to every text item of ThisString
   end if
   set the text item delimiters to OldDelims
   
    if class of ThisItem is list then
       return arrItem as text
   else
       if ThisItem is not 0 then
           return (item ThisItem of arrItem) as text
       else
           return arrItem -- return every items
       end if
   end if
end GetTextItem
try
on error theErrorMessage --theErrorNumber
   beep 3
   display dialog "Error:  " & theErrorMessage & theErrorNumber & return buttons {"OK"} default button 1 with icon caution
end try

nellbern · April 28, 2009, 10:25pm

A very helpful guy suggested me another approach to achieve what I want. The problem I’m having that the script it is not deleting the files. In the even log window I can see is finding the matches even though in the script there is line to delete them & I’m getting an error «script» doesn’t understand the writeToFile message. What is it that I’m missing?

--use mdfind to find all files that contain letter codes
set lookInThisFolderOnly to choose folder
set lookInThisFolderOnly to (POSIX path of lookInThisFolderOnly)

set theDeleteTheseFiles to {}
set theCodeList to {"AAN", "ABH", "ABT", "AFE", "AGP", "AHM", "AIL", "ALI", "ALV", "AME", "APE", "APP", "APS", "ARE"}
repeat with aCode in theCodeList
	set theMDFindScript to "mdfind -onlyin " & lookInThisFolderOnly & " kMDItemDisplayName == '*" & (aCode as string) & "*'"
	-- return theMDFindScript
	
	--kMDItemDisplayName
	set theList to (do shell script theMDFindScript)
	set AppleScript's text item delimiters to "
"
	if theList = "" then
		set theDeleteTheseFiles to (theDeleteTheseFiles & (every text item of theList))
	end if
end repeat


set AppleScript's text item delimiters to return
tell me to writeToFile({rpText:"- Files Deleted - " & return & theDeleteTheseFiles & return, rpDest:"Homes-2:nrosario:Desktop:testLog", rpFileName:"DeleteFiles.log", opAppend:"a", opTimeStamp:"f"})

tell me to deleteFileFolder({rpTarget:theDeleteTheseFiles})

mikerickson · April 30, 2009, 12:51am

Nellbern,
I’m also working on a Mac, if you are using Excel 2004 (as I do) VBA is supported. Its only VB v.5, but it works.