Parker Software Ltd Homepage
Forum Home Forum Home > Email2DB Email Parser > Samples
  New Posts New Posts RSS Feed: CSV process - using repeating items
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

CSV process - using repeating items

 Post Reply Post Reply Page  12>
Author
Message
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 879
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Topic: CSV process - using repeating items
    Posted: 23 Jul 2008 at 11:04am
This is a sample for extracting an e-mailed piece of CSV and putting it into a database.

Here is the sample csv I am working from:
Name,Date,Email,Amount 
Daniel Tallentire,2008-04-03,daniel@test.com,15.63
"S. Parker, Jr",2008-04-02,s@test.com,115.74
A. Hughes,2008-04-01,a@test.com,153.63
Mark Robbs,2008-08-03,m@test.com,13.73
Ian Green,2008-07-03,i@test.com,1566.02

1. Create your account, (don't set up your message retrieval yet).

 

2. Create your new trigger, for parsing the csv data into lines.

 

3.  Add a field - paste your data into the right hand window.

 

4. Enable "is repeating block" and set the look for to \r\n[^\r\n]+ (a line feed followed by as many characters as possible that aren't line feeds).

 

5. On the "extract data" tab, enable "use the find mask".

 

6.  Enable the extract script, and select edit.  The paste in the following code to the window.  This code removes " and uses a replacement method to escape commas (which we will undo in the next trigger).  This also replaces empty fields with a better value for extraction at the next stage.


Sub Main()

    Dim vbQuote As String
    Dim arr As Variant
    Dim mod_ans As Integer
    vbQuote = Chr(34)

    If InStr(1, Email2DBExtract, vbQuote) > 0 Then
        arr = Split(Email2DBExtract, vbQuote)

        For idx = LBound(arr) To UBound(arr)
            If idx Mod 2 = 1 Then
               arr(idx) = Replace(arr(idx), ",", "[COMMA]")
            End If
        Next idx

        Email2DBExtract = Join(arr, "")
    End If

    If Left(Email2DBExtract, 1) = "," Then Email2DBExtract = " " & Email2DBExtract
    Email2DBExtract = Replace(Email2DBExtract, ",,", ", ,")

End Sub

 

7. Go back to the "general" tab, and check then uncheck "is repeating block" to get Email2DB to reprocess the regular expression.

 

8. OK the field and OK the trigger.

 

9. Add a new trigger for the line.

 

10. Add a new field to the trigger, and paste one of the lines from the csv to the right hand window.

 

11. Enter the name of the field, then in look for enter *, and on "extract data" "until these characters" enter ,

 

12. Repeat for each field, except for these fields, it should be look for , extract until ,  and for the last field it should be extract until end of line.

 

13. In fields where commas could be part of the data (usually free text style fields) go to the extract data tab of the field, and enter the code:


Sub Main()    Email2DBExtract = Replace(Email2DBExtract, "[COMMA]", ",")End Sub


This undoes what we did in step 6.

 

14. Ok this trigger, and open the multiple lines trigger.  Go into the line extraction field, then to the "pass to" tab.  Select the Line trigger from the drop down.  Other settings should be fine on defaults.

 

15.  Run a test, and make sure your fields are extracted correctly.

 

16.  Set up the line trigger to update your database or whatever actions you want to do with the data.

 
 
Here is the accounts to import into Email2DB 2: uploads/1089/CSVParse.vdb

 


Edited by Daniel - 23 Jul 2008 at 11:46am
Daniel Tallentire
Support
Parker Software
Back to Top
vinchu View Drop Down
New User
New User


Joined: 16 Mar 2006
Posts: 5
Post Options Post Options   Quote vinchu Quote  Post ReplyReply Direct Link To This Post Posted: 30 Sep 2008 at 5:31pm

Hi Daniel,

Thanks for that post, I think that give me some hope to solve my issue.
 
The issue I am having is when an email address contains ' (quotes), when it passes to SQL server by email2db, it just trucate the rest of the sql statment.
 
How to work with the system built-in field %msg_from%?
 
Any code example will be appeciated.
 
Thanks,
Back to Top
vinchu View Drop Down
New User
New User


Joined: 16 Mar 2006
Posts: 5
Post Options Post Options   Quote vinchu Quote  Post ReplyReply Direct Link To This Post Posted: 30 Sep 2008 at 5:52pm
that's my plug - not sure how it can with the system built-in field %msg_from% ??

Sub Main()
    ' commands start here
    Dim vbDash As String
    Dim Email2DBExtract2 As String
    vbDash = Chr(39)
    Email2DBExtract2 = "abc'abc"
    If InStr(Email2DBExtract2, vbDash) >0 Then
    Email2DBExtract2 = Replace(Email2DBExtract2, "'", "''")
    Debug.Print Email2DBExtract2
  End If
End Sub
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 879
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 20 Oct 2008 at 11:02am
Hi,

To do it with a system field use the field MSG_FROM in the script. This can be edited in the same was as any script value.
Daniel Tallentire
Support
Parker Software
Back to Top
mitesh281 View Drop Down
New User
New User
Avatar

Joined: 24 Feb 2009
Posts: 36
Post Options Post Options   Quote mitesh281 Quote  Post ReplyReply Direct Link To This Post Posted: 03 Mar 2009 at 1:14pm
Hi Daniel,
 
I am trying to use repeating block feature for below formate of email.
 
Client Name : Test
Client Type : Test1
Layer Name : Test
 Service : NONE
 Test : Yes
 Description : Test1
 Last Measure : 1.0 Mins
 Service : NONE
 Test : No
 Description : Test2
 Last Measure : 4.0 Mins
Priority : Critical
Start Time : Feb 01, 2009 01:31:56
 
Can you please guide me on this?
 
Thanks.
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 879
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 06 Mar 2009 at 12:46pm
Hi Mitesh,

You should use a regular expression to grab the block (this sort of block are the least easy to do).

Look for should be
Service : [A-Za-z]*[\r\n]+[A-Za-z0-9 ]*[\r\n]+[A-Za-z0-9 ]*[\r\n]+[A-Za-z0-9. ]*


Then the 2nd tab should have "use the find mask" checked.
Daniel Tallentire
Support
Parker Software
Back to Top
Webtrix View Drop Down
New User
New User


Joined: 10 Aug 2010
Posts: 3
Post Options Post Options   Quote Webtrix Quote  Post ReplyReply Direct Link To This Post Posted: 11 Aug 2010 at 12:43am
I am having some trouble doing this. I followed this step by step, but it does not work for me. When i use the "Run Test" feature everything works smoothly however when i email it from an outside source, the data from my csv file is not getting passed from the email to the triggers. any help would be greatly appreciated.
Back to Top
Daniel Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Posts: 310
Post Options Post Options   Quote Daniel Horton Quote  Post ReplyReply Direct Link To This Post Posted: 12 Aug 2010 at 10:54am
Hi Brian,

Thanks for contacting support@parker-software.com. We shall resume communication via email for the time being and if I find your problem I will update both this and contact you via email.
Best Regards,

Daniel James Horton
Technical Manager
Parker Software
Back to Top
Daniel Horton View Drop Down
Admin Group
Admin Group
Avatar
Technical Support

Joined: 01 Jun 2009
Location: Stoke-on-Trent
Posts: 310
Post Options Post Options   Quote Daniel Horton Quote  Post ReplyReply Direct Link To This Post Posted: 23 Jun 2011 at 5:36pm
If you are expecting multiple blank items within a row of data then please use the following script in your CSVLine trigger:


Sub Main()

    Dim vbQuote As String
    Dim arr As Variant
    Dim mod_ans As Integer
    vbQuote = Chr(34)

    If InStr(1, Email2DBExtract, vbQuote) > 0 Then
        arr = Split(Email2DBExtract, vbQuote)

        For idx = LBound(arr) To UBound(arr)
            If idx Mod 2 = 1 Then
               arr(idx) = Replace(arr(idx), ",", "[COMMA]")
            End If
        Next idx

        Email2DBExtract = Join(arr, "")
    End If

    If Left(Email2DBExtract, 1) = "," Then Email2DBExtract = " " & Email2DBExtract
    Do While InStr(1, Email2DBExtract, ",,") > 0
        Email2DBExtract = Replace(Email2DBExtract, ",,", ", ,")
    Loop
End Sub

Best Regards,

Daniel James Horton
Technical Manager
Parker Software
Back to Top
Daniel View Drop Down
Admin Group
Admin Group
Avatar
Technical Director

Joined: 19 Dec 2006
Location: Stoke-on-Trent
Posts: 879
Post Options Post Options   Quote Daniel Quote  Post ReplyReply Direct Link To This Post Posted: 17 Aug 2011 at 10:47am
If you want to extract a maximum of X lines from the script, first setup an empty field extraction above the CSV Line extraction, and name it LineCount.
 
Now, modify the CSV extraction to:
 
Sub Main()

Dim vbQuote As String
Dim arr As Variant
Dim mod_ans As Integer
vbQuote = Chr(34)
 
    If Val(Fields_value("LineCount")) > 18 Then
       Email2DBTrigger = False
    End If

 
If InStr(1, Email2DBExtract, vbQuote) > 0 Then
arr = Split(Email2DBExtract, vbQuote)

For idx = LBound(arr) To UBound(arr)
If idx Mod 2 = 1 Then
arr(idx) = Replace(arr(idx), ",", "[COMMA]")
End If
Next idx

Email2DBExtract = Join(arr, "")
End If

If Left(Email2DBExtract, 1) = "," Then Email2DBExtract = " " & Email2DBExtract
Do While InStr(1, Email2DBExtract, ",,") > 0
Email2DBExtract = Replace(Email2DBExtract, ",,", ", ,")
Loop
 
Fields_Value("LineCount") = Cstr(Val(Fields_value("LineCount")) + 1)
End Sub
 
 
 
This will cancel the trigger once the execution reaches the linecount, so no more messages would be passed through.


Edited by Daniel - 17 Aug 2011 at 5:19pm
Daniel Tallentire
Support
Parker Software
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down



This page was generated in 0.109 seconds.
These are the forums for Parker Software, developers of Live Chat Software: WhosOn and Email Automation Software: Email2DB.