Applescript loop to parse CSV and inject values

Question

I am trying to loop through each comma separated value in a file and have each item undergo a series of actions. Essentially the workflow is this:

CSV file:
123,456,789

Workflow:
[1] Take '123'
[2] Inject '123' into a text field
[3] Click on buttons
[4] Repeat until (and including) last value in CSV

My current code is as follows, but I'm not being able to correctly loop or inject the current item into the text field.

Any pointers you could give me?

set pathInputFile to (choose file with prompt "Select the CSV file" of type "csv")

set strFileContents to read pathInputFile

set parFileContents to (paragraphs of strFileContents)
set numRows to count of parFileContents

repeat with iPar from 1 to number of items in parFileContents

    set lstRow to item iPar of parFileContents
    if lstRow = "" then exit repeat -- EXIT Loop if Row is empty, like the last line

    set lstFieldsinRow to parseCSV(lstRow as text)

    ----------
    -- now I would need to pass the current value to the next block
    -- and "paste" it into the text field
    ----------

    tell application "System Events"
        tell process "App"
            set frontmost to true
            click menu item "Query Window..." of menu "Network" of menu bar 1
            click radio button "Number" of tab group 1 of window "Query"
            set focused of text field 1 of tab group 1 of window "Query" to true
            delay 0.1
            keystroke "a" using command down
            delay 0.1
            keystroke "v" using command down
            click UI element "Query" of group 4 of splitter group 1 of window "Query"
        end tell
    end tell

end repeat -- with iPar

EDIT:

set pathInputFile to (choose file with prompt "Select the CSV file" of type "csv")
set strFileContents to read pathInputFile
log strFileContents

Output:
(*147782
167482
182676
185309
184119
188494*)

What delimiter should I use in this case?

vadian · Accepted Answer · 2016-06-04 14:47:24Z

2

If the CSV is simple CSV (without double quotes as additional field separators) it's also simple to separate the items with AppleScript's text item delimiters.

set theCSV to "123,456,789"
set {TID, text item delimiters} to {text item delimiters, ","}
repeat with anItem in (get text items of theCSV)
    display dialog anItem buttons {"OK"} default button 1
end repeat
set text item delimiters to TID

If all fields are wrapped in double quotes you can use this

set theCSV to "\"123\",\"456\",\"789\""
set trimmedCSV to text 2 thru -2 of theCSV

set {TID, text item delimiters} to {text item delimiters, quote & "," & quote}
repeat with anItem in (get text items of trimmedCSV)
    display dialog anItem buttons {"OK"} default button 1
end repeat
set text item delimiters to TID

The data structure in the EDIT part seems to be line separated (one item per line) not really CSV. In this case neither text item delimitersnor a repeat loop is needed. paragraphs of reads the text line separated (it considers LF, CR and CRLF).

set strFileContents to paragraphs of (read pathInputFile)

edited Jun 4, 2016 at 14:47

answered Jun 4, 2016 at 6:58

vadian

286k32 gold badges378 silver badges379 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

pepe Over a year ago

it looks like my csv file contains quotes, unfortunately -- how would you modify your approach?

vadian Over a year ago

Do all fields contain quotes or only a few? I updated the answer for the case "all" fields.

pepe Over a year ago

thx so much for your help. I am bit baffled by this: on OS X, when I "save as" on Excel as any variety of CSV (CSV, Windows CSV, MS-DOS CSV etc) and then open the file on TextEdit or other pure code editor, the items are not actually separated by commas, but appear as if 1 on each row. Would that pose a problem for using your suggestion?

vadian Over a year ago

CSV doesn't have a uniform specification for the separator character. Excel usually saves the file semicolon-separated.In this case replace the comma in the code with a semicolon.

pepe Over a year ago

I just updated the OP---my data is being ingested into the AppleScript as shown above (no commas or semicolons)--would you have suggestion on what use as a delimiter in this case? [btw nice profile picture of you and your bernese! :)]

Collectives™ on Stack Overflow

Applescript loop to parse CSV and inject values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related