Open CSV wtih Excel from Finder

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

While I don't know how to use the pipe character | can I suggest you use "Tab-Delimited Text" which is one of the "Specialty Formats" listed under "Save As...".

Then if you wish you can use tr to change the tabs to pipes. tr '\t' '\|' < file.txt > file_converted.txt at the command line.

Note that most software that exports CSV files (including Excel) wraps a field with a comma in it in quotes (as you mention above). Also note that most software that imports CSV files can usually also perform the unwrap. Finally, you may well find that whatever you are doing with the exported file is perfectly happy with tab-delimited text.