Read CSV file as Array with AppleScript

Question

I would like to read in a CSV file in as a 2D array and then return it back to a CSV file. Let's say that this is my CSV file. It is an excel file and the | implies the adjacent cell:

family | type
Doctor | Pediatrics
Engineer | Chemical

From what I understand, there are no arrays on applescript, just lists and records. If it would be better to do this with a XLSX file, please let me know.

Solution 1

Nigel's CSV-to-list converter is the best I have seen ...

http://macscripter.net/viewtopic.php?pid=125444#p125444

For your example, use these settings:

set csvText to "family | type
Doctor | Pediatrics
Engineer | Chemical"
csvToList(csvText, {separator:"|"}, {trimming:true})

v2

set csvText to read "/Users/user1385816/Desktop/yourfile.csv"
csvToList(csvText, {}, {trimming:true})

Solution 2

An array is just a list in applescript, so you want a 2d array or a list-of-lists in applescript-speak. If you understand applescript's text item delimiters then your task is just simple manipulation to convert strings into lists and vice versa. So I wrote you a couple handlers to make the task easy for you; textToTwoDArray() and twoDArrayToText(). This first example shows how to convert your string into a list-of-lists using textToTwoDArray().

NOTE: you have to be careful of the line endings in a text file because they can be either a carriage return (character id 13) or a line feed (character id 10). You can see I used character id 10 in my code but if you aren't getting the proper results try "13".

set fileText to "family | type
Doctor | Pediatrics
Engineer | Chemical"

textToTwoDArray(fileText, character id 10, " | ")

on textToTwoDArray(theText, mainDelimiter, secondaryDelimiter)
    set {tids, text item delimiters} to {text item delimiters, mainDelimiter}
    set firstArray to text items of theText
    set text item delimiters to secondaryDelimiter
    set twoDArray to {}
    repeat with anItem in firstArray
        set end of twoDArray to text items of anItem
    end repeat
    set text item delimiters to tids
    return twoDArray
end textToTwoDArray

And here's how to convert a list-of-lists back into your string using twoDArrayToText().

set twoDArray to {{"family", "type"}, {"Doctor", "Pediatrics"}, {"Engineer", "Chemical"}}

twoDArrayToText(twoDArray, character id 10, " | ")

on twoDArrayToText(theArray, mainDelimiter, secondaryDelimiter)
    set {tids, text item delimiters} to {text item delimiters, secondaryDelimiter}
    set t to ""
    repeat with anItem in theArray
        set t to t & (anItem as text) & mainDelimiter
    end repeat
    set text item delimiters to tids
    return (text 1 thru -2 of t)
end twoDArrayToText

So now all you have to do is figure out how to read and write to a text file with applescript. Good luck ;)