Prase Individual Strings for CSV

I came across the script below on the Discussion forums on the Apple site and tried to sign up and contact the original poster but everything is greyed out and cannot message them directly or repost on the topic. I am hoping someone here might be able to point me in the right direction to modify this code.

The script creates new folders in a destination chosen by the user based on paragraphs of a csv or text file chosen and passed into the script.

However, the script creates folders named with the entire contents of each line of the csv or txt file. I would like to us specific strings from each line (should always be in the same order) to create a folder hierarchy of (CATEGORY > PRODUCT NAME) so that I can store relevant product info in each folder.

Mass importing product info, descriptions etc. is easy with a csv but I do not seem to have an option to automatically upload multiple images so I would like to keep them organized in the same folder structure as the site so I always know where to get them should I upload a new product.

on run -- make new folders from names in a text file
	
	(*
 
 makes new folders at the destinationFolder using names from the inputFile text file
 
 if useExistingFolders is set to false, a number suffix is added to the name if the folder exists
 
 the colon (:) is illegal for Finder names, but can be used to specify subfolders
 
 *)
	
	set useExistingFolders to false -- use folders that already exist?
	set inputFile to missing value -- set the source text file path here
	
	try
		set inputFile to inputFile as alias
	on error
		set inputFile to (choose file with prompt "Choose a text file containing folder names:")
	end try
	
	set destinationFolder to missing value -- set the destination folder path here
	
	try
		set destinationFolder to destinationFolder as alias
	on error
		set destinationFolder to (choose folder with prompt "Choose a destination folder for the new sub folders:")
	end try
	
	
	repeat with anItem in paragraphs of (read inputFile)
		set anItem to anItem as text
		if anItem is not "" then -- skip blank items
			set targetFolder to destinationFolder
			set {tempTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ":"}
			set {nameList, AppleScript's text item delimiters} to {text items of anItem, tempTID}
			
			repeat with theName in nameList -- deal with sub folders
				if not useExistingFolders then set theName to (getUniqueName for theName from targetFolder) -- avoid duplicate names
				try
					tell application "Finder"
						make new folder at targetFolder with properties {name:theName}
						set targetFolder to (the result as alias)
					end tell
				on error number -48 -- folder already exists
					set targetFolder to ((targetFolder as text) & theName) as alias
				end try
			end repeat
		end if
	end repeat
end run

to getUniqueName for someName from someFolder
	
	(*
 
 check if someName exists in someFolder, creating a new unique name if needed
 
 parameters - someName [text]: a name.extension to check for
 
 someFolder [mixed]: a folder to check
 
 returns [text]: a unique name
 
 *)
	
	set {counter, divider} to {"00", "_"}
	
	set here to -(offset of "." in ((reverse of text items of someName) as text)) - 1
	
	set theName to text 1 thru here of someName
	
	
	if here is -1 then -- no extension
		set theExtension to ""
	else
		set theExtension to text (here + 1) thru -1 of someName
	end if
	
	set newName to theName & theExtension
	
	tell application "System Events" to tell (get name of items of folder (someFolder as text))
		repeat while it contains newName
			set counter to text -2 thru -1 of ((100 + counter + 1) as text) -- leading zero
			set newName to theName & divider & counter & theExtension
		end repeat
	end tell
	
	return newName
	
end getUniqueName

Edit: [applescript] tags added by moderator.

If I understood well your question you may try to use this script after adjusting the variables indexCategory and indexProductName

on run -- make new folders from names in a text file
	
	(*

makes new folders at the destinationFolder using names from the inputFile text file

if useExistingFolders is set to false, a number suffix is added to the name if the folder exists

the colon (:) is illegal for Finder names, but can be used to specify subfolders

*)
	
	set useExistingFolders to false -- use folders that already exist?
	set inputFile to missing value -- set the source text file path here
	
	try
		set inputFile to inputFile as alias
	on error
		set inputFile to (choose file with prompt "Choose a text file containing folder names:")
	end try
	
	set destinationFolder to missing value -- set the destination folder path here
	
	try
		set destinationFolder to destinationFolder as alias
	on error
		set destinationFolder to (choose folder with prompt "Choose a destination folder for the new sub folders:")
	end try
	
	set indexCategory to 2 -- Assuming that CATEGORY is the item 2 in the list
	set indexProductName to 4 -- Assuming that PRODUCT Name is the item 4 in the list
	
	repeat with anItem in paragraphs of (read inputFile)
		set anItem to anItem as text
		if anItem is not "" then -- skip blank items
			set targetFolder to destinationFolder
			set {tempTID, AppleScript's text item delimiters} to {AppleScript's text item delimiters, ":"}
			set {nameList, AppleScript's text item delimiters} to {text items of anItem, tempTID}
			# Create the subfolder "CATEGORY"
			set targetFolder to my createSubFolder(item indexCategory of nameList, useExistingFolders)
			# Create the subfolder "PRODUCT NAME"
			set targetFolder to my createSubFolder(item indexProductName of nameList, useExistingFolders)
		end if
	end repeat
end run

on createSubFolder(theName, useExistingFolders)
	if not useExistingFolders then set theName to (getUniqueName for theName from targetFolder) -- avoid duplicate names
	try
		tell application "Finder"
			make new folder at targetFolder with properties {name:theName}
			set targetFolder to (the result as alias)
		end tell
	on error number -48 -- folder already exists
		set targetFolder to ((targetFolder as text) & theName) as alias
	end try
	return targetFolder
end createSubFolder

to getUniqueName for someName from someFolder
	
	(*

check if someName exists in someFolder, creating a new unique name if needed

parameters - someName [text]: a name.extension to check for

someFolder [mixed]: a folder to check

returns [text]: a unique name

*)
	
	set {counter, divider} to {"00", "_"}
	
	set here to -(offset of "." in ((reverse of text items of someName) as text)) - 1
	
	set theName to text 1 thru here of someName
	
	
	if here is -1 then -- no extension
		set theExtension to ""
	else
		set theExtension to text (here + 1) thru -1 of someName
	end if
	
	set newName to theName & theExtension
	
	tell application "System Events" to tell (get name of items of folder (someFolder as text))
		repeat while it contains newName
			set counter to text -2 thru -1 of ((100 + counter + 1) as text) -- leading zero
			set newName to theName & divider & counter & theExtension
		end repeat
	end tell
	
	return newName
	
end getUniqueName



Yvan KOENIG running High Sierra 10.13.6 in French (VALLAURIS, France) lundi 9 septembre 2019 10:52:39

Hi jfish.

The purpose of the original script was apparently to take a text file in which each line consisted of a partial HFS path and to create the hierarchies represented by those paths in a chosen folder, with the option to reuse existing folders or not.

Just to be clear, are you saying that in your set-up, each path is just one of several comma-separated items in each line of the text file? Or that you only want to create parts of the hierarchies? Or something else?

I’ve edited your post to wrap the script text in MacScripter’s [applescript] and [/applescript] tags so that it can be opened more easily in people’s script editors. Would you mind using these yourself when posting AS code?

Meanwhile, here’s a rewrite of the getUniqueName handler: :slight_smile:

to getUniqueName for someName from someFolder
	
	(*
		Check if someName exists in someFolder, creating a new unique name if needed.
		Parameters:
		someName [text]: a name.extension to check for
		someFolder [mixed]: a folder to check
		returns [text]: a unique name
	*)
	
	if (someName contains ".") then
		set astid to AppleScript's text item delimiters
		set AppleScript's text item delimiters to "."
		set theName to text 1 thru text item -2 of someName
		set theExtension to "." & text item -1 of someName
		set AppleScript's text item delimiters to astid
	else
		set theName to someName
		set theExtension to ""
	end if
	
	set newName to someName
	set {counter, divider} to {0, "_"}
	
	tell application "System Events" to tell (get name of disk items of folder (someFolder as text))
		repeat while it contains newName
			set counter to counter + 1
			set newName to theName & divider & text 2 thru -1 of (100 + counter as text) & theExtension
		end repeat
	end tell
	
	return newName
	
end getUniqueName

Hi Nigel,

No each line contained in my file is as comma seperated list of the attributes for a single product, (i.e. name, description, category, stock, price etc.)

I want to pull the string for category and description to create folders “Category > Description” so that I can store files in each each folder pertaining to the product. I have over 700 items I need to make folders for.

I thought I did paste my code inside the applescirpt tags but will be more careful next time.

Yvan, I will try to adjust the variables indexCategory and indexProductName and try your code.

OK. This does what I think you want. It bears a superficial resemblance to the script you posted. :wink: I’ve removed the code which creates alternative folders when names clash as it doesn’t seem you’ll need it.

Adjust the properties at the top to specify the field separator, to ignore a header line or not, and to define the column numbers of the fields to use in the folder hierarchy and the order in which they’re used. The CSVToList handler is from a script I wrote last year and still happened to have on my Desktop.

-- Adjust these properties to control the script behaviour.
property fieldSeparator : ","
property ignoringTopLine : true -- Is the first line of the CSV text just headers?
property descriptionColumn : 2
property categoryColumn : 3
-- More 'column' properties can be set if needed and added to the following list.
property hierarchyOrder : {categoryColumn, descriptionColumn} -- For rootPath/category/description/

main()

on main()
	script o
		property separatedValues : missing value
		property collector : {}
	end script
	
	-- Select the CSV file and the destination folder.
	set inputFile to (choose file with prompt "Choose a CSV file:" of type {"csv"})
	set rootPath to quoted form of POSIX path of (choose folder with prompt "Choose a destination folder for the new sub folders:")
	-- Create a list of lists of text representing the records and fields of the CSV text.
	set CSVText to (read inputFile as «class utf8»)
	set o's separatedValues to CSVToList(CSVText, {separator:fieldSeparator, trimming:false})
	
	-- Ignoring the first record or not …
	if (ignoringTopLine) then
		set start to 2
	else
		set start to 1
	end if
	-- … loop through each list (ie. row, line, or record) in the result.
	set astid to AppleScript's text item delimiters
	repeat with i from start to (count o's separatedValues)
		set thisRecord to item i of o's separatedValues
		-- Build a subhierarchy path for this record in the required field order, watching out for POSIX path incompatible characters.
		set thisSubpath to {}
		repeat with thisField in hierarchyOrder
			set end of thisSubpath to degremlinise(item thisField of thisRecord)
		end repeat
		set AppleScript's text item delimiters to "/"
		-- Add the result to the 'collector' list.
		set end of o's collector to quoted form of (thisSubpath as text)
		-- Every 1000 records, create the subhierarchies for the paths so far and start a new collector.
		if (i mod 1000 is 0) then
			doThisLot(rootPath, o's collector)
			set o's collector to {}
		end if
	end repeat
	set AppleScript's text item delimiters to astid
	-- At the end of the repeat, create the subhierarchies for the path(s) remaining in the collector.
	set subhierarchiesToDo to (count o's collector)
	if (subhierarchiesToDo > 1) then
		doThisLot(rootPath, o's collector)
	else if (subhierarchiesToDo is 1) then
		do shell script ("mkdir -p " & rootPath & beginning of o's collector)
	end if
end main

-- Given a quoted root path and a list of quoted subpaths, create the represented hierarchies.
on doThisLot(rootPath, thisLot)
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to ","
	set hierarchyTree to "{" & thisLot & "}"
	set AppleScript's text item delimiters to astid
	
	do shell script ("mkdir -p " & rootPath & hierarchyTree)
end doThisLot

-- Ensure that a given text is POSIX path compatible. Replace any colons with underscores and any slashes with colons.
on degremlinise(thisText)
	set astid to AppleScript's text item delimiters
	if (thisText contains ":") then
		set AppleScript's text item delimiters to ":"
		set textItems to thisText's text items
		set AppleScript's text item delimiters to "_"
		set thisText to textItems as text
	end if
	if (thisText contains "/") then
		set AppleScript's text item delimiters to "/"
		set textItems to thisText's text items
		set AppleScript's text item delimiters to ":"
		set thisText to textItems as text
	end if
	set AppleScript's text item delimiters to astid
	
	return thisText
end degremlinise

(* Return a list of lists from a CSV text.
	Parameters:
		CSVText: The CSV text. Can be text, NSString, or NSMutableString.
		implementation: A record with optional 'separator' and 'trimming' properties. 'separator' specifies the field separator (default = ","), while 'trimming' indicates whether or not to trim leading and trailing spaces from field values (default = false).
*)
on CSVToList(CSVText, implementation)
	script o
		use AppleScript version "2.4" -- Yosemite (10.10) or later
		use framework "Foundation"
		
		property allFields : missing value
		property output : {}
		
		on CSVToList()
			-- Sort out the field separator and trimming mode.
			set {separator:fieldSeparator, trimming:trimming} to implementation & {separator:",", trimming:false}
			
			-- Get an NSMutableString version of the CSV text and strip any trailing line breaks from it.
			set |⌘| to current application
			set CSVString to (|⌘|'s class "NSMutableString"'s stringWithString:(CSVText))
			set regexSearch to |⌘|'s NSRegularExpressionSearch
			tell CSVString to replaceOccurrencesOfString:("\\R++\\Z") withString:("") options:(regexSearch) range:({0, its |length|()})
			-- If the very first field's empty, insert an additional field separator at the beginning to make the field visible to regex.
			set testLength to 10
			tell (CSVString's |length|()) to if (it < testLength) then set testLength to it
			if ((CSVString's rangeOfString:(fieldSeparator & "|\\R") options:(regexSearch) range:({0, testLength}))'s location is 0) then tell CSVString to insertString:(fieldSeparator) atIndex:(0)
			
			-- Get all matches for a regex having capture groups for the field separator, record separator, or text start before each field and for the field itself.
			if (trimming) then
				set fieldPattern to "(" & fieldSeparator & "|\\R|\\A) *+(\"(?:[^\"]|\"\")*+\"|(?:[^ [:cntrl:]" & fieldSeparator & "]| *+(?!" & fieldSeparator & "|\\R|\\Z))*+) *+"
			else
				set fieldPattern to "(" & fieldSeparator & "|\\R|\\A)(\"(?:[^\"]|\"\")*+\"|[^[:cntrl:]" & fieldSeparator & "]*+)"
			end if
			set fieldRegex to (|⌘|'s class "NSRegularExpression"'s regularExpressionWithPattern:(fieldPattern) options:(0) |error|:(missing value))
			set CSVRange to {0, CSVString's |length|()}
			set fieldMatches to fieldRegex's matchesInString:(CSVString) options:(0) range:(CSVRange)
			
			-- Find out the number of fields per record by counting the matches before the first whose capture group 1 represents a line break.
			set fieldsPerRecord to (count fieldMatches) -- (In case there's only one record.)
			set counter to 0
			repeat with thisMatch in fieldMatches
				set group1Range to (thisMatch's rangeAtIndex:(1))
				if ((CSVString's rangeOfString:("\\R") options:(regexSearch) range:(group1Range)) is group1Range) then
					set fieldsPerRecord to counter
					exit repeat
				end if
				set counter to counter + 1
			end repeat
			
			-- Replace every match with character id 1 and the field value.
			tell fieldRegex to replaceMatchesInString:(CSVString) options:(0) range:(CSVRange) withTemplate:((character id 1) & "$2")
			-- Delete all field-enclosing double-quotes.
			tell CSVString to replaceOccurrencesOfString:("(?<=\\u0001)\"|\"(?=\\u0001)") withString:("") options:(regexSearch) range:({0, its |length|()})
			-- Delete all double-quote-escaping double-quotes.
			tell CSVString to replaceOccurrencesOfString:("\"\"") withString:("\"") options:(0) range:({0, its |length|()})
			-- Get an AS list of the field values as AS texts.
			set allFields to (CSVString's componentsSeparatedByString:(character id 1)) as list
			-- Transfer the values to the output list in lists of a record's worth at a time.
			repeat with i from 2 to (count allFields) by fieldsPerRecord
				set end of my output to my allFields's items i thru (i + fieldsPerRecord - 1)
			end repeat
			
			return output
		end CSVToList
	end script
	
	return o's CSVToList()
end CSVToList

Wow, thank you Nigel. I will take a look at this when I am home this evening and let you know what I find.

Wow this worked like a charm.

I believe I am interpreting this correctly and have found from some testing that if a folder and sub folder already exist the script ignores the item and will not overwrite the folders (i.e. files inside those folders). This got me to thinking that if I adjust the description of the file in the csv and re-run it is going to create a new folder and the content inside the old one will stay where it is. I was trying to think of a way I could move the content. Adding tags using Hazel or something but that is another days task.

Hopefully I am not modifying too many descriptions and only adding new items for the most part.

I appreciate your help.