CODEWITHSUNDEEP
excelmacoscsvapplescript
user1385816
user1385816

Reputation: 99

Read CSV file as Array with AppleScript

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.

Upvotes: 5

Views: 11141

Answers (3)

adayzdone
adayzdone

Reputation: 11238

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})

Upvotes: 7

regulus6633
regulus6633

Reputation: 19030

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 ;)

Upvotes: 4

kopischke
kopischke

Reputation: 3413

If your question is just about modeling your CSV data in AppleScript, the solution is to use a list of records like this:

set csvData to {{family:"Doctor", type:"Engineer"}, {family:"Engineer", type:"Chemical"}}

you can then read that out again inside a repeat with aRow in csvData loop.

A technical note: AppleScript lists are implemented as vectors, meaning they are ordered and can be accessed by numerical index (beginning at index 1) – they behave much like an array in most other languages.

Upvotes: 0

Related Questions