EXPORTING WFFM REPORTS ON A SCHEDULE

While Sitecore provides an easy way to export contact reports from WFFM forms through the dashboard, sometimes we need to export this data programmatically for marketing consumption, etc.

In this mini tutorial we are going to learn to create a Sitecore Scheduled task that will export our form data to a CSV file in our webroot.

First, we need to create a Sitecore command. This can be done by creating a new “Command” item in the following path:
/sitecore/system/Tasks/Commands. Here, we will specify the method that will be executed by our schedule.

Next, we need to create a “schedule” item that will call our command on a given interval under: /sitecore/system/Tasks/Schedules Here, we will specify the command we want to run and the interval for our schedule. The interval format can get a little tricky. For instance, the following schedule string will run every 24hrs: 19700101|99991231|127|24:00:00

Finally, it’s time to write some code. We’ll go ahead and create the class we specified in our Sitecore command with the following code (yeah, it’s a little long 🙁 ). Note: make sure to update the export path and WFFM item accordingly:

public void Execute(Item[] items, Sitecore.Tasks.CommandItem command, Sitecore.Tasks.ScheduleItem schedule)
{
    Sitecore.Diagnostics.Log.Info("Generating WFFM CSV file.", this);

    string formID = "{FORM GUID}";
    string exportFolder = "{FORM OUTPUT PATH};
    string exportFilename = "{FORM FILENAME}";
    
    //Build complete export path
    string filePath = MainUtil.MapPath(string.Concat("/", exportFolder));

    //Check if folder already exists. Otherwise, create it.
    bool exists = Directory.Exists(filePath);
    if (!exists)
        Directory.CreateDirectory(filePath);

    //Append filename
    filePath = String.Format(filePath + "\\{0}", exportFilename);

    CreateCsvFile(ExtractWFFMData(new Guid(formID)), filePath);
}


public FormExportArgs ExtractWFFMData(Guid id)
{
    try
    {
        Item fromMasterDB = this.itemRepository.GetItemFromMasterDatabase(new ID(id));
        Assert.IsTrue(fromMasterDB != null, "Can't find the form.");
        Job job = Context.Job;
        if (job != null)
            job.Status.LogInfo(DependenciesManager.ResourceManager.Localize("READING_DATA_FROM_DATABASE"));
        IEnumerable<FormData> formData = this.FormsDataProvider.GetFormData(id);
        FormExportArgs formExportArgs = new FormExportArgs(this.itemRepository.CreateFormItem(fromMasterDB), new FormPacket(formData), this.webUtil.GetTempFileName(), "text/xml");
        formExportArgs.Parameters.Add("contextUser", Context.User.Name);

        return formExportArgs;
    }
    catch (Exception e)
    {
        Sitecore.Diagnostics.Log.Error("Error extracting data from WFFM form. ", e, this);
        throw;
    }
}

public void CreateCsvFile(FormExportArgs args, string filename)
{
    try
    {
        Job job = Context.Job;
        if (job != null)
            job.Status.LogInfo(DependenciesManager.ResourceManager.Localize("EXPORTING_WFFM_DATA_TO_CSV"));

        Sitecore.Diagnostics.Log.Info(String.Format("Number of entries in form: {0}", args.Packet.Entries.Count.ToString()), this);

        using (new SecurityDisabler())
        {
            //Write CSV File - Using CSVHelper 2.16.3
            using (var sw = new StreamWriter(filename))
            using (var writer = new CsvWriter(sw))
            {
                //Write Header
                writer.WriteField("Created");
                foreach (IFieldItem field in args.Item.Fields)
                {
                    writer.WriteField(field.FieldDisplayName);
                }
                writer.NextRecord();

                //Write Form Entries
                foreach (FormData entry in args.Packet.Entries)
                {
                    writer.WriteField(entry.Timestamp.ToLocalTime().ToString("G"));
                    foreach (IFieldItem field in args.Item.Fields)
                    {
                        FieldData fieldData =
                            entry.Fields.FirstOrDefault<FieldData>((Func<FieldData, bool>)(f => f.FieldId == field.ID.Guid));
                        writer.WriteField(fieldData != null ? fieldData.Value : " ");
                    }
                    writer.NextRecord();
                }
            }
        }
    }
    catch (Exception e)
    {
        Sitecore.Diagnostics.Log.Error("Error creating CSV file. ", e, this);
    }
}

And, that’s it! This task will generate a CSV file with our form report on an interval that we specified in our schedule Sitecore item.

While this tutorial focuses in exporting WFFM data, it could easily be adapted to export data from Sitecore 9 forms by updating our ExtractWFFMData method.

This feature can probe specially useful for marketing purposes where data has to be accessed rapidly and sometimes be consumed by other marketing tools.

Leave a Reply

Your email address will not be published. Required fields are marked *