I’m looking for a simple AppleScript to read/write cells + delete rows and columns in a CSV table.
Features:
I would appreciate any contributions. I have tried several AI-generated scripts, but unfortunately, the code was either too large or did not work (Script Error: Can’t get file …).
You need to read your file as string.
Split the string into lines/rows into csvRows
Create an array of columnKeys
(See below for splitting)
Know if the first csvRow contains the column names,
If not create your own based on the number of columns in first row ie “column1”, “column2”……
Enumerate over each csvRow in csvRows
split csvRow into rowColumns by CSV delimiter type (comma or tab)
Enumerate over each column in the rowColumns and process as you like.
Or create a rowDictionary
with keys from columnKeys
And values from your line’s rowColumns
You have to have a fail safe check to make sure that all of your rowColumns count match the columnKeys count
Add this rowDictionary to a masterArray
Repeat
danielA1. Your request specifies “AppleScript” and “simple”, and I’ve included my suggestion below. My script just demonstrates the basics, and you should be able to work out the rest. I included the CSV table in the script just for testing, but you can easily get it from a file with the read command.
set theTable to "Name,Age,Location
Peavine,78,Prescott
Nigel,20,Warwickshire
Shane,30,Australia"
--create a list of lists
set theLists to paragraphs of theTable
set text item delimiters to ","
set theListOfLists to {}
repeat with aList in theLists
set end of theListOfLists to text items of aList
end repeat
--get the value of a cell
set peavineAge to item 2 of item 2 of theListOfLists
# return peavineAge -->"78"
--write the value of a cell
set item 2 of item 2 of theListOfLists to "25"
set peavineAge to item 2 of item 2 of theListOfLists
# return peavineAge -->"25"
--delete a row
set listToDelete to 3
set newList to {}
repeat with i from 1 to (count theListOfLists)
if i is not listToDelete then set end of newList to item i of theListOfLists
end repeat
# return newList -->{{"Name", "Age", "Location"}, {"Peavine", "25", "Prescott"}, {"Shane", "30", "Australia"}}
--delete a column
set itemToDelete to 2
set newList to {}
repeat with aList in theListOfLists
set tempList to {}
repeat with i from 1 to (count aList)
if i is not itemToDelete then set end of tempList to item i of aList
end repeat
set end of newList to tempList
end repeat
# return newList -->{{"Name", "Location"}, {"Peavine", "Prescott"}, {"Nigel", "Warwickshire"}, {"Shane", "Australia"}}
--make table from list of lists
set newTable to ""
set text item delimiters to {","}
repeat with anItem in newList
set newTable to (newTable & anItem as text) & linefeed
end repeat
# return newTable
--reset text item delmiters to default
set text item delimiters to {""}
Parsing CSV is not trivial, because there is no unique standard.
For example you have to consider that the separation character can occur in a field.
Nowadays there are more reliable formats like JSON
This works (created by Perplexity.ai). Can you modify the code to allow text with a comma (e.g.: 1,2) to be entered into a cell?
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)
Do you consider to use Numbers.app and use AS for it? I have some examples for such solution.
I can’t make up my mind which witty comment to make about those ages! 
The OP has started a new thread on this topic, so I thought it would be OK to look at a JSON alternative to a CSV. Basic AppleScript doesn’t support JSON (as far as I know), which leaves ASObjC or a shortcut. A shortcut is simpler, but constructing the JSON still seems a bit of a chore. Perhaps there is a simple CSV to JSON converter, although it’s difficult to envision how it would work with my test CSV.
The data could be entered into a Dictionary action, but I don’t know if that’s any better.
ASObjC provides NSJSONSerialization which is pretty straightforward
As I said CSV can be complicated. It’s allowed to use the separator character in a field but then you have to wrap the value in double quotes. And if there are even double quote characters in the field along with the separator character you have to escape them.
Thanks Stefan. I’ve never worked with a JSON in ASObjC, and that’s why a shortcut implementation was simpler for me. I’ll try to create–or search the forum for–an ASObjC example that will work with my test JSON later today. If you (or another forum member) have an example that you could post that would be great.
My knowledge of JSON is quite limited, and perhaps my test JSON is poorly written (although it does seem to work well in a shortcut):
{"Peavine":{"age":"78","location":"Prescott"},"Nigel":{"age":"20","location":"Warwickshire"},"Shane":{"age":"30","location":"Australia"}}
This converts JSON to an array oder dictionary. In case of CSV an array (AppleScript list) is preferable
use AppleScript version "2.5"
use framework "Foundation"
use scripting additions
set json to "[{\"name\":\"Peavine\",\"age\":78,\"location\":\"Prescott\"},
{\"name\":\"Nigel\",\"age\":20,\"location\":\"Warwickshire\"},
{\"name\":\"Shane\",\"age\":30,\"location\":\"Australia\"}]"
property |⌘| : a reference to current application
set jsonNSString to |⌘|'s NSString's stringWithString:json
set jsonData to jsonNSString's dataUsingEncoding:(|⌘|'s NSUTF8StringEncoding)
set {theArray, theError} to |⌘|'s ((NSJSONSerialization's JSONObjectWithData:jsonData options:0 |error|:(reference))) as list
Thanks Stefan. So, just for learning purposes, the code to get someone’s age would be the following?
use AppleScript version "2.5"
use framework "Foundation"
use scripting additions
set json to "[{\"name\":\"Peavine\",\"age\":78,\"location\":\"Prescott\"},
{\"name\":\"Nigel\",\"age\":20,\"location\":\"Warwickshire\"},
{\"name\":\"Shane\",\"age\":30,\"location\":\"Australia\"}]"
property |⌘| : a reference to current application
set jsonNSString to |⌘|'s NSString's stringWithString:json
set jsonData to jsonNSString's dataUsingEncoding:(|⌘|'s NSUTF8StringEncoding)
set {theArray, theError} to |⌘|'s ((NSJSONSerialization's JSONObjectWithData:jsonData options:0 |error|:(reference)))
--get Nigel's age
set theName to "Nigel"
set filter to current application's NSPredicate's predicateWithFormat_("self[FIRST] == %@", theName)
set theAge to (((theArray's filteredArrayUsingPredicate:(filter))'s valueForKey:"age")'s objectAtIndex:0) as integer
Yes, it works, but the predicate can be simply
NSPredicate's predicateWithFormat_("name == %@", theName)
And you filter usually the other way round, first get the object at at index then get the value
set theAge to (((theArray's filteredArrayUsingPredicate:(filter))'s objectAtIndex:0)'s objectForKey:"age") as integer