linuxtextfile-conversionmboxmaildir

Convert simple emaildump to maildir or mbox format

In OTRS I've got a database with emails. I have the raw emails (in eml format) in a table called "article_plain".

I can easily do a dump of this using a statement like:

SELECT ap.body FROM article_plain ap 
INNER JOIN article a ON (a.id = ap.article_id) 
INNER JOIN ticket t ON (a.ticket_id = t.id) 
INNER JOIN queue q ON (t.queue_id = q.id) 
WHERE q.name LIKE '%something%' 
LIMIT 10;

Using the commandline:

mysql -u root otrs -pmypassword --silent --skip-column-names --raw -e 
"select ap.body from article_plain ap inner join article a on (a.id = ap.article_id) inner join ticket t on (a.ticket_id = t.id) inner join queue q on (t.queue_id = q.id) where q.name like '%something%' limit 10;" 
>> ./emlconcat_something

I've now exported into a single file with concatenated eml files. This looks something like:

X-Mimeole: Produced By Microsoft Exchange V6.5 Received: from xxxxxxxx.org.za ([10.0.50.12]) by xxxxxx.xxxxxx.org.za with Microsoft SMTPSVC(6.0.3790.4675); Tue, 28 Sep 2010 11:20:22 +0200 MIME-Version: 1.0 Content-Type: multipart/related; type="multipart/alternative"; boundary="----_=_NextPart_001_01CB5EEE.613E57EA" Received: from xxxxxx.xxxxxxx.co.za ([41.208.19.106]) by xxxxxxxx.org.za with SMTP; 28 Sep 2010 09:27:03 +0000 Received: from mail.dtss.co.za (mail.xxxxx.co.za [196.4.89.13]) by xxxxx.xxxxxxx.co.za; Tue, 28 Sep 2010 11:27:00 +0200 Received: from xxxxx-xxxxx.xxxxxx.org.za ([172.16.252.23]) by mail.dtss.co.za with Microsoft SMTPSVC(6.0.3790.4675); Tue, 28 Sep 2010 11:30:44 +0200 Content-Class: urn:content-classes:message Subject: RE: Some subject Date: Tue, 28 Sep 2010 11:24:31 +0200 Message-ID: <7918418270AAB64A84F55F47027EC3135FC0F0@xxx.xxx.xxxx.org.za> X-MS-Has-Attach: yes X-MS-Tnef-Correlator: Thread-Topic: Global Whatever Thread-Index: ActeL4wmcWmZ5plSTzSDPA18WuxM7AAvyzQA References: <01BD0A17B3803A48A04E613E35E0513D4E6C54@xxxxxxxxxx.local> From: "xxxx xxxxxxxx (Gauteng North, Hatfield)" To: "Nellie" , "xxxxxxx xxxxxx" CC: "xxxxx xxxxx" , "xxxxx xxxxxx" , "xxxxx xxxxx" , "xxxx xxxxx (Centurion)" , "xxxx xxxxxx (Gauteng North, Hatfield)"

This is a multi-part message in MIME format.

------_=_NextPart_001_01CB5EEE.613E57EA Content-Type: multipart/alternative; boundary="----_=_NextPart_002_01CB5EEE.613E57EA"

------_=_NextPart_002_01CB5EEE.613E57EA Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable

Dear Whomever

All other emails are just concatenated to the first one (as per the initial select statement).

Question:
How do I transform this into mbox or maildir format?
I.e. what script should I use on the linux server do that?

Solution

  • One solution is to use csplit

    Example:

    csplit -z -k mail_export_file "/Mimeole/" {*}

    The "Mimeole" part should ideally be expanded, but I suck at regex so I leave that as an exercise for the reader.