Sitecore Forms - Export to Semicolon File
The new dashboard for Sitecore Forms has a button that allows a user to export their form data to a CSV file. When clicking on that button, you can choose to export all data or a specific date range. Interestingly enough, when that CSV gets generated, it shows up with semi-colons as the delimiter. Under normal circumstances, this wouldn't be a big deal, except that the export provider forgot to tell the file that it uses a semi-colon as the delimiter. This results in the file not gracefully opening in Excel. This popped up in a question on Slack today, and I remembered that we applied a "fix" for this in a project. A colleague of mine, Keith Deshane, solved this problem by overwriting the controller used to generate the export. In going through his change, I did a deeper dive into the implementation, and was able to achieve a similar resolution with a little less code.
Out of the box, Sitecore leverages the Sitecore.ExperienceForms.Client.Data.CsvExportProvider class to generate the file content when the user chooses to export the form data. DotPeek (a Sitecore dev's best friend) told me that the semi-colon as the delimiter is hard-coded into the implementation:
This means that we can either write our own export provider, or figure out a way to tell the exported file that it is full of semi-colons. Luckily, the implementation of the provider uses virtual methods!! :D
Let's make a new class that extends the existing provider:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
public class SemiColonExportProvider : CsvExportProvider
{
public SemiColonExportProvider(IFormDataProvider formDataProvider) : ``base``(formDataProvider) { }
protected override string GenerateFileContent(IEnumerable<FormEntry> formEntries)
{
// this will tell the file that it is using semi-colons
const string separator = ``"\"sep=;\"\r\n"``; /* see foot note */
var content = ``base``.GenerateFileContent(formEntries);
var stringBuilder = ``new StringBuilder();
stringBuilder.AppendLine(separator);
stringBuilder.AppendLine(content);
return stringBuilder.ToString();
}
}
This class is pretty straight forward. We extend the existing provider, and are going to use most of the logic. The first line of the GenerateFileContent method will tell the file that the separator is a semi-colon. We can leverage the default logic for loading up the content, and then just make sure the separator is added to the beginning of the file.
All that is left is to swap out the original provider with the new provider:
1
2
3
4
5
6
7
<``configuration xmlns:patch``=``"[http://www.sitecore.net/xmlconfig/](http://www.sitecore.net/xmlconfig/)"``>
<``sitecore``>
<``services``>
<``register serviceType``=``"Sitecore.ExperienceForms.Data.IExportDataProvider, Sitecore.ExperienceForms" implementationType``=``"Feature.Forms.Providers.SemiColonExportProvider, Feature.Forms" patch:instead``=``"*[@implementationType='Sitecore.ExperienceForms.Client.Data.CsvExportProvider, Sitecore.ExperienceForms.Client']"``/>
</``services``>
</``sitecore``>
</``configuration``>
Easy as that, the new export logic will now generate a file that can be interpreted by Excel:
FOOTNOTE: @mike_i_reynolds says not to hard code string literals. Check out this post to learn how you can configure and inject the separator string.
I hope this helps alleviate some headache when it comes to figuring out why your exported form data isn't opening gracefully.