CSV process - using repeating items

Message Share Topic Printable Version Google Delicious Digg StumbleUpon Windows Live Yahoo Bookmarks reddit Facebook MySpace Newsvine Furl Topic Search Topic Options Post Reply Create New Topic

   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