0

I have a CSV filled with text messages, and I want to be able to easily filter by sender/receiver. The last field indicates "SMS", "iMessage" or "WhatsApp"; the previous fields may spill over multiple lines.

E.g: "Sent","Aug 4, 2021 12:34:56","Some Person","+11234567890","Hello!","SMS"

So I can easily :g/.*Some Person.* to get everything with that person. But if the message ends up being long enough to spill over into multiple lines, I need to tell global to keep matching until it hits that last field. E.g.:

"Sent","Aug 4, 2021 12:34:56","Some Person","+11234567890","Hello!
Nice
to
see you","SMS"

I've tried things along the lines of:

:g/\"Sent.*Some Person\(.*\)\"SMS|\WhatsApp\|iMessage\"

however it seems like because the .* only matches until newline, this only matches one full line at most.

What's the correct way around this?

EDIT 1: I've also tried g:/.*some person.*\_\"imessage\|sms\|whatsapp\"\{-}: it matches lines that don't only contain "some person". I have ignorecase set.

EDIT 2: here's a sample.

"Received","Nov 26, 2021 15:00:28","Foo Bar cell","11234567890","A couple of words","WhatsApp"
"Sent","Nov 26, 2021 14:57:22","Foo Bar cell","11234567890","Word word word word
Sentence goes here
another ones goes here","WhatsApp"
"Sent","Nov 26, 2021 14:56:52","Dad Cell","11234567890","Word word word word. Word!","WhatsApp"

I should be able to search for messages from 'Foo Bar cell' and have it return lines one through four (the first two messages).

12
  • 1
    Just guessing but it might have something to do with :global only marking the first line of a multi-line match (meaning the associated command will only affect that line...try it with :g//d to see what I mean). That's why I recommend sticking to one line per record.
    – B Layer
    Jan 6 at 5:06
  • 1
    I'm not sure how this would work with the line-based mechanism of :g. Naively, if what :g does is check whether each line matches the pattern, then the middle lines do not match the pattern, since it's the combination of all of those lines that does match. You should probably rework whatever you're doing with :g to instead read up until the message ends.
    – muru
    Jan 6 at 6:46
  • 1
    @Harv I wouldn't say it ignores that - I'm saying that whatever operation :g would do is still only on the first line, because that's where the pattern matches - g has no idea other lines were gobbled up by the regex. I meant that if you were doing something like :g/.../ w some-file, instead of just w file, you should probably use a command there that would instead start on the line on which g would run and end on the message ending line. But that depends on what you you're doing with :g here, and you haven't shown us that.
    – muru
    Jan 6 at 8:35
  • 1
    Then you could try something like g/"Sent.*Some Person/ s/^\_.\{-}\(SMS\|WhatsApp\|iMessage\)"\n// to remove the matching lines, perhaps. It'll be trickier to keep only them instead
    – muru
    Jan 6 at 11:01
  • 1
    @Rich you're absolutely right. I'm updating the question now with a sample.
    – Harv
    Jan 11 at 9:49

4 Answers 4

2
+100

Match starting line by global. Then by range until the closing pattern.

g/"Some person"/,/"\(SMS\|iMessage\|WhatsApp\)"/p

Upd. If full match can consist of a single line only then we have to use a little longer construction. For example,

g/"Some person"/exe search('\v"(SMS|iMessage|WhatsApp)"', 'n')->printf(',%dp')
3
  • That can include the following line because (most often) the line that matches the starting line also has the closing pattern in it. That said, I think this is the closest to what I'm looking for, so far.
    – Harv
    Jan 10 at 4:44
  • Ok, the updated version works. It steps outside regular vim I feel, is it possible to have it push everything onto one line for easier further processing? I don't really understand exactly what it does.
    – Harv
    Jan 12 at 10:04
  • @Harv This is mostly the same as the first version, except it makes use of :h search() instead of :h :/. The whole point is that the second "slash" always starts looking for pattern at the next line, while we need to check the current (i.e. the one that "global" has brought us onto). Therefore, we evaluate function "search" instead and take its result (line no) to compose a command. In order to do this we format a string and make engine to re-interpret it as a command (a very common pattern for interpreted languages). If you want it to do something else just change the format string.
    – Matt
    Jan 12 at 11:20
2

OK, lets do tricks. Suppose you don't have the word NEWLINE on your file. Then:

:%s/\n/NEWLINE/g
:%s/"NEWLINE"/\r/g

Then you can do

:g/\"Sent.*Some Person\(.*\)\"SMS|\WhatsApp\|iMessage\"

And finally, replace it back...

:%s/NEWLINE/\r/g
14
  • Not sure you can do that because there's no way to tell the difference between an internal newline and a newline that terminates an entry.
    – Jake
    Jan 9 at 3:13
  • I get E486: Pattern not found: <c-v><CR>
    – Harv
    Jan 9 at 7:17
  • I get that error as well, but \n works, but overall this wouldn't work for the problem
    – Jake
    Jan 9 at 11:21
  • Jake, there is a way, see the second line if there is a " afterwards \n afterwards \n it is between entries .. I meant you should type <c-v> and enter.. \n works maybe(wasn't sure). But why doesn't it work?
    – eyal karni
    Jan 9 at 14:27
  • @eyalkarni I think this gets close, but unfortunately it's written in a way that's difficult for me to follow. Do you mind please looking at what you wrote and checking if it's accurate? Especially line #2 ("NEWLINE" won't be matched - also why immediately reverse the effects of line #1?), and #3 doesn't work.
    – Harv
    Jan 10 at 9:45
1

This might be overkill, but you can write vim script file with the following contents:

let col = 3
let pattern = 'Some Person'

let line = 1
let numlines = line('$')
while line <= numlines
    let str = getline(line)
    let entry = str
    let items = []
    while str != ""
        let item = matchstr(str, '^\("[^"]*",\?\|"[^"]*\|[^,]*,\|[^,]\+\)')
        let str = str[strlen(item):]
        if item =~ '^"[^"]*$'
            let line += 1
            while line <= numlines
                let str = getline(line)
                let entry .= "\n" . str
                let endquote = matchstr(str, '^[^"]*",\?')
                if endquote != ""
                    let item .= "\n" . endquote
                    let str = str[strlen(endquote):]
                    break
                else
                    let item .= "\n" . str
                    let line += 1
                endif
            endwhile
        endif
        let item = substitute(item, '^"\|",$\|"$\|,$', '', 'g')
        call add(items, item)
    endwhile
    if len(items) >= col && items[col - 1] =~ pattern
        echo entry
    endif
    let line += 1
endwhile

This script will parse the .csv using regexes. Then edit the variables col and pattern at the top to set which column you want to search, and what pattern to search for. Then open the .csv file in vim, then run the command:

source script.vim

and it will print the full entries that match the pattern.

1

You can solve this with positive look-ahead in your pattern. The positive look-ahead matches an atom without actually consuming the matched text. You can use it to define the structure of a CSV record with optional line breaks. After the record has been matched, the remaining text will again be searched for from the start of a record:

:g/^\(\("\(\_[^"]\|""\)*"\(,\|$\)\)*\)\@=.*Some Person.*/p

or, if Some Person may also be broken into two lines, use \_s instead of a simple space:

:g/^\(\("\(\_[^"]\|""\)*"\(,\|$\)\)*\)\@=.*Some\_sPerson.*/p

The first part of the pattern up to and including \@= matches a CSV record consisting of zero or more fields. Fields are delimited by quotes and can include any character (including new-lines) except quotes. Quotes inside a field need to be doubled. After a field a comma is expected to separate fields from each other, or an end-of-line must follow which terminates the record.

After a record has been found, the following .*Some\_sPerson.* is again tested from the start of the line.

If you want to restrict the matched record to those of specific message types (and the message type is in the last field), you need to modify the look-ahead part:

:g/^\(\("\(\_[^"]\|""\)*",\)*"\(SMS\|WhatsApp\|iMessage\)"$\)\@=.*Some Person.*/p

Note that you need parentheses around SMS\|WhatsApp\|iMessage. Otherwise any line that contains "WhatsApp" or "iMessage" will match.

Edit: You can get rid of most of the backslashes if you put \v at the start of the pattern. After that only the underscore needs a backslash for its special meaning:

:g/\v^(("(\_[^"]|"")*",)*"(SMS|WhatsApp|iMessage)"$)@=.*Some\_Person.*/p
3
  • 1
    So the person's name will always be on the initial line, but the last field in the record is the message type (SMS/WhatsApp/iMessage); this should be matching everything from the beginning (denoted by "Sent"/"Received") up to and including the last field (message type), filtered by person. In its current form it is matching on the person but failing to get the last field if that last field is not on the first line; e.g. if there is a line break inside the message field. I hope that makes sense.
    – Harv
    Jan 10 at 22:12
  • It's difficult to let :g work on "records" which may or may not contain line breaks. If one uses :g/pattern1/,/pattern2/... (with a comma to separate the patterns) the second pattern is searched for from the current cursor position on, and if one uses :g/pattern1/;/pattern2/... (with a semicolon) the search for the second pattern starts after the line where the first pattern matched. Do you need to keep the line breaks? If not, you can run :%s/\v^(("(\_[^"]|"")*",)*"(SMS|WhatsApp|iMessage)"$)/\=substitute(submatch(0), '\n', ' ', 'g')/ to clean up your file and then ... Jan 11 at 12:47
  • ... do a simple :g/Some Person/... on the resulting lines. Jan 11 at 12:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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