1. GeniusConnect support
  2. Database
  3. MS SQL Server
  4. exporting email to SQL Server
WimP
2020-01-09T16:10:00Z
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.
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2020-01-09T16:13:00Z
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
Administrator
2020-01-09T16:16:00Z
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
 Atom Feed Rss Feed
Similar Topics
  1. GeniusConnect support
  2. Database
  3. MS SQL Server
  4. exporting email to SQL Server