Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I would like to search a file only on the first column based on a set of IDs from another file. Copy and paste the results to another output file.

For example , My first file contains

ABHA-PHY49M

ABHA-PHY49N

ABHA-PHY49O

My second file contains

"ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHY49N";"Hi, Some mail contents1 Kind Regards,"

"ABHA-8RXIYY";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHY49N";"Hi, Some mail contents2 Kind Regards,"

"ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHYCCC";"Hi, Some mail contents3 Kind Regards,"

"ABHA-8RXIXX";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHYDDD";"Hi, Some mail contents4 Kind Regards,"

I need to retrieve all lines matching the ids from the first file with the first column value. I cant use excel because there is a field called Verbatim where the contents could be free text and would contain CR+LF etc which destroys the formatting when opening with Excel. I would like to know if we can write/record a macro to achieve this. All I can think of is to search the ids(obtained from file1) and search in the first column , extend the selection and copy the data. Not sure if this is possible or how to do it.

In the above example , just two rows will be selected because of the Id ABHA-PHY49M

"ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHY49N";"Hi, Some mail contents1 Kind Regards,"

"ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHYCCC";"Hi, Some mail contents3 Kind Regards,"

share|improve this question

Have you tried a regular expression search? If it is definitely the first column you're searching in, then this should work:

^"ABHA-PHY49M".*$
  • The first character ^ ensures that it will only search at the beginning of the line.
  • The literal characters "ABHA-PHY49M" match the string you're looking for.
  • The remaining characters .*$ include the entire rest of the line.

The search results I get, from your sample data, look right:

Search "^"ABHA-PHY49M".*$" (2 hits in 1 file)
  new  5 (2 hits)
    Line 1: "ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHY49N";"Hi, Some mail contents1 Kind Regards,"
    Line 5: "ABHA-PHY49M";"Blah blah";"Ashwin";"";"10/14/2 009 10:36:29";"ABHA-PHYCCC";"Hi, Some mail contents3 Kind Regards,"
share|improve this answer

There is SQL Plugin allows execute SQL database commands in CSV files. In your case query would be:

 select * from data where id="abha-phy49m"

if first column is called "id" (commands case insensitive). This will retrieve all lines from current file where id is "abha-phy49m"

EDIT: Unfortunately it converts output text to lowercase

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .