Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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