Writing list our to CSV file

Scripting Forums AppleScript | Mac OS X
#1

Hi All,

I’m trying to write the following list out to a CSV file. It was originally read from a CSV file using Nigel Garvey’s script (great script), manipulated and now I need to write it back to a new CSV file. I can’t seem to get anything close to what I need so some help would be appreciated.

This is what the list looks like:

This is what I want the CSV to look like:

Any help would be greatly appreciated as my brain is fried now trying different combinations.

Thanks,

RP

#2

Hi rpaulen.

At its crudest and simplest, this would do for comma-delimiting field values which don’t contain commas themselves and don’t contain quotes. However, for general use, it would need to be expanded to deal with these. It’s late in my part of the world, but I’ll look the script again tomorrow if you need it to be able to handle quotes and/or commas too.

on listToCSV(theList)
	copy theList to theList -- Use a copy in case the original needs to be preserved.
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to ","
	repeat with thisSublist in theList
		set thisSublist's contents to thisSublist as text
	end repeat
	set AppleScript's text item delimiters to linefeed
	set CSVText to theList as text
	set AppleScript's text item delimiters to astid
	
	return CSVText
end listToCSV

set theList to {{"email", "website"}, {"info@youremail.ca", "https://www.youremail.ca/"}, {"info@youremail2.ca", "https://www.youremail2.ca/"}, {"info@anotheremail.com", "http://somewebsite.com/"}}
return listToCSV(theList)
(* -->
"email,website
info@youremail.ca,https://www.youremail.ca/
info@youremail2.ca,https://www.youremail2.ca/
info@anotheremail.com,http://somewebsite.com/" *)
#3

Works great Nigel. Thanks for putting this together for me!

RP

#4

Just for my own satisfaction:

on listToCSV(theList, separator)
	copy theList to theList -- Use a copy in case the original needs to be preserved.
	set astid to AppleScript's text item delimiters
	set AppleScript's text item delimiters to separator
	-- Check each item in each sublist of the input list.
	repeat with thisSublist in theList
		repeat with thisField in thisSublist
			-- Make sure we have a text version of the item (assuming this is possible!).
			set fieldValue to thisField as text
			if (fieldValue contains quote) then
				-- If the value contains double-quote(s), escape them and enquote it.
				set AppleScript's text item delimiters to quote
				set textItems to fieldValue's text items
				set AppleScript's text item delimiters to "\"\""
				set thisField's contents to quote & textItems & quote
				set AppleScript's text item delimiters to separator
			else if ((fieldValue contains separator) or ((count fieldValue's paragraphs) > 1)) then
				-- Enquote the value if it contains the separator or a line ending.
				set thisField's contents to quote & fieldValue & quote
			end if
		end repeat
		-- Replace the entire sublist with text formed from its values joined with the separator.
		set thisSublist's contents to thisSublist as text
	end repeat
	-- Combine the sublist replacements into a single text with linefeeds between them.
	set AppleScript's text item delimiters to linefeed
	set CSVText to theList as text
	set AppleScript's text item delimiters to astid
	
	return CSVText
end listToCSV

set theList to {{"email", "website"}, {"info@youremail.ca", "https://www.youremail.ca/"}, {"", ""}, ¬
	{"There are \"double-quotes\" in this field", "Commas, in, this"}, {pi, "And a line break
in this."}}

-- With comma separators:
listToCSV(theList, ",")
(* -->
"email,website
info@youremail.ca,https://www.youremail.ca/
,
\"There are \"\"double-quotes\"\" in this field\",\"Commas, in, this\"
3.14159265359,\"And a line break
in this.\"" *)

-- Or with semicolon separators:
listToCSV(theList, ";")
(* -->
"email;website
info@youremail.ca;https://www.youremail.ca/
;
\"There are \"\"double-quotes\"\" in this field\";Commas, in, this
3.14159265359;\"And a line break
in this.\"" *)
#5

Works like a charm.
Thanks for doing this Nigel!

Regards,

RP