Pedro Liska's Blog

LINQ query to CSV

February 9, 2012 at 11:45 am (Computer Science)

The C# code below generates a CSV (comma separated value) string with the header row and data obtained from a LINQ query.

For example: A query like this:

dc.Users.Select(u => new
{
    user_id = u.UserId,
    username= u.Username,
    first_name = u.FirstName,
    last_name = u.LastName,
    creation_date = u.CreationDate
});

Would result in a CSV string like this one:

user_id,username,first_name,last_name,creation_date
"abf577a5-dc76-4719-9375-001a445d0c3d","jdoe","John","Doe",2011-03-29T14:50:28
"24b888cf-b02b-413e-8542-007e7ee572a6","jdoe2","John","Doe2",2009-05-29T19:52:32

And now to the code:

var users = dc.Users.Select(u => new
{
    user_id = u.UserId,
    username = u.Username,
    first_name = u.FirstName,
    last_name = u.LastName,
    creation_date = u.CreationDate
}).ToArray();

var usersCsv = GenerateCsv(users);

And make sure the following methods are delcared:

/// <summary>
/// This method generates CSV data out an array of anything. The array
/// elements could be of anonymous type
/// </summary>
private string GenerateCsv(Array items)
{

    var rowType = items.GetType().GetElementType();
    var colNames = rowType.GetProperties().Select(p => p.Name).ToArray();

    // create the header row
    var retVal = new StringBuilder();
    retVal.AppendLine(string.Join(",", colNames));

    // now create the body
    foreach (var row in items)
    {
        var rowItems = new string[colNames.Length];
        for (int i = 0; i < colNames.Length; i++)
            rowItems[i] = this.FormatCell(rowType.GetProperty(colNames[i]).GetValue(row, null));

        retVal.AppendLine(string.Join(",", rowItems));
    }

    return retVal.ToString();
}

private string FormatCell(object item)
{
    if (item == null)
        return "";

    if (item.GetType() == typeof(DateTime))
        return ((DateTime)item).ToUniversalTime().ToString("s");

    return "\"" + item.ToString().Replace("\"", "\"\"") + "\"";
}

Permalink Leave a Comment