Writing onto a CSV column on applescript

Question

So I'm creating a script in AppleScript that needs to write onto a column in a CSV. I've already read from the CSV and I have code on how to write onto a CSV but it doesn't write at all. I want to write on column 22. At the moment, I can read from that column but I'm not sure how to edit the column. Or is there a way to edit a certain box in excel like A3 to say this "Yes". I've attached my code below.

set theFile to (choose file)
set f to read theFile

-- break the file into paragraphs (c.f. rows)
repeat with row in (paragraphs of f)
    -- parse the row into comma-delimited fields
    set fields to parseCSV(row as text)
    -- now you have your data:
    set verifyAccountStatus to item 22 of fields
    set theResult to writeTo(verifyAccountStatus, "Yes", text, true)

end repeat

on parseCSV(theText)
    set {od, my text item delimiters} to {my text item delimiters, ","}
    set parsedText to text items of theText
    set my text item delimiters to od
    return parsedText
end parseCSV

on writeTo(targetFile, theData, dataType, apendData)
    -- targetFile is the path to the file you want to write
    -- theData is the data you want in the file.
    -- dataType is the data type of theData and it can be text, list, record etc.
    -- apendData is true to append theData to the end of the current contents of the file or false to overwrite it
    try
        set openFile to open for access file targetFile with write permission
        if apendData is false then set eof of openFile to 0
        write theData to openFile starting at eof as dataType
        close access openFile
        return true
    on error
        try
            close access file targetFile
        end try
        return false
    end try
end writeTo

EDIT: For some reason it isn't working when I run this.

set theFile to (choose file)
set openFile to open for access theFile with write permission
do shell script "awk -F, '$1 ~ /Line 7/{$22=\"hello\"} 1' openFile"
close access openFile

It says it can't open file openFile

Answer 1

Maybe consider doing this using awk which is built-in to OS X anyway. Say your file is called file.csv and it is comma-separated and looks like this:

Line 0,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,16
Line 1,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,44
Line 2,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,93
Line 3,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96
Line 4,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,19
Line 5,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,77
Line 6,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,30
Line 7,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,30
Line 8,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,1
Line 9,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96

If you run this awk script in the Terminal, it will change all the field22 entries to "Hello"

awk -F, '{$22="hello"}1' OFS=, file.csv

and give you this:

Line 0,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,16
Line 1,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,44
Line 2,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,93
Line 3,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,96
Line 4,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,19
Line 5,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,77
Line 6,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,30
Line 7,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,30
Line 8,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,1
Line 9,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,hello,f23,f24,96

If you want that in a new file, just redirect the output like this:

awk -F, '{$22="hello"}1' OFS=, file.csv > newfile.csv

You can call this from Applescript using:

do shell script "awk -F, '{$22=\"hello\"} 1' file.csv > newFile.csv"

If you want the user to choose a file from a dialog window, then your filename will be in an Applescript variable - in your example it is called theFile. You will then need to convert this to a filename that the shell and awk understand, and quote it and pass it to the shell something like this:

do shell script "awk -F, '{$22=\"hello\"} 1'  & quoted form of POSIX path of theFile & " > /tmp/a"

If you only want to change field 22 on lines that contain "77" anywhere on them, do this:

awk -F, '/77/ {$22="hello"} 1' file.csv

Line 0,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,16
Line 1,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,44
Line 2,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,93
Line 3,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96
Line 4,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,19
Line 5 field2 field3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 hello f23 f24 77
Line 6,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,30
Line 7,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,30
Line 8,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,1
Line 9,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96

If you only want to change field22 on lines where field 1 matches "Line 7", you can do this:

awk -F, '$1 ~ /Line 7/ {$22="hello"} 1' file.csv
Line 0,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,16
Line 1,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,44
Line 2,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,93
Line 3,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96
Line 4,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,19
Line 5,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,77
Line 6,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,30
Line 7 field2 field3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 hello f23 f24 30
Line 8,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,1
Line 9,field2,field3,f4,f5,f6,f7,f8,f9,f10,f11,f12,f13,f14,f15,f16,f17,f18,f19,f20,f21,f22,f23,f24,96

Note:

The -F, just tells awk that input fields are separated by commas. The OFS=, tells awk to separate output fields by commas.