CODEWITHSUNDEEP
c#.netcsv
M. X
M. X

Reputation: 1347

Convert a single datarow in a CSV like string in C#

How can I convert a single datarow (of a datatable) into a CSV like string with only few C# commands. In other words into a string like "value_1;value_2;...;value_n"

Upvotes: 2

Views: 5484

Answers (2)

Adriano Repetti
Adriano Repetti

Reputation: 67090

Quick and dirty for a single DataRow:

System.Data.DataRow row = ...;

string csvLine = String.Join(
    CultureInfo.CurrentCulture.TextInfo.ListSeparator,
    row.ItemArray);

If you do not care about the culture specific separator you may do this to convert a full DataTable:

public static string ToCsv(System.Data.DataTable table)
{
    StringBuilder csv = new StringBuilder();

    foreach (DataRow row in table.Rows)
        csv.AppendLine(String.Join(";", row.ItemArray));

    return csv.ToString();
}

Here a more complex example if you need to handle a little bit of formatting for the values (in case they're not just numbers).

public static string ToCsv(DataTable table)
{
    StringBuilder csv = new StringBuilder();
    
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < row.ItemArray.Length; ++i)
        {
            if (i > 0)
                csv.Append(CultureInfo.CurrentCulture.TextInfo.ListSeparator);
    
            csv.Append(FormatValue(row.ItemArray[i]));
        }
    
        csv.AppendLine();
    }
    
    return csv.ToString();
}

Or, if you prefer LINQ (and assuming table is not empty):

public static string ToCsv(DataTable table, string separator = null)
{
    if (separator == null)
        separator = CultureInfo.CurrentCulture.TextInfo.ListSeparator;

    return table.Rows
        .Cast<DataRow>()
        .Select(r => String.Join(separator, r.ItemArray.Select(c => FormatValue(c)))
        .Aggregate(new StringBuilder(), (result, line) => result.AppendLine(line))
        .ToString();
}

Using this private function to format a value. It's a very naive implementation, for non primitive types you should use TypeConverter (if any, see this nice library: Universal Type Converter) and quote the text only if needed (2.6):

private static string FormatValue(object value)
{
    if (Object.ReferenceEquals(value, null))
        return "";

    Type valueType = value.GetType();

    if (valueType.IsPrimitive || valueType == typeof(DateTime))
        return value.ToString();

    return String.Format("\"{0}\"",
        value.ToString().Replace("\"", "\"\"");
}

Notes
Even if there is a RFC for CSV many applications does not follow its rules and they handle special cases in their very own way (Microsoft Excel, for example, uses the locale list separator instead of comma and it doesn't handle newlines in strings as required by the standard).

Upvotes: 9

Guffa
Guffa

Reputation: 700382

Here is a start:

StringBuilder line = new StringBuilder();
bool first = true;
foreach (object o in theDataRow.ItemArray) {
  string s = o.Tostring();
  if (s.Contains("\"") || s.Contains(",")) {
    s = "\"" + s.Replace("\"", "\"\"") + "\"";
  }
  if (first) {
    first = false;
  } else {
    line.Adppend(',');
  }
  line.Append(s);
}
String csv = line.ToString();

It will handle quoted values and values containing the separator, i.e. a value that contains quotation marks or the separator needs to be surrounded by quotation marks, and the quotation marks inside it needs to be escaped by doubling them.

Note that the code uses comma as separator, as that's what the C in CSV stands for. Some programs might be more comfortable using semicolon.

Room for imrovement: There are also other characters that should trigger quoting, like padding spaces and line breaks.

Note: Even if there is a standard defined for CSV files now, it's rarely followed because many programs were developed long before the standard existed. You just have to adapt to the peculiarities of any program that you need to communicate with.

Upvotes: 5

Related Questions