Converting log file to CSV

Question

I have to convert a (Squid Web Proxy Server) log file to CSV file, so that it can be loaded into powerpivot for analysis of queries. So how should I start, any help would strongly be appreciated. I've to use C# language for this task, log looks like the following:

Format: Timestamp Elapsed Client Action/Code Size Method URI Ident Hierarchy/From Content

1473546438.145    917 5.45.107.68 TCP_DENIED/403 4114 GET http://atlantis.pennergame.de/pet/ - NONE/- text/html
1473546439.111      3 146.148.96.13 TCP_DENIED/403 4604 POST http://mobiuas.ebay.com/services/mobile/v1/UserAuthenticationService - NONE/- text/html
1473546439.865    358 212.83.168.7 TCP_DENIED/403 3955 GET http://www.theshadehouse.com/left-sidebar-post/ - NONE/- text/html
1473546439.985    218 185.5.97.68 TCP_DENIED/403 3600 GET http://www.google.pl/search? - NONE/- text/html
1473546440.341      2 146.148.96.13 TCP_DENIED/403 4604 POST http://mobiuas.ebay.com/services/mobile/v1/UserAuthenticationService - NONE/- text/html
1473546440.840    403 115.29.46.240 TCP_DENIED/403 4430 POST http://et.airchina.com.cn/fhx/consumeRecord/getCardConsumeRecordList.htm - NONE/- text/html
1473546441.486      2 52.41.27.39 TCP_DENIED/403 3813 POST http://www.deezer.com/ajax/action.php - NONE/- text/html
1473546441.596      2 146.148.96.13 TCP_DENIED/403 4604 POST http://mobiuas.ebay.com/services/mobile/v1/UserAuthenticationService - NONE/- text/html

Answer 1

A CSV is a delimited file whose field delimiter is ,. Almost all programs allow you to specify different field and record delimiters, using , and \n as defaults.

Your file could be treated as delimited if it didn't contain multiple spaces for indentation. You can replace multiple spaces with a single one using the regex \s{2,}, eg:

var regex=new Regex(@"\s{2,}");
var original=File.ReadAllText(somePath);
var delimited=regex.Replace(original," ");
File.WriteAllText(somePath,delimited);

Power BI Desktop already allows you to use space as a delimiter. Even if it didn't, you could just replace all spaces with a comma by changing the pattern to \s+, ie:

var regex=new Regex(@"\s+");
...
var delimited=regex.Replace(original,",");
...

Log files are large, so it's a very good idea to reduce the amount of memory they use. You can avoid reading the entire file in memory if you use ReadLines to read one line at a time, make the replacement and write it out:

using(var writer=File.CreateText(targetPath))
{
    foreach(var line in File.ReadLines(somePath))
    {
        var newline=regex.Replace(line," ");
        writer.WriteLine(newline);
    }
}

Unlike ReadAllLines which loads all lines in an array, ReadLines is an iterator that reads and returns one line at a time.

Answer 2

public static class SquidWebProxyServerCommaSeparatedWriter
{
    public static void WriteToCSV(string destination, IEnumerable<SquidWebProxyServerLogEntry> serverLogEntries)
    {
        var lines = serverLogEntries.Select(ConvertToLine);

        File.WriteAllLines(destination, lines);
    }

    private static string ConvertToLine(SquidWebProxyServerLogEntry serverLogEntry)
    {
        return string.Join(@",", serverLogEntry.Timestamp, serverLogEntry.Elapsed.ToString(),
            serverLogEntry.ClientIPAddress, serverLogEntry.ActionCode, serverLogEntry.Size.ToString(),
            serverLogEntry.Method.ToString(), serverLogEntry.Uri, serverLogEntry.Identity,
            serverLogEntry.HierarchyFrom, serverLogEntry.MimeType);
    }
}    

public static class SquidWebProxyServerLogParser
{
    public static IEnumerable<SquidWebProxyServerLogEntry> Parse(FileInfo fileInfo)
    {
        using (var streamReader = fileInfo.OpenText())
        {
            string row;

            while ((row = streamReader.ReadLine()) != null)
            {
                yield return ParseRow(row)
            }
        }
    }

    private static SquidWebProxyServerLogEntry ParseRow(string row)
    {
        var fields = row.Split(new[] {"\t", " "}, StringSplitOptions.None);

        return new SquidWebProxyServerLogEntry
        {
            Timestamp = fields[0],
            Elapsed = int.Parse(fields[1]),
            ClientIPAddress = fields[2],
            ActionCode = fields[3],
            Size = int.Parse(fields[4]),
            Method =
                (SquidWebProxyServerLogEntry.MethodType)
                Enum.Parse(typeof(SquidWebProxyServerLogEntry.MethodType), fields[5]),
            Uri = fields[6],
            Identity = fields[7],
            HierarchyFrom = fields[8],
            MimeType = fields[9]
        };
    }

    public static IEnumerable<SquidWebProxyServerLogEntry> Parse(IEnumerable<string> rows) => rows.Select(ParseRow);
}

public sealed class SquidWebProxyServerLogEntry
{
    public enum MethodType
    {
        Get = 0,
        Post = 1,
        Put = 2
    }

    public string Timestamp { get; set; }
    public int Elapsed { get; set; }
    public string ClientIPAddress { get; set; }
    public string ActionCode { get; set; }
    public int Size { get; set; }
    public MethodType Method { get; set; }
    public string Uri { get; set; }
    public string Identity { get; set; }
    public string HierarchyFrom { get; set; }
    public string MimeType { get; set; }
}

Answer 3

It is already close to a CSV, so read it line by line and clean each line up a little:

...
line = line
  .Replace("   ", " ")  // compress 3 spaces to 1
  .Replace("  ", " ")   // compress 2 spaces to 1
  .Replace("  ", " ")   // compress 2 spaces to 1, again
  .Replace(" ", "|")    // replace space by '|'
  .Replace(" - ", "|"); // replace  -  by '|'

You may want to tweak this for the fields like TCP_DENIED/403 .

this gives you a '|' separated line. Easy to convert to any separator you need. Or split it up:

// write it out or process it further    
string[] parts = line.split('|');