Read incoming email and convert it into database format

[ZeitenWanderer]

Hi again,

this is my task: I receive an order-email sent by a customer of mine, format is reliable. I do not use any mail-prog, but the function provided by MS, so the mail always ends up in a txt-file. Here is an example of the body:


At first plenty lines, I have no use for. Then, always starting in the same line 13, what I am actually waiting for:

Kein Autor angegeben: 25 Jahre Gemeinde Brunsbek, Oho Bad Oldesloe 2001 272 S.; Die Geschichte der Dörfer Papendorf, Kronshorst, Langelohe


I need to interpret it like this:
ReadLn 13 up to sign "", save temporarily

Afterwards, I will put content 1 and 2 into a file, same line, divided by TAB.

I have been running through the help and also through the forum, surely, I am not the first one to automate realiable email content .... But I could not find any fitting example.

Probably some other folks would find a couple hints on this interesting too ...

Thanks for caring everyone.

[Bob Hansen]

You may need to be using Regular Expressons (RegEx) to parse the file you have returned. Hard to tell without actual file sample.

TextPad has pretty good RegEx Search/Replace tools that I use frequently.
More info available at http://www.textpad.com/products/textpad/index.html

You could also consider using VBScript RegEx function.

[Me_again]

IF it's always line 13, and IF it's always as simple as getting the part before the first "", and IF it's a text file, then this code will do it. But in my experience life's never that easy so this may need some tweaking

//read line 13
ReadLn>c:\test\emailtest.txt,13,line
MDL>%line%
//find the ""
Pos>>,line,1,caret2
MDL>%caret1% %caret2%
//adjust the positions to avoid getting the in the output
Let>caret2=caret2-1
Let>len=caret2-caret1
Let>caret3=caret1+1
Let>caret1=caret1-1
//get the part before the line,1,caret1,part1
//get the part between the
MidStr>line,caret3,len,part2
MDL>%part1% %part2%

[Bob Hansen]

Thanks for the script Me_again.

As you mentioned "IF" has big connotation. I was contacted offline re this issue and I asked for three copies of the files to be parsed. Bad news is that the data is not on line 13. Good news is that is was on line 35, on all three samples.

I haven't tried your code yet, but will assume it is good. In that case, only need to change
FROM:
ReadLn>c:\test\emailtest.txt,13,line
TO:
ReadLn>c:\test\emailtest.txt,35,line

In this case, I suspect line 13 was referenced because it was the 1 VISIBLE line, but all of the email header was also in the file, unseen.

Lesson learned here:
It is always best to submit actual data to be used vs. trying to explain in words.

[ZeitenWanderer]

Sorry, I have to set things a little clearer here: It is indeed line 13, if you pick up the pop3 email via MS as I intend to do. In that case the body text is cut off - just the way I would want it to be. Otherwise I would have to tolerate all kinds of different headers, which would make coding a lot harder.

Thus it is of absolutely no matter, what kind of mail-prog anyone uses.

Since I forwarded the mails as attachments, they of course included the headers.

[Aaron]

Hello ZeitenWanderer, I was just curious, Did this work for you

[ZeitenWanderer]

All right. That really did the trick. I never would have found out myself. What a relief .....

Of course, there is still a lot of boring leg-work left for me to do (what other coders would not care for), but the basic problem - as far as is interesting for a forum - is elegantly solved.

In short again - anyone, who needs to distribute a solution, including the picking up of pop3-emails (imap is a different story, I am not thinking about at the moment) should think about avoiding the local email-prog by using the given pop3 example in the help-file. That slices the incoming mail into its "ingredients". Distribution starts as soon as you have two PC`s .... e.g. you want to show your script off, use it in the office also etc.

[ZeitenWanderer]

Yes, as was estimated already, life is indeed a little more complex than we suppose sometimes.

I need a hint on how I find and read a certain line by content.

Means, I am searching for the line
"Gesamtbetrag: ...... EUR 9,99"
from where I would need to pick up the sum. In this case I can not predict the absolute line, it might be 33 as well as 35 or whatsoever, but the line in itself from "Gesamtbetrag:" to the last dot is always of the same length.

I checked all the email-formats from all the forums I recieve book orders from and so far, I guess, this would be the last question, concerning reading the content from predictable emails.

[Marcus Tettmar]

I would use regular expressions:

Code: Select all

//A VBScript Function to search a string for a regex pattern
VBSTART
Function regExSearch(patrn,str)
  Set regEx = New RegExp ' Create regular expression.
  regEx.Pattern = patrn ' Set pattern.
  regEx.IgnoreCase = True ' Make case insensitive. Default=False
  Set matches = RegEx.Execute(str)
  if matches.count > 0 then
  	 regExSearch = matches(0).value
  end if
End Function
VBEND

//Read the file contents into a variable
ReadFile>YourFile.txt,FileData

//replace CRLF chars with VBScript equivalents
StringReplace>FileData,CR," & vbCR & ",FileData
StringReplace>FileData,LF," & vbLF & ",FileData

//Search for the line beginning "Gesamtbetrag"
VBEval>regExSearch("Gesamtbetrag.*","%FileData%"),TheLine

//Extract just the value
StringReplace>TheLine,Gesamtbetrag: ...... EUR ,,TheValue