GeniusConnect support forum
GeniusConnect support forum
Home | Profile | Register | Active Topics | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Database
 MS SQL Server
 exporting email to SQL Server
 New Topic  Reply to Topic
 Printer Friendly
Author  Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic

WimP

9 Posts

Posted - 09 Jan 2020 :  16:10:15  Show Profile  Edit Topic  Reply with Quote  View user's IP address  Delete Topic
the emails that we will be exporting to SQL Server are standardized such that the body of the email will always contain 3 lines and a value for each line. For example;
Status: Succeeded
Nr of items: 3
Time for completion: 1:17

My question is whether Genius Connect has the ability for us to be able to parse those 3 lines into 3 separate fields in the database record.

Admin

Czech Republic
1708 Posts

Posted - 09 Jan 2020 :  16:13:22  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
GeniusConnect has no parsing support of body text, but once you have the email in your DB, you can create a View or stored procedure to export or View the body data. Here an example of parsing body in SQL Server for address parts:

select
LOWER(dbo.ToString(Body, 'City=', 'City :')) AS City,
LOWER(dbo.ToString(Body, 'Country=', 'Country :') AS Country
from XXXXX


the dbo.ToString is a custom function that will return a value for a specific column title (Marker):

CREATE FUNCTION [dbo].[ToString] (@buffer ntext, @Marker1 varchar(1000), @Marker2 varchar(1000))
RETURNS varchar(200)
AS
BEGIN

DECLARE @converter varchar(200)
DECLARE @Marker varchar(1000)
DECLARE @len int

DECLARE @index int

SET @index = CHARINDEX(@Marker1, @buffer)

if (@index > 0)
BEGIN
SET @Marker=@Marker1
SET @len=LEN(@Marker1)
END
ELSE IF (@Marker2 IS NOT NULL)
BEGIN
SET @index = CHARINDEX(@Marker2, @buffer)

if (@index > 0)
BEGIN
SET @Marker=@Marker2
SET @len=LEN(@Marker2)
END
END

if (@index > 0)
BEGIN

SET @converter=LTRIM(SUBSTRING (CONVERT(varchar(2000),@buffer) , @index+@len,100 ) )
SET @index = CHARINDEX(CHAR(10), @converter)

if (@index > 0)
BEGIN
SET @converter=RTRIM(LEFT (@converter , @index ))
SET @converter=REPLACE(@converter , CHAR(9), '' )
SET @converter=REPLACE(@converter , CHAR(10), '' )
SET @converter=REPLACE(@converter , CHAR(13), '' )

END

END

RETURN(@converter)
END


Go to Top of Page

Admin

Czech Republic
1708 Posts

Posted - 09 Jan 2020 :  16:16:03  Show Profile  Edit Reply  Reply with Quote  View user's IP address  Delete Reply
when you are sure that your mail body is always the same, it can be much simpler. This example function is trying to remove tab, carriage return chars etc..., since the body was not always the same (different character sets, sending mail clients etc..)

if the nr. of emails is huge, it can be better to have GeniusConnect insert the email in some sort of stack table, and to create an insert trigger on this table, that will re-insert parsed body to a table with named columns, in this example column City and Country.
Using a View with parsing will work but can get very slow with huge number of records, when using insert triggers, it will parse only once during the insert.


Example trigger:

CREATE TRIGGER iParseTrigger on myStackTable
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO myRealTable (ID, City, Country)
SELECT ID, LOWER(dbo.ToString(Body, 'City=', 'City :')),
LOWER(dbo.ToString(Body, 'Country=', 'Country :')
FROM inserted

SET NOCOUNT OFF

END
Go to Top of Page
   Topic Next Topic Lock Topic Edit Topic Delete Topic New Topic Reply to Topic
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
GeniusConnect support forum © Copyright (C) 2003 - 2020, GeniusConnect, All Rights Reserved. Go To Top Of Page
Snitz Forums 2000