Edit CSV table - write text with commas into a cell

[danielA1]

“I have an AppleScript below that allows writing cell/reading cell/deleting columns/deleting rows in a CSV table. The script works. But when I want to write text with commas into a cell, e.g., ‘a1, 2, 3,’ the entire table breaks. I have tried several AI tools but haven’t found a solution. Could you please help me fix this script?”

Created by Perplexity.ai:

on csvEdit(action, rowIdx, colIdx, value, csvPath)
	set csvText to (do shell script "iconv -f utf-8 -t utf-8 " & quoted form of POSIX path of csvPath)
	set csvLines to paragraphs of csvText
	set csvData to {}
	repeat with l in csvLines
		set end of csvData to (my splitText(l, ","))
	end repeat
	
	if action is "read" then
		if rowIdx ≥ 1 and rowIdx ≤ (count csvData) then
			set rowList to item rowIdx of csvData
			if colIdx ≥ 1 and colIdx ≤ (count rowList) then
				return item colIdx of rowList
			end if
		end if
		return ""
	else if action is "write" then
		if rowIdx ≥ 1 and rowIdx ≤ (count csvData) then
			set rowList to item rowIdx of csvData
			if colIdx ≥ 1 and colIdx ≤ (count rowList) then
				set item colIdx of rowList to value
				set item rowIdx of csvData to rowList
			end if
		end if
	else if action is "delrow" then
		if rowIdx ≥ 1 and rowIdx ≤ (count csvData) then
			if rowIdx = 1 then
				set csvData to items 2 thru (count csvData) of csvData
			else if rowIdx = (count csvData) then
				set csvData to items 1 thru (rowIdx - 1) of csvData
			else
				set csvData to (items 1 thru (rowIdx - 1) of csvData) & (items (rowIdx + 1) thru (count csvData) of csvData)
			end if
		end if
	else if action is "delcol" then
		repeat with i from 1 to count csvData
			set rowList to item i of csvData
			if colIdx ≥ 1 and colIdx ≤ (count rowList) then
				if colIdx = 1 then
					set rowList to items 2 thru (count rowList) of rowList
				else if colIdx = (count rowList) then
					set rowList to items 1 thru (colIdx - 1) of rowList
				else
					set rowList to (items 1 thru (colIdx - 1) of rowList) & (items (colIdx + 1) thru (count rowList) of rowList)
				end if
			end if
			set item i of csvData to rowList
		end repeat
	end if
	
	set outText to ""
	repeat with r in csvData
		set outText to outText & (my joinText(r, ",")) & linefeed
	end repeat
	do shell script "echo " & quoted form of outText & " | iconv -f utf-8 -t utf-8 > " & quoted form of POSIX path of csvPath
	return "OK"
end csvEdit
-- Split and join
on splitText(txt, delim)
	set AppleScript's text item delimiters to delim
	set outList to text items of txt
	set AppleScript's text item delimiters to ""
	return outList
end splitText
on joinText(lst, delim)
	set AppleScript's text item delimiters to delim
	set outText to lst as string
	set AppleScript's text item delimiters to ""
	return outText
end joinText
-- CSV file
set csvPath to "/Users/adam/Desktop/test.csv"

-- Read: [D1]
set val to csvEdit("read", 1, 4, "", csvPath)
display dialog val
-- Write: [D1]
csvEdit("write", 1, 4, "New value", csvPath)
-- Delete: row (3)
csvEdit("delrow", 3, 0, "", csvPath)
-- Delete: column (5)
csvEdit("delcol", 0, 5, "", csvPath)

[StefanK]

Basically you need to insert the lines

if value contains "," then
	set value to quote & value & quote
end if

in the write action right before the line

set item colIdx of rowList to value

[danielA1]

That doesn’t work.

csvEdit("write", 1, 4, "New, value", csvPath)

[StefanK]

@danielA1

That doesn’t work.

Actually it does, but only for the write branch as you wrote

@danielA1

…I want to write text…

You need to modify the code to consider also quoted values while reading.

[danielA1]

@StefanK

You need to modify the code to consider also quoted values while reading.

Can I ask you to modify the code?

[Camelot]

This is much harder than you might think.

The root of the problem is that you’re reading in the entire file and using AS’s text item delimiters to parse out the fields.
That’s inherently a problem where existing data may have commas. You need to differentiate between a comma that separates values, and a comma that’s embedded in one.

For that, the CSV standard says that values that contain commas should be quoted, like:

A,B,C,‘D,E,F’,G

As per CSV rules, this row contains 5 values:

{“A”, “B”, “C”, “D,E,F”, “G”}

However your code, using simple AppleScript Text Item Delimiters sees it as:

{“A”, “B”, “C”, “'D”, “E”, “F’”, “G”}

because its breaking on the comma and is blind to the quoted fourth item. The quote symbol just becomes part of that field value.

In order to properly parse this you need to check for the quote symbol and not break subsequent commas into new fields until the quote is closed.

The only way to do this properly is character-by-character scanning where you specifically detect the opening quote and pivot how you interpret the data.

Here’s one way of doing it.:

use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions

set CSVDataContainsQuotedValues to false
set CSVData to {}

set RawCSVData to read file ((path to desktop as text) & "test.csv") as text

if RawCSVData contains "'" then set CSVDataContainsQuotedValues to true

if CSVDataContainsQuotedValues is false then
	-- no quoted values, so simple TIDs shortcut will suffice:
	set {my text item delimiters, oldDelim} to {",", my text item delimiters}
	set CSVData to text items of RawCSVData
	set my text item delimiters to oldDelim
	return CSVData
else
	-- we have quoted data so we need to parse it
	set CSVRows to paragraphs of RawCSVData
	repeat with eachRow in CSVRows
		if eachRow contains "'" then
			-- this row contains a quote, so take the long road
			set thisRowData to parseRow(eachRow as text)
		else
			-- no quote in this row, so simple TIDs will suffice
			set {my text item delimiters, oldDelim} to {",", my text item delimiters}
			set thisRowData to text items of (eachRow as text)
			set my text item delimiters to oldDelim
		end if
		
		set CSVData to CSVData & {thisRowData}
	end repeat
	return CSVData
end if

on parseRow(theData)
	local thisRowsData
	set thisVal to ""
	set thisRowsData to {}
	set i to 1
	set charCount to (count theData)
	
	repeat while i ≤ charCount
		set thisChar to character i of theData
		
		if thisChar is "'" then
			-- this is the opening of a quoted field, so grab everything up to the closing quote
			set quotedStart to i + 1
			set checkChar to quotedStart + 1
			repeat until (character checkChar of theData is "'") or (checkChar = charCount)
				set thisVal to characters quotedStart through checkChar of theData as text
				set checkChar to checkChar + 1
			end repeat
			set i to checkChar
		else
			if thisChar is "," then
				set thisRowsData to thisRowsData & thisVal
				set thisVal to ""
			else
				set thisVal to thisVal & thisChar as text
			end if
		end if
		log thisVal
		
		set i to i + 1
	end repeat
	set thisRowsData to thisRowsData & thisVal
	
	return thisRowsData
end parseRow

[Nigel_Garvey]

Here’s a collection of handlers based on the idea of deriving a mutable array of mutable arrays from the CSV text, performing any required edits in the array, and then creating a new text from the array. There are CSVToArray() and arrayToCSV() handlers (returning an NSMutableArray and NSMutableString respectively) and handlers for inserting and deleting rows and columns from the array. Individual field values can be set using AppleScript values and syntax and it’s up to the scripter how they load and store the CSV text.

Unfortunately, the CSVToArray() handler doesn’t guess the field delimiter, but assumes it’s a comma unless told otherwise. The record separator can be either (return & linefeed), linefeed, or return. The arrayToCSV() handler’s defaults are comma and (return & linefeed) respectively unless specified otherwise via its separators parameter.

(* Assumes that the CSV text follows the RFC 4180 convention:
	Records are delimited by CRLF line breaks (but LFs or CRs are OK too here).
	The last record may or may not be followed by a line break.
	All the records have the same number of fields.
	Fields in the same record are separated by commas (but a different separator can be specified here).
	The last field in a record is NOT followed by a field separator.
	Any field value may be enclosed in double-quotes and MUST be if its value contains line breaks, separator characters, or double-quote characters.
	Double-quotes within quoted fields are escaped as pairs of double-quotes.
	Trailing or leading spaces in unquoted fields are included in the field values.
	Spaces (or anything else!) outside the quotes of quoted fields are not allowed.
	No other variations are currently supported. *)

use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions

property |⌘| : current application
property regex : a reference to |⌘|'s NSRegularExpressionSearch

demo()

on demo()
	set CSV to "One,two,three,four
1,2,3,4
\"5,000\", 60 ,\"7,890\",
111,222,333,444
555,\"5, 6, or 7\",777,888"
	
	set table to CSVToArray(CSV, {}) -- same as: CSVToArray(CSV, {fieldSeparator:","})
	-- return (table as list)
	--> {{"One", "two", "three", "four"}, {"1", "2", "3", "4"}, {"5,000", " 60 ", "7,890", ""}, {"111", "222", "333", "444"}, {"555", "5, 6, or 7", "777", "888"}}
	deleteRow(table, 4)
	insertRow(table, 2)
	insertColumn(table, 2)
	set table's item 1's item 2 to "The proverbial \"1a\"" -- Field 2 of record 1. Entered with AS text and syntax.
	set CSV to arrayToCSV(table, {}) -- same as: arrayToCSV(table, {fieldSeparator:",", recordSeparator:(return & linefeed)})
	return CSV as text
	(*
--> "One,\"The proverbial \"\"1a\"\"\",two,three,four

,,,,

1,,2,3,4

\"5,000\",, 60 ,\"7,890\",

555,,\"5, 6, or 7\",777,888"
*)
end demo

(* Derive an NSMutableArray of NSMutableArrays from a CSV text. *)
on CSVToArray(CSV, separator) -- (CSV text or NSString, record with optional fieldSeparator property)
	set {fieldSeparator:fieldSeparator} to separator & {fieldSeparator:","} -- Given or default field separator.
	
	set CSV to (|⌘|'s class "NSMutableString"'s stringWithString:(CSV))
	-- Strip any trailing line breaks.
	CSV's replaceOccurrencesOfString:("\\R++\\Z") withString:("") options:(regex) range:({0, CSV's |length|()})
	-- If the very first field's empty, insert an additional field separator at the beginning to make the field visible to the regex stuff below.
	if ((CSV's rangeOfString:(fieldSeparator & "|\\R") options:(regex))'s location() is 0) then CSV's insertString:(fieldSeparator) atIndex:(0)
	
	-- Get all matches for a regex pattern having a capture group for the separator or text start before a field and a capture group for the field's value.
	-- The field value is either quoted text or an optional run of non-separator characters.
	set fieldPattern to "(" & fieldSeparator & "|\\R|\\A)(\"(?:[^\"]|\"\")*+\"|[^" & fieldSeparator & "\\u000a\\u000d]*+)"
	set fieldRegex to (|⌘|'s class "NSRegularExpression"'s regularExpressionWithPattern:(fieldPattern) options:(0) |error|:(missing value))
	set CSVRange to {0, CSV's |length|()}
	set fieldMatches to fieldRegex's matchesInString:(CSV) options:(0) range:(CSVRange)
	
	-- The number of fields per record is the number of matches before the first match whose first capture group matches a line break.
	set fieldsPerRecord to 0
	repeat with thisMatch in fieldMatches
		tell (thisMatch's rangeAtIndex:(1)) to if ((CSV's rangeOfString:("\\R") options:(regex) range:(it)) = it) then exit repeat
		set fieldsPerRecord to fieldsPerRecord + 1
	end repeat
	
	-- Replace every match with character id 1 and the field value.
	fieldRegex's replaceMatchesInString:(CSV) options:(0) range:(CSVRange) withTemplate:((character id 1) & "$2")
	-- Delete any field-enclosing double-quotes.
	CSV's replaceOccurrencesOfString:("(?<=\\u0001)\"|\"(?=\\u0001)") withString:("") options:(regex) range:({0, CSV's |length|()})
	-- Unescape any CSV-escaped double-quotes.
	CSV's replaceOccurrencesOfString:("\"\"") withString:("\"") options:(0) range:({0, CSV's |length|()})
	-- Split the string into an array of the field values. (The first item will be a superfluous empty string.)
	set allFields to (CSV's componentsSeparatedByString:(character id 1))
	-- Transfer the values to an output array in (mutable) arrays of a record's worth of fields at a time.
	set array to |⌘|'s class "NSMutableArray"'s arrayWithCapacity:((count allFields) div fieldsPerRecord)
	repeat with i from 1 to ((count allFields) - 1) by fieldsPerRecord -- 0-indexed.
		(array's addObject:((allFields's subarrayWithRange:({i, fieldsPerRecord}))'s mutableCopy()))
	end repeat
	
	return array
end CSVToArray

(* Derive a CSV NSString from an array of arrays or list of lists. *)
on arrayToCSV(array, separators) -- (NSArray or list, record with optional fieldSeparator and recordSeparator properties)
	set {fieldSeparator:fieldSeparator, recordSeparator:recordSeparator} to separators & {fieldSeparator:",", recordSeparator:(return & linefeed)} -- Specified or default separators.
	
	set array to |⌘|'s class "NSMutableArray"'s arrayWithArray:(array)
	-- Flatten the input array to an NSMutableString of (character id 1)-delimited fields and (character id 2)-delimited records.
	repeat with thisRecord in array
		set thisRecord's contents to (thisRecord's componentsJoinedByString:(character id 1))
	end repeat
	set CSV to (array's componentsJoinedByString:(character id 2))'s mutableCopy()
	-- Escape any double-quotes in the string.
	CSV's replaceOccurrencesOfString:(quote) withString:(quote & quote) options:(0) range:({0, CSV's |length|()})
	-- Enquote any fields containing field separator(s), quote(s), and/or line ending(s).
	set quoteFieldPattern to "(?<=\\A|\\u0001|\\u0002)[^\\u0001\\u0002" & fieldSeparator & "\"\\u000a\\u000d]*+[" & fieldSeparator & "\"\\u000a\\u000d][^\\u0001\\u0002]*+"
	CSV's replaceOccurrencesOfString:(quoteFieldPattern) withString:("\"$0\"") options:(regex) range:({0, CSV's |length|()})
	-- Replace the (character id 1)s with field separators.
	CSV's replaceOccurrencesOfString:(character id 1) withString:(fieldSeparator) options:(0) range:({0, CSV's |length|()})
	-- Replace the (character id 2)s with record separators.
	CSV's replaceOccurrencesOfString:(character id 2) withString:(recordSeparator) options:(0) range:({0, CSV's |length|()})
	
	return CSV
end arrayToCSV

(* Insert a row of empty fields into the table created above. *)
on insertRow(table, rowNumber) -- (NSMutable array, 1-based row number)
	set columnCount to (count table's first item)
	set newRow to |⌘|'s class "NSMutableArray"'s arrayWithCapacity:(columnCount)
	repeat columnCount times
		newRow's addObject:("")
	end repeat
	table's insertObject:(newRow) atIndex:(rowNumber - 1)
end insertRow

(* Delete a row from the table. *)
on deleteRow(table, rowNumber) -- (NSMutable array, 1-based row number)
	table's removeObjectAtIndex:(rowNumber - 1)
end deleteRow

(* Insert a column of empty fields into the table. *)
on insertColumn(table, columnNumber) -- (NSMutable array, 1-based column number)
	repeat with thisRow in table
		(thisRow's insertObject:("") atIndex:(columnNumber - 1))
	end repeat
end insertColumn

(* Delete a column from from the table. *)
on deleteColumn(table, columnNumber) -- (NSMutable array, 1-based column number)
	table's makeObjectsPerformSelector:("removeObjectsAtIndexes:") withObject:(|⌘|'s class "NSIndexSet"'s indexSetWithIndex:(columnNumber - 1))
end deleteColumn