sorting files based on information from a CVS file

the CVS file has two columns: file code in one and folder name in the other. The code is the 5-number string in the file name.

As is, the code puts everything into the first folder A. Please help.


tell application "Finder"
	--gets working folder and the cvs file
	set folderwithfiles to choose folder
	set workingFiles to get every item of (entire contents of folder (folderwithfiles)) whose name extension is "zip" as list
	set FolderwithFilesString to ((folderwithfiles as string) & "data.csv")
	set cvsFile to FolderwithFilesString as alias
	
	--Main Loop
	set countworkingFiles to count every item in workingFiles
	repeat with i from 1 to countworkingFiles
		--this section is getting values from working folder
		set folderName to ""
		set thefile to item i of workingFiles --complete path to the working file
		set nameofthefile to (the name of thefile) --the file name
		set VersiontoFind to my findVersion(nameofthefile) --gets the version from the from the file
		set folderName to my findFolder(cvsFile, VersiontoFind) --Folder the file needs to in 
		set destinationFolder to ((folderwithfiles as string) & (folderName as string))
		
		tell application "Finder"
			move file (thefile as alias) to (destinationFolder as alias)
		end tell
		
	end repeat
	
end tell


on findVersion(workingFile)
	set text item delimiters to "_"
	set versionCode to every text item of workingFile
	set theCode to item 2 of versionCode as string
	set oldDelims to AppleScript's text item delimiters
	set AppleScript's text item delimiters to ","
	return theCode
end findVersion

-- I did not write this I copied it from a webiste
on findFolder(the_file, versionCode)
	set the_search_term to versionCode as text
	set my_data to read the_file
	set my_list to paragraphs of my_data as list
	-- we need to make two lists: ColumnA, and ColumnB
	set ColumnA_list to {}
	set ColumnB_list to {}
	-- this is housekeeping
	set oldDelims to AppleScript's text item delimiters
	set AppleScript's text item delimiters to ","
	
	-- /housekeeping
	--make the lists
	
	repeat with an_item in my_list
		-- inserting "try" statement to catch blank lines
		try
			set end of ColumnA_list to text item 1 of an_item
			set end of ColumnB_list to text item 2 of an_item
		end try
	end repeat
	
	set AppleScript's text item delimiters to oldDelims
	
	set the_position to indexof(versionCode, ColumnA_list)
	-- "indexof" is Emmanuel Levy's routine-- thanks Emmanuel!
	set the_position to the_position + 1
	if the_position is 0 then
		tell application "Finder"
			activate
			display dialog "No Files"
		end tell
		return
	end if
	
	-- now we know which line has the search term, so we can specify the corresponding
	-- item in ColumnB_list
	--set test to (item the_position of ColumnB_list)
	--display dialog (test as string)
	tell application "Finder"
		return item the_position of ColumnB_list
	end tell
	
end findFolder


-- I did not write this I copied it from a webiste it goes with function fineversion
-- This is Emmanuel Levy's routing
on indexof(theItem, theList) -- credits Emmanuel Levy
	set text item delimiters to return
	set theList to return & theList & return
	set text item delimiters to {""}
	try
		-1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
	on error
		0
	end try
end indexof



It works for me with two minor edits

[format]This: set my_list to paragraphs of my_data as list

Becomes: set my_list to rest of paragraphs of my_data as list[/format]
The above edit removes the column headers in the csv file.

[format]This: return item the_position of ColumnB_list

Becomes: return item (the_position - 1) of ColumnB_list[/format]
The above edit stops the counter from hitting its head after completing the list (i.e. when searching for the last file - ROSEHILL_11673_0728_FOM).

That said, the ‘findFolder’ handler seems a bit heavy, as it builds the exact same table of data for every file search. You should be able to separate that functionality so you do it only once. Also, you could probably simplify it by getting only the last character of the item that ‘begins with’ the ‘VersiontoFind’ — you don’t really need to split each paragraph. For example:

-- Source data in csv
set csvDataFile to "88807,A
10838,B"
set csvData to paragraphs of csvDataFile
--> {"88807,A", "10838,B"}

set versionToFind to "10838"

repeat with parItem in csvData
	if contents of parItem begins with versionToFind then
		set folderCode to last character of parItem
	end if
end repeat
return folderCode
--> "B"

To flesh out the last bit above…

Replace findFolder() with newfindFolder() like so:

In the main loop, comment out the first line and insert the second. This will make it easy to revert.

[format] – set folderName to my findFolder(csvFile, VersiontoFind) – old --Folder to move to
set folderName to my newfindFolder(csvData, VersiontoFind) – new --Folder to move to[/format]

Add the newfindFolder function:

on newfindFolder(csvParagraphs, versionCode) -- csvData, VersiontoFind
	repeat with parItem in csvParagraphs -- {"88807,A", "10838,B"}
		if contents of parItem begins with versionCode then -- "88807,A"
			set folderCode to last character of parItem -- "A"
			return folderCode -- folderName
		end if
	end repeat
end newfindFolder

Comment out or ignore the original findFolder function.

thank you for the reply. I’m probably doing something wrong because it’s still not working for me.

‘Not working’ doesn’t really help with troubleshooting.

Try making these the lines above the ‘main loop’ (right after choosing the folder).

set workingFiles to get every item of (entire contents of folder (folderwithfiles)) whose name extension is "zip" as list
	set FolderwithFilesString to ((folderwithfiles as string) & "data.csv")
	set csvFile to FolderwithFilesString as alias
	tell me to set csvData to (rest of the paragraphs of (read csvFile))
	--> {"88806,A", "88807,A", "10838,B", "10837,B", "10840,B", "88808,C", "88809,C", "88852,C", "88854,C", "88819,D", "11167,D", "88849,A", "88818,B", "11053,D", "11673,A"}
	display dialog csvData as text

This should be displayed:
[format]88806,A,88807,A,10838,B,10837,B,10840,B,88808,C,88809,C,88852,C,88854,C,88819,D,11167,D,88849,A,88818,B,11053,D,11673,A[/format]

If this isn’t what you see, then try commenting out each line below the first one then run the script and check to see if the results are what you expect them to be and what the next line needs them to be. Then uncomment the next line and run it again. Eventually (probably) you’ll get to the source of the problem.

NB, to save yourself from having to ‘choose folder’ every time, just store the result with set folderwithfiles to alias whatever….

Comment out the ‘try’ statements in the handlers as well, as they will obscure any problems.

If you’re still getting all of the zip files moved to folder “A” then try this. In your csv file, change every ‘A’ to another letter and then see where your files are ending up.

Or, you could work backwards and put this line above the ‘move file’ line (and by the way, delete the tell/end tell around the move command as you’re already in a Finder tell block).

display dialog nameofzipFile & return & VersiontoFind & return & folderName

It should display these as the first two results:

[format]CORVALIS_88807_0728.zip
88807
A[/format]
[format]EDMONTON_10837_0728_MILF.zip
10837
B[/format]

FWIW, I tested 4bitm’s script exactly as written and it worked for me. I did do the following:

  • I only tested the script with 5 files–1 file was moved to Folder A, 2 to Folder B, and 1 to Folder C.
  • I deleted the header from the CSV file so that it would not be read.
  • I replaced the spaces between the columns in the CSV file with commas.

I agree that 4bitm should post the error message he is receiving or exactly what is not working.

FWIW, in my initial trial I used 4 files and it worked fine. The problem is that one of the counters pushes it past the end of the list when it looks for the ROSEHILL record, so you need to run it for at least 11 records (I’m not sure how the processing order is determined but it appears to be alphabetical). Or, I guess you could just clear out some records as long as you keep the rosehill one.

I didn’t have any spaces in the csv either — i used the data as shown in my comments. I had just assumed that there wouldn’t be spaces and that this site had done whatever to format for display here. In addition to the error message, I would like to see the actual csv data, like this to remove any possibility of confusion.

set csvData to paragraphs of csvDataFile
→ {“88807,A”, “10838,B”}

Mockman. I created all of the OP’s files and reran his original script. It threw an error as you predict.

I changed just that one line (beginning with “return item”) in the OP’s original script as you suggest and reran. All of the files were moved to the appropriate folders and no errors were reported.

peavine, I reran the script and changed out only that a one line, but it came back with an error, "Can’t get item 0 of {"ARO". what does your working script looks like? Thank you.

Mockman, I changed both instances as suggested and got the same error above.

I’m at a loss because somehow it doesn’t work for me.

I’ve included below a script that does what you want.

There is a possible flaw in what you are doing, because you get the entire contents of the selected folder which contains the target folders. As currently written, the script will work as you want provided the target folders contain no zip files. A better approach would be to get the files in the selected folder only or to place the target folders elsewhere. There’s also the question of what to do when folders within the selected folder contain zip files with the same name.

Before testing this script, please review the data file to insure the values on each line are separated by a comma only and no spaces.

on main()
	set theFolder to (choose folder)
	
	try
		set theDataFile to ((theFolder as text) & "data.csv") as alias
		set theData to paragraphs of (read theDataFile)
	on error
		display dialog "The data file could not be found." buttons "OK" cancel button 1 default button 1
	end try
	
	tell application "Finder"
		set theFiles to (every file of the entire contents of theFolder whose name extension is "zip") as alias list
		if (count theFiles) = 0 then display dialog "No files with a ZIP extension were found." buttons "OK" cancel button 1 default button 1
		repeat with aFile in theFiles
			set aFileName to name of aFile
			set theFolderName to my getFolderName(theData, aFileName)
			try
				move aFile to folder ((theFolder as text) & theFolderName & ":")
			on error
				display dialog "The Finder was unable to move the file " & quote & aFileName & quote & ". Please check that the destination folder exists and that the destination folders contain no zip files." buttons "OK" cancel button 1 default button 1
			end try
		end repeat
	end tell
end main

on getFolderName(theData, theFileName)
	set AppleScript's text item delimiters to "_"
	set theParsedFileName to text item 2 of theFileName
	set AppleScript's text item delimiters to ","
	repeat with aLine in theData
		if aLine contains "," then
			if text item 1 of aLine = theParsedFileName then
				set theFolder to text item 2 of aLine
				set AppleScript's text item delimiters to ""
				return theFolder
			end if
		end if
	end repeat
	display dialog "A folder could not be found for " & quote & theFileName & quote buttons "OK" cancel button 1 default button 1
end getFolderName

main()

Can you create a new script with the following, run it and then post the results here?

tell application "Finder"
	-- gets working folder and csv file
	set folderwithfiles to choose folder
	set workingFiles to get every item of (entire contents of folder (folderwithfiles)) whose name extension is "zip" as list
	
	set FolderwithFilesString to ((folderwithfiles as string) & "data.csv")
	set csvFile to FolderwithFilesString as alias
	
	-- gets each data set
	set workingFilesNames to get name of every item of (entire contents of folder (folderwithfiles)) whose name extension is "zip" as list
	set destfoldersNames to name of every folder of folder folderwithfiles
	set csvRecords to paragraphs of (read csvFile)
	
	set bigPile to {workingFilesNames, destfoldersNames, csvRecords}
	
end tell

It may help identify the differences between what we each see. The results should look something like this:

{{"CORVALIS_88807_0728.zip", "EDMONTON_10837_0728_MILF.zip", "EVERETT_88808_0728.zip", "HUNTS_88818_0728_PWP.zip", "KIRKLAND_88854_0728_RDMIZR.zip", "MEDINA_88819_0728_LIT.zip", "MTHOOD_88806_0728_OFT.zip", "ORCAS_11167_0728_TTM.zip", "OVERALKE_11053_0728_DWD.zip", "REDMOND_88852_0728_JIGGY.zip", "ROSEHILL_11673_0728_FOM.zip", "STJAMES_10840_0728_CCC7W.zip", "STMARTEN_88809_0728_AMF.zip", "WINDRIVER_10838_0728_MODM.zip", "YARROW_88849_0728.zip"}, {"A", "B", "C", "D", "E"}, {"FILE,FOLDER", "88806,A", "88807,A", "10838,B", "10837,B", "10840,B", "88808,C", "88809,C", "88852,C", "88854,C", "88819,D", "11167,D", "88849,A", "88818,B", "11053,D", "11673,A"}}

CSV file is one file with data separated by comma “,”. But, I will set text AppleScript’s text item delimiters to return and linefeed combinations as well. Because I am not sure the OP’s csv file is csv file exactly (the OP uses paragraphs term, so I guess the OP’s csv file may content linefeeds and returns as well).

If something will throw error in the following script, feel free to post here error message:


set sourceFolderHFS to (choose folder) as text

-- get File Codes and Folder Names lists
set csvText to read (file (sourceFolderHFS & "data.csv"))
set AppleScript's text item delimiters to {return, linefeed, return & linfeed, ","}
set csvList to text items of csvText
set {theCodes, folderNames} to {{}, {}}
repeat with i from 1 to (count csvList) - 1 by 2
	set end of theCodes to item i of csvList
	set end of folderNames to item (i + 1) of csvList
end repeat

-- get files list of entire contents of chosen folder
tell application "Finder" to set workingFiles to (files of entire contents of folder sourceFolderHFS whose name extension is "zip") as alias list

set AppleScript's text item delimiters to "_"
repeat with anAlias in workingFiles
	tell application "Finder" to set fileName to name of anAlias
	set theCode to text item 2 of fileName -- gets file code from filename
	repeat with i from 1 to count theCodes
		if item i of theCodes is theCode then
			set folderName to item i of folderNames -- -- gets corresponding folder basename
			-- move file to appropriated folder
			tell application "Finder" to move file ((contents of anAlias) as text) to folder (sourceFolderHFS & folderName)
			exit repeat
		end if
	end repeat
end repeat
set AppleScript's text item delimiters to ""

I always assumed that the format of a CSV file was pretty standardized but apparently that’s not the case. The Wikipedia definition of CSV file–which my script is based on–is as follows:

I think the OP was using ‘paragraph’ in the general applescript sense — as a record delimiter.

That all said, there isn’t any point in guessing or chasing one’s own tail. If the data is provided then the issue can be solved.

Hi, all. Assuming the destination folders A thru D and the Source folder already live on the desktop, perhaps the entire script might be as simple as:

set FiFoPair to (read (choose file with prompt "Where is the CSV file?"))'s words 3 thru -1
repeat with odd from 1 to count FiFoPair by 2
	tell application "Finder" to move (folder "Source"'s files whose its name contains my (FiFoPair's item odd) and name ends with ".zip") to folder (FiFoPair's item (odd + 1))
end repeat

While it would have been helpful had the opening state been fully described, if you root through the code you’ll find that all items (csv, destination folders, zip files) are in the same folder (or potentially subfolder for the zip files).

So you’d need to tweak your code a bit but it works well once you do. Clever though. That’s a busy tell statement. Not especially quick, at least for me… takes almost ten seconds.