Adding formatting to row delimited email files

Roger

Absolute newbie, I am told this is one of the better C# forums. I have been asked to analyze 800K poorly formatted email files in MS SQL. I think the best way to import them into the database is using something like this http://msdn.microsoft.com/de-de/library/ms345160.aspx, using case statements and the multiple flat files connection manager. To do so I need to format the files by inserting something I can use for the case statement like Row1, Row2, Row3… Is there an efficient way to do this using C#, should I be posting on an SSIS forum? Any thoughts?

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jan Montano17y ago
I anticipated that the Received tag would appear any number of times. In the example below, Received: appeared 3 times. What I would do is create a Received array or list to store the Received: data. I'll read a row and if its a Received: data, I'll put it in Received[0]. I'll then read the next row, It's a received: data so I'll put it in Received[1] now. Then read the next row again, It's not a received data and it's not any tag I expected to read. This time I would assume that it's still part of the previous Received data so I'll append it in Received[1]. Until I read a received Tag or any other tag, I'll keep on appending it to the current received array. Hope this makes sense.

Received: (qmail 53822 invoked from network); 1 Dec 2008 19:23:26 -0000

Received: from as3.mail.mho.net (64.58.4.20)

by q.mx.mho.net with SMTP; 1 Dec 2008 19:23:26 -0000

X-ASG-Debug-ID: 1228160527-5ba901230000-8FHETi

X-Barracuda-URL: http://as1.mho.net:8000/cgi-bin/mark.cgi

Received: from mx4.thesafetea.com (localhost [127.0.0.1])

by as3.mail.mho.net (Spam Firewall) with SMTP id 1CC811200D03

for ; Mon, 1 Dec 2008 12:42:07 -0700 (MST)
Roger17y ago
Hi Jan - Thanks for taking another look. Parsing like that would seem to be problematic, in this example the word "Received" appears 3 times at the beginning of a row. I have seen other files where it shows up 5 times or more. I may also need to analyze some of the rows from the body of the email, and there is no predicting how those would start. That is why I came up with the idea of the Row1, Row2 stuff. Does that not sound feasible?
Jan Montano17y ago
Adding "Row1", "Row2" would be unnecessary.

Why not read the files directly and parse words like:

Return-Path:
Delivered-To:
Received:
Roger17y ago
Here is a sample from one of the files, the first 10 rows of this file:

Return-Path: [email protected]

Delivered-To: [email protected]

Received: (qmail 53822 invoked from network); 1 Dec 2008 19:23:26 -0000

Received: from as3.mail.mho.net (64.58.4.20)

by q.mx.mho.net with SMTP; 1 Dec 2008 19:23:26 -0000

X-ASG-Debug-ID: 1228160527-5ba901230000-8FHETi

X-Barracuda-URL: http://as1.mho.net:8000/cgi-bin/mark.cgi

Received: from mx4.thesafetea.com (localhost [127.0.0.1])

by as3.mail.mho.net (Spam Firewall) with SMTP id 1CC811200D03

for ; Mon, 1 Dec 2008 12:42:07 -0700 (MST)

My thought is to insert "Row1" at the beginning of the first row, "Row2" at the beginning of the second row, "Row3" at the beginning of the third row, etc. I can then use the example at http://msdn.microsoft.com/en-us/library/ms345160.aspx, using the Case statements to load the file to MS SQL Server 2008, putting each row into its own column. I plan on using the Multiple Flat Files Connection manager along with that example code since I have so many files to load. Once I get 'em into the database I can do my analysis and move stuff around in and between columns as necessary. Thanks for any help.
Jan Montano17y ago
Welcome to the forum Roger.

Could you post a very small email sample (unformatted with line breaks and all) that you have? And pls explain what you want to happen with its contents in detail. Thanks!