1

don't know how elementary a question like this is, but...

• I have a 2 column csv file that I want to search for a value in col A and then grab its matching value in col B.

• I will store the csv file in an AppleScript bundle to keep things tidy.

Is there a way (search with grep?) to do this, and keep a separate GUI app (Excel, Numbers, etc) out of the equation?

  • You do not need to use a GUI app like Excel, Numbers etc. however why AppleScript when you can use awk, grep etc. in Terminal? It would be more helpful if you'd provide a sample of the contents of the CSV file and an example match you like to make along with additional information on how you expect this to work in AppleScript in that do you want to be prompted for what to search or is this a hard-coded value, etc. – user3439894 Jan 7 '17 at 10:16
  • I think you're right, Applescript seems to bog down with this. [more below] – nuthindoin Jan 9 '17 at 4:50
2

You can use write a script to read your two column CSV file, and then convert it into a list where you'd have one list item for every row in your CSV file, and every list item would itself be a list (column A value, column B value). So, if your CSV file looked like this:

red,apple
yellow,banana
green,pickle
brown,desk
white,sock

It would be converted it to this:

{{red,apple},{yellow,banana},{green,pickle},{brown,desk},{white,sock}}

Then it is easy to loop through the list and find the first item whose first item matches the search term. For example if I'm searching for "brown" I would find "brown" in item 4 of the larger list, and then pick off item 2 of item 4 of the larger list, resulting in "desk."

Here is a script that asks you to choose a CSV file, then asks you for the search term (the thing you want to find in Column A). It then displays the Column B value in a dialog box. This may not completely solve your problem, but it does answer your question regarding searching a CSV file using AppleScript and not Excel or Numbers.

    tell application "Finder"
        set the_file to choose file
    end tell

    set my_data to read the_file
    set my_list to paragraphs of my_data as list
    -- we need to make a list of lists... each item in my_list needs to be a list of two items.
    set new_list to {}
    -- this is housekeeping
    set oldDelims to AppleScript's text item delimiters
    set AppleScript's text item delimiters to ","
    -- /housekeeping
    --
    --make the list look right
    repeat with an_item in my_list
    -- inserting "try" statement to catch blank lines
    try
        set x to text item 1 of an_item
        set y to text item 2 of an_item
        set component_list to {x, y}
        set end of new_list to component_list
    end try
    end repeat
    set AppleScript's text item delimiters to olddelims

    -- now you have a list with each item in the list
    -- being Columns A and B of one line in the CSV file
    --
    -- Bringing Finder to the front to make dialog boxes show more easily
    tell application "Finder"
      activate
      set the_search_term to display dialog "What are you looking for?" default answer "red"
      set the_search_term to text returned of the_search_term

      repeat with some_item in new_list
          if item 1 of some_item is the_search_term then
              display dialog "Column B value is: " & item 2 of some_item
              return
          end if
      end repeat
    end tell
  • Using your example CSV file and your AppleScript code, it errors out in the first repeat loop with: error "Can’t get text item 1 of \"\"." number -1728 from text item 1 of "" – user3439894 Jan 7 '17 at 10:12
  • Of course you are right. When I posted the answer I neglected to format the sample CSV file properly, so instead of looking like A,B (return) A,B (return) it was all in one line. Thanks for cleaning up my answer. I learned something about formatting answers today thanks to you. – Christian Boyce Jan 7 '17 at 18:53
  • You code errors out with a properly formatted CSV file! – user3439894 Jan 7 '17 at 19:00
  • I'll edit the original answer and get it right. There, done. I even copied the script out of my answer here (as published) and ran it. – Christian Boyce Jan 7 '17 at 20:47
  • Sorry but your modified code still errors out at the same place with the same error. I've tried your code under both OS X 10.8.5 and macOS 10.12, both copying the content for the CSV file from the post as well as creating it manually in a Spreadsheet saving it as a CSV file! – user3439894 Jan 7 '17 at 21:06
0

I've rewritten the script to be much more efficient (and a LOT faster). I thought it would be useful to see the original as well as this one so I am posting this as a second answer.

It's a different approach. First I split the CSV file into TWO lists-- one called ColumnA_list and one called ColumnB_list. Then I find the search term in ColumnA_list and note its position. Then I go straight to the corresponding item in ColumnB_list. This eliminates the second loop in the script, speeding things up a LOT.

tell application "Finder"
    set the_file to choose file
end tell
--
set my_data to read the_file
set my_list to paragraphs of my_data as list
-- we need to make two lists: ColumnA, and ColumnB
set ColumnA_list to {}
set ColumnB_list to {}
-- this is housekeeping
set oldDelims to AppleScript's text item delimiters
set AppleScript's text item delimiters to ","
-- /housekeeping
--
--make the lists
repeat with an_item in my_list
    -- inserting "try" statement to catch blank lines
    try
        set end of ColumnA_list to text item 1 of an_item
        set end of ColumnB_list to text item 2 of an_item
    end try
end repeat
set AppleScript's text item delimiters to oldDelims
--
-- now you have two lists.
-- we will search ColumnA_list for the search term, then locate the corresponding item
-- in ColumnB_list
--
-- Bringing Finder to the front to make dialog boxes show more easily
tell application "Finder"
    activate
    set the_search_term to display dialog "What are you looking for?" default answer "red"
    set the_search_term to text returned of the_search_term
end tell
--
-- Now we find the line number of the item in ColumnA_list matching the earch term
set the_position to indexof(the_search_term, ColumnA_list)
-- "indexof" is Emmanuel Levy's routine-- thanks Emmanuel!
if the_position is 0 then
    tell application "Finder"
        activate
        display dialog "The search term does not exist in Column A."
    end tell
    return
end if
-- now we know which line has the search term, so we can specify the corresponding
-- item in ColumnB_list
tell application "Finder"
    display dialog "Column B value is: " & item the_position of ColumnB_list
end tell
--
-- This is Emmanuel Levy's routing
on indexof(theItem, theList) -- credits Emmanuel Levy
    set text item delimiters to return
    set theList to return & theList & return
    set text item delimiters to {""}
    try
        -1 + (count (paragraphs of (text 1 thru (offset of (return & theItem & return) in theList) of theList)))
    on error
        0
    end try
end indexof

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.