Writing to CSV using log4net

Introduction

This is article 1 in a 2-part series discussing how to write to CSV files using log4net.

Anyone who has used log4net knows how versatile a logging library it is. Routing log messages to a text file, console, database, ASP.NET trace, and/or a multitude of other destinations is a simple matter of configuration. With the help of pattern tokens to represent system-defined properties, full customization of the message output is also easily handled via configuration. (For the purposes of this post, I will assume the reader is already familiar with log4net.)

However, one type of log output is notably missing: CSV. Although a comma-separated value file is simply a text file (which is supported by FileAppender), and while it is possible to comma-delimit your field values (using PatternLayout), there are no quoting or escaping rules, which are important in a CSV file. Consider the following example:

Using this configuration:
<appender name="CsvFileAppender" type="log4net.Appender.FileAppender">
  <file value="log.csv"/>
  <appendToFile value="true"/>
  <layout type="log4net.Layout.PatternLayout">
    <header value="DateTime,Level,Message&#13;&#10;" />
    <conversionPattern
      value="%date{M/d/yyyy H:mm:ss.fff},%level,%message%newline" />
  </layout>
</appender>
and executing this code:
private static void Main()
{
  XmlConfigurator.Configure();
  ILog logger = LogManager.GetLogger("");
  logger.Info("Here is a sample message");
  logger.Info("To include a comma, wrap in quotes");
  logger.Warn("Literal \"quotes\" must be escaped");
}
we get this output:
DateTime,Level,Message
5/14/2010 10:57:14.115,INFO,Here is a sample message
5/14/2010 10:57:14.115,INFO,To include a comma, wrap in quotes
5/14/2010 10:57:14.115,WARN,Literal "quotes" must be escaped
As you can see, the presence of unescaped commas and quotes throws off the CSV format. There are 3 things we need to take care of:

  • Automatically start each field with a quote.
  • Automatically end each field with a quote.
  • Automatically double all literal quotes within the field.
All of this can be accomplished by implementing a custom PatternLayout class and two custom PatternConverter classes. Let's take a look.

Implementing a Custom Pattern Layout

The key overridable method we're interested in when subclassing PatternLayout is the Format method. It takes a TextWriter instance (which is used to write the actual stream of text to the output) and a LoggingEvent instance (which contains information about the message being logged). We can override this method and, using the decorator pattern, surreptitiously wrap the TextWriter instance in our own CsvTextWriter implementation, which we can use to automatically double all quotes being written to the output:
public class CsvPatternLayout : PatternLayout
{
  public override void Format(TextWriter writer, LoggingEvent loggingEvent)
  {
    CsvTextWriter ctw = new CsvTextWriter(writer);
    base.Format(ctw, loggingEvent);
  }
}

public class CsvTextWriter : TextWriter
{
  ...
  public override void Write(char value)
  {
    _textWriter.Write(value);
    // double all quotes
    if (value == '"')
      _textWriter.Write(value);
  }
}
This is fine for doubling the quotes within each field, but how do we begin and end each field with a quote? This part is a little more involved.

Implementing Custom Pattern Tokens

First, it's important to note that the CsvPatternLayout.Format method is only called once for each message/row. So the primary challenge is to find an unambiguous way to recognize the separation between fields within the layout pattern and to recognize the end of the row.

To do this, we will define two custom pattern tokens: %newfield and %endrow. By using these tokens in the layout pattern, it becomes possible to specify the separation between fields and rows, which will allow us to programmatically inject quotes at the beginning and end of each automatically.

Our layout pattern in the configuration becomes:
%date{M/d/yyyy H:mm:ss.fff}%newfield%level%newfield%message%endrow
All that's left is to implement the code for these pattern tokens. We do this by subclassing PatternConverter once for each token, and registering these subclass types in our CsvPatternLayout class.

The Final Code

using System.IO;
using System.Text;
using log4net.Core;
using log4net.Layout;
using log4net.Util;

namespace CsvLogging
{
  public class CsvPatternLayout : PatternLayout
  {
    public override void ActivateOptions()
    {
      // register custom pattern tokens
      AddConverter("newfield", typeof(NewFieldConverter));
      AddConverter("endrow", typeof(EndRowConverter));
      base.ActivateOptions();
    }

    public override void Format(TextWriter writer, LoggingEvent loggingEvent)
    {
      CsvTextWriter ctw = new CsvTextWriter(writer);
      // write the starting quote for the first field
      ctw.WriteQuote();
      base.Format(ctw, loggingEvent);
    }
  }

  public class NewFieldConverter : PatternConverter
  {
    protected override void Convert(TextWriter writer, object state)
    {
      CsvTextWriter ctw = writer as CsvTextWriter;
      // write the ending quote for the previous field
      if (ctw != null)
        ctw.WriteQuote();
      writer.Write(',');
      // write the starting quote for the next field
      if (ctw != null)
        ctw.WriteQuote();
    }
  }

  public class EndRowConverter : PatternConverter
  {
    protected override void Convert(TextWriter writer, object state)
    {
      CsvTextWriter ctw = writer as CsvTextWriter;
      // write the ending quote for the last field
      if (ctw != null)
        ctw.WriteQuote();
      writer.WriteLine();
    }
  }

  public class CsvTextWriter : TextWriter
  {
    private readonly TextWriter _textWriter;

    public CsvTextWriter(TextWriter textWriter)
    {
      _textWriter = textWriter;
    }

    public override Encoding Encoding
    {
      get { return _textWriter.Encoding; }
    }

    public override void Write(char value)
    {
      _textWriter.Write(value);
      // double all quotes
      if (value == '"')
        _textWriter.Write(value);
    }

    public void WriteQuote()
    {
      // write a literal (unescaped) quote
      _textWriter.Write('"');
    }
  }
}
The full log4net configuration is:
<log4net>
  <appender name="CsvFileAppender" type="log4net.Appender.FileAppender">
    <file value="log.csv"/>
    <appendToFile value="true"/>
    <layout type="CsvLogging.CsvPatternLayout, CsvLogging">
      <header value="DateTime,Level,Message&#13;&#10;" />
      <conversionPattern value=
"%date{M/d/yyyy H:mm:ss.fff}%newfield%level%newfield%message%endrow" />
    </layout>
  </appender>
  <root>
    <level value="DEBUG" />
    <appender-ref ref="CsvFileAppender" />
  </root>
</log4net>
and the sample output now becomes:
DateTime,Level,Message
"5/14/2010 10:57:14.115","INFO","Here is a sample message"
"5/14/2010 10:57:14.115","INFO","To include a comma, wrap in quotes"
"5/14/2010 10:57:14.115","WARN","Literal ""quotes"" must be escaped"
which is valid CSV!

Conclusion

In this article we implemented a custom pattern layout and custom pattern tokens in log4net in order to enable properly quoted and escaped CSV output. In the next installment, I will demonstrate a way to bind individual message properties to specific fields in the CSV file.

5 Response to "Writing to CSV using log4net"

  1. Pavel November 15, 2010 at 5:04 AM
    thanks for the post!
    I've a question to the tag. if it's possible to print it just once in a file, but not each time when the logging starts?
    Thank you!

    Best regards,
    Pavel
  2. Anonymous February 3, 2011 at 9:10 AM
    Nice, you should submit this to the core library!
  3. dbezz August 4, 2011 at 6:41 AM
    Thank you! Very useful! It should be in a core.
  4. Nitin Goyal October 30, 2014 at 1:22 AM
    Hi Thanks for this.

    Do you have idea if i want to generate the CSV with the huge data in dynamic form. Currently you are making the conv patt and header in log4net.config but I want to generate it dynamically.

    I have already implemented this thing for header but I am getting issues while implementing the dynamic conv pattern and the pushing the values as the values form the List is coming in a single cell and not in the different cells as needed. ANy advice will be very helpful

Post a Comment