Quotes to text file in Linux

quotes

#1

UPDATE: Since the quote table starts with quote #0, I put a random quote I don’t care about in the the 0 row, and updated the query to only return values starting with quote id=1. That way, the line numbers added by sed actually match up with what chatters will query!

In case you’re in the same boat, here’s the command I came up with to export the quotes to a text file in Linux (using sqlite3 tools, grep, and sed)…

sqlite3 -list -noheader -readonly /path/to/your/phantombot.db "select variable,value from phantombot_quotes where variable !=0;" | grep -Po '(?<=",")(.*)(?=",[0-9]{13})' | sed = $1 | sed 'N;s/\\"/"/g;s/\n/. /' > /path/to/your/quotes.txt

…and here’s what it all means!

sqlite3 -list -noheader -readonly /path/to/your/phantombot.db "select variable,value from phantombot_quotes where variable !=0;" | 

This uses sqlite3 to query the phantombot database, return everything in the quotes table, and pipe it to…

grep -Po '(?<=",")(.*)(?=",[0-9]{13})' |

This isolates the quotes themselves, without any extra info (like who submitted it, the timestamps, or the game) and pipes it to…

sed = $1 |

This adds line numbers to the quotes, but above each line (which we’ll fix next), and pipes it to…

sed 'N;s/\\"/"/g;s/\n/. /' > /path/to/your/quotes.txt

This replaces any escaped double-quotes (which are what come out of the database) with plain double-quotes, puts the line numbers on the same lines as the quotes, adds a period after the line number (it replaces the new-line character after the line number with a period-space combo), and dumps the output into a file called “quotes.txt”

GREP DETAILS:
-P = Perl Regexp (which you need for the “look behind” and “look ahead” regexp bits)
-o = show only the matching bits, not the whole line

REGEXP DETAILS:
(?<=",") is the positive look behind. It looks for the first instance of this -> “,” <- and ignores everything before it
(.*) is what’s matching your quote
(?=",[0-9]{13}) is looking for the close quote, plus a comma, plus the 13-digit timestamp, and ignore it, and everything after

DISCLAIMER:
I’m not an expert in regular expressions, and sed just looks like witchcraft to me, so if you’re wondering how to tweak this to work for whatever your use case it, Google is better than me!