Tuesday, September 21, 2021

#1 2021-07-28 12:29:37 pm

4bitm
Member
Registered: 2021-01-12
Posts: 13

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.

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



FILE NAMES
MTHOOD_88806_0728_OFT.zip
CORVALIS_88807_0728.zip
WINDRIVER_10838_0728_MODM.zip
EDMONTON_10837_0728_MILF.zip
STJAMES_10840_0728_CCC7W.zip
EVERETT_88808_0728.zip
STMARTEN_88809_0728_AMF.zip
REDMOND_88852_0728_JIGGY.zip
KIRKLAND_88854_0728_RDMIZR.zip
MEDINA_88819_0728_LIT.zip
ORCAS_11167_0728_TTM.zip
YARROW_88849_0728.zip
HUNTS_88818_0728_PWP.zip
OVERALKE_11053_0728_DWD.zip
ROSEHILL_11673_0728_FOM.zip



Applescript:


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


Offline

 

#2 2021-07-28 08:11:03 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

It works for me with two minor edits

This:        set my_list to paragraphs of my_data as list

Becomes:     set my_list to rest of paragraphs of my_data as list

The above edit removes the column headers in the csv file.

This:        return item the_position of ColumnB_list

Becomes:     return item (the_position - 1) of ColumnB_list

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:

Applescript:

-- 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"

Offline

 

#3 2021-07-28 08:44:35 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

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.

        -- set folderName to my findFolder(csvFile, VersiontoFind) -- old --Folder to move to  
        set folderName to my newfindFolder(csvData, VersiontoFind) -- new --Folder to move to

Add the newfindFolder function:

Applescript:

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.

Last edited by Mockman (2021-07-28 08:48:22 pm)

Offline

 

#4 2021-07-29 11:57:37 am

4bitm
Member
Registered: 2021-01-12
Posts: 13

Re: sorting files based on information from a CVS file

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

Offline

 

#5 2021-07-29 01:24:32 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

'Not working' doesn't really help with troubleshooting.

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

   

Applescript:

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:

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

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).

Applescript:

display dialog nameofzipFile & return & VersiontoFind & return & folderName

It should display these as the first two results:

CORVALIS_88807_0728.zip
88807
A
EDMONTON_10837_0728_MILF.zip
10837
B

Offline

 

#6 2021-07-29 07:01:47 pm

peavine
Member
From:: Prescott, Arizona
Registered: 2018-09-04
Posts: 1043

Re: sorting files based on information from a CVS file

4bitm wrote:

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



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.

Last edited by peavine (2021-07-29 07:33:58 pm)


2018 Mac mini - macOS Catalina - Script Debugger 8

Offline

 

#7 2021-07-29 07:35:01 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

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"}

Last edited by Mockman (2021-07-29 07:39:45 pm)

Offline

 

#8 2021-07-29 07:57:05 pm

peavine
Member
From:: Prescott, Arizona
Registered: 2018-09-04
Posts: 1043

Re: sorting files based on information from a CVS file

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.

Last edited by peavine (2021-07-30 07:46:54 am)


2018 Mac mini - macOS Catalina - Script Debugger 8

Offline

 

#9 2021-08-02 04:22:53 pm

4bitm
Member
Registered: 2021-01-12
Posts: 13

Re: sorting files based on information from a CVS file

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.

Last edited by 4bitm (2021-08-02 04:47:07 pm)

Offline

 

#10 2021-08-02 08:13:43 pm

peavine
Member
From:: Prescott, Arizona
Registered: 2018-09-04
Posts: 1043

Re: sorting files based on information from a CVS file

4bitm wrote:

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.



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.

Applescript:

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()

Last edited by peavine (2021-08-03 09:28:40 am)


2018 Mac mini - macOS Catalina - Script Debugger 8

Offline

 

#11 2021-08-02 08:49:42 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

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

Applescript:

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:

Applescript:

{{"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"}}

Offline

 

#12 2021-08-04 05:26:53 am

KniazidisR
Member
From:: Greece
Registered: 2019-03-03
Posts: 2038

Re: sorting files based on information from a CVS file

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:

Applescript:


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 ""

Last edited by KniazidisR (2021-08-04 05:54:14 am)


Model: MacBook Pro
OS X: Catalina 10.15.4
Web Browser: Safari 14.1
Ram: 4 GB

Offline

 

#13 2021-08-04 09:54:14 am

peavine
Member
From:: Prescott, Arizona
Registered: 2018-09-04
Posts: 1043

Re: sorting files based on information from a CVS file

KniazidisR wrote:

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).



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:

A comma-separated values (CSV) file is a delimited text file that uses a comma to separate values. Each line of the file is a data record. Each record consists of one or more fields, separated by commas. The use of the comma as a field separator is the source of the name for this file format. A CSV file typically stores tabular data (numbers and text) in plain text, in which case each line will have the same number of fields.


2018 Mac mini - macOS Catalina - Script Debugger 8

Offline

 

#14 2021-08-04 10:17:01 am

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

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.

Offline

 

#15 2021-08-04 06:23:20 pm

Marc Anthony
Member
From:: Dallas, TX
Registered: 2006-04-27
Posts: 1039

Re: sorting files based on information from a CVS file

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:

Applescript:

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

Offline

 

#16 2021-08-04 07:20:34 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 105

Re: sorting files based on information from a CVS file

Marc Anthony wrote:

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:


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).

-- set FolderwithFilesString to ((folderwithfiles as string) & "data.csv")
-- (entire contents of folder (folderwithfiles)) whose name extension is "zip"
-- set destinationFolder to ((folderwithfiles as string) & (folderName as string))


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.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)