How to capture data from an attached CSV file and save it to a database


See also: Parsing text from attached files Example. Parsing an attached PDF file

  In this example we will show how to save to a MySQL database the cells from a CSV file that looks like this:  

    Attached CSV files, like all the supported attached file types, can be read with a field of type Attachment reader. Once set up it looks as follows:  

  With this capture method we are just placing the CSV file content as plain text in a field we have named csv_content. The next step is to reconstruct the table structure based on this field. We start getting the rows:

  Note that we have chosen csv_content as input and we have activated “The captured text appears multiple times”. This means that we expect the conditions at the top to be met multiple times in the input text, resulting in multiple results. In this case, the condition we have used is, in plain English, “take any piece of text that starts with a number and ends in a line break”. The result is that we retrieve all the rows separately as shown.   Now that we have all the rows, we have to separate them into columns. This is done as follows:  


  We have used this regular expression in column1, column2 and column3:
  You can take a look at how regular expressions work in EmailParser but the most important detail in this example is that we have used named groups to identify each column. Note that you can also use any other type of capture method for separating the columns. For instance:

    Now, if we process an email we will see results like this:  

  That is the kind of parsing results we are looking for, each column has 5 values as it is expected based on the received CSV file we are using for testing. Now, the next step is to insert this data into the database. To do that we use two actions: the MySQL action and the Run for each field group action . The combination of the two looks like this:  


    We process again the test email and check the output to confirm that all CSV cells are inserted in the database: