8
\$\begingroup\$

I wrote some extension methods to read CSV-styled text directly into a datatable or dataset or write them to this format. Is it right to use the methods as extensions or should I create a separate class that contains this functionality, and so on?

Formats explained:

The ReadFromCsv and WriteToCsv will work with a normal CSV style like:

Column1;Column2;Column3;...
Value11;Value12;Value13;...
Value21;Value22;Value23;...

The ReadFromSectionedCsv and WriteToSectionedCsv methods use a format like this:

[Table1]
Column1;Column2;Column3;...
Value11;Value12;Value13;...
Value21;Value22;Value23;...

[Table2]
Column1;Column2;Column3;...
Value11;Value12;Value13;...
Value21;Value22;Value23;...

where each table is read to a separate DataTable in the DataSet.

using System;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;

namespace CsvExtensions
{
    /// <summary>
    /// Erweiterungen für die Klassen System.Data.DataTable und System.Data.DataSet
    /// zum einlesen von an CSV angelehnten Daten direkt in eine Instanz dieser Typen
    /// </summary>
    public static class DataCsvExtension
    {
        //Trennzeichen der einzelnen Spalten
        private const char SEPERATOR = ';';

        /// <summary>
        /// Liest die Daten einer CSV Datei ein
        /// </summary>
        /// <param name="table">DataTable object</param>
        /// <param name="filepath">Pfad zur CSV Datei</param>
        public static void ReadFromCsv(this DataTable table, string filepath)
        {
            using (Stream filestream = File.Open(filepath, FileMode.Open))
            {
                table.ReadFromCsv(filestream);
            }
        }

        /// <summary>
        /// Liest die Daten einer CSV Datei ein
        /// </summary>
        /// <param name="table">DataTable object</param>
        /// <param name="filestream">Stream der CSV Datei</param>
        public static void ReadFromCsv(this DataTable table, Stream filestream)
        {
            table.Clear();

            Encoding encoding = Encoding.UTF8; //Encoding.Default;
            //if (Utf8Checker.IsUtf8(filestream))
            //    encoding = Encoding.UTF8;
            StreamReader sr = new StreamReader(filestream, encoding);
            string line = sr.ReadLine();

            //empty line is considered the end of the table
            if (String.IsNullOrEmpty(line))
                return;

            string[] array = line.Split(SEPERATOR);


            foreach (string value in array)
            {
                DataColumn dataColumn = new DataColumn(value.Trim())
                {
                    Caption = value.Trim()
                };
                table.Columns.Add(dataColumn);
            }
            table.NewRow();

            while (sr.Peek() > -1)
            {
                line = sr.ReadLine();

                if (line == null
                    || (line.Trim() == "" || !line.Contains(SEPERATOR)
                        || String.IsNullOrEmpty(line.Replace(';', ' ').Trim())))
                    continue;
                array = line.Split(SEPERATOR);

                int count = table.Columns.Count;
                if (array.Length < count)
                {
                    string[] newArray = new string[count];
                    for (int s = 0; s<array.Length;s++)
                    {
                        newArray[s] = array[s];
                    }
                    for( int s = array.Length; s<count;s++)
                    {
                        newArray[s] = "";
                    }
                    array = newArray;

                }
                if (array.Length > table.Columns.Count)
                {
                    //More Values than Columns found
                    throw new Exception(
                        String.Format(
                            "Fehlerhafte Zeile: Wertanzahl entspricht nicht der Anzahl der Spalten: {0}", line));
                }
                table.Rows.Add(array);
            }
        }

        /// <summary>
        /// Liest die Daten aus einer sektionierten CSV Datei in das DataSet
        /// Format der CSV-Datei:
        ///     [Tabellenname1]
        ///     Spalte1;Spalte2;Spalte3
        ///     Wert11;Wert12;Wert13
        ///     Wert21;Wert22;Wert23
        ///     ....
        ///     WertN1;WertN2;WertN3
        ///     [Tabellenname2]
        ///     Spalte1;Spalte2...
        ///     ...
        /// </summary>
        /// <param name="dataset">DataSet object</param>
        /// <param name="filepath">Pfad zur CSV Datei</param>
        public static void ReadFromSectionedCsv(this DataSet dataset, string filepath)
        {
            const string PATTERN = @"
^                           # Beginning of the line
((?:\[)                     # Section Start
(?:[ ]*)
(?<Section>[^\]^ ]*)         # Actual Section text into Section Group
(?:[ ]*)
(?:\])                    # Section End then EOL/EOB
(?:[ ;]*)
(?:[\r\n]{1,})
(?<Data>[^\[]*)
(?:[\r\n]{0,})
)";
            dataset.Clear();
            using (Stream filestream = File.Open(filepath, FileMode.Open))
            {
                Encoding encoding = Encoding.UTF8; //Encoding.Default;
                //if (Utf8Checker.IsUtf8(filestream))
                //    encoding = Encoding.UTF8;
                string fileContetnt;
                using (StreamReader sr = new StreamReader(filestream, encoding))
                {
                    fileContetnt = sr.ReadToEnd();
                }

                var match = Regex.Matches(fileContetnt, PATTERN,
                    RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);
                foreach (Match m in match)
                {
                    var sectionmatch = m.Groups["Section"];
                    var datamatch = m.Groups["Data"];
                    //Refactor: Is there a Better way to read the Tables of a Section?
                    using(MemoryStream stream = new MemoryStream()) 
                        using (StreamWriter writer = new StreamWriter(stream))
                        {
                            writer.Write(datamatch.Value);
                            writer.Flush();
                            stream.Position = 0;
                            dataset.Tables.Add(sectionmatch.Value).ReadFromCsv(stream);
                        }
                }
            }
        }

        /// <summary>
        /// Schreibt die Daten der DataTable als neue CSV Datei
        /// </summary>
        /// <param name="table">DataTable object</param>
        /// <param name="filepath">Pfad zur CSV Datei</param>
        public static void WriteToCsv(this DataTable table, string filepath)
        {
            using (Stream filestream = File.Open(filepath, FileMode.CreateNew))
            {
                table.WriteToCsv(filestream);
            }
        }

        /// <summary>
        /// Schreibt die Daten der DataTable im CSV Format in den angegebenen Stream
        /// </summary>
        /// <param name="table">DataTable object</param>
        /// <param name="filestream">Stream der CSV Datei</param>
        public static void WriteToCsv(this DataTable table, Stream filestream)
        {
            using (StreamWriter sw = new StreamWriter(filestream, Encoding.UTF8))
            {
                int numberOfColumns = table.Columns.Count;

                for (int i = 0; i < numberOfColumns; i++)
                {
                    sw.Write(table.Columns[i]);
                    if (i < numberOfColumns - 1)
                        sw.Write(SEPERATOR);
                }

                foreach (DataRow dr in table.Rows)
                {
                    sw.WriteLine();
                    for (int i = 0; i < numberOfColumns; i++)
                    {
                        sw.Write(dr[i].ToString());

                        if (i < numberOfColumns - 1)
                            sw.Write(SEPERATOR);
                    }
                }
                sw.Flush();
            }
        }

        /// <summary>
        /// Schreibt die Daten des DataSet als neue sektionierte CSV Datei
        /// Format der CSV-Datei:
        ///     [Tabellenname1]
        ///     Spalte1;Spalte2;Spalte3
        ///     Wert11;Wert12;Wert13
        ///     Wert21;Wert22;Wert23
        ///     ....
        ///     WertN1;WertN2;WertN3
        ///     [Tabellenname2]
        ///     Spalte1;Spalte2...
        ///     ...
        /// </summary>
        /// <param name="dataset">DataSet object</param>
        /// <param name="filepath">Pfad zur CSV Datei</param>
        public static void WriteToSectionedCsv(this DataSet dataset, string filepath)
        {
            using (Stream filestream = File.Open(filepath, FileMode.CreateNew))
            {
                using (StreamWriter sw = new StreamWriter(filestream, Encoding.UTF8))
                {
                    foreach (DataTable table in dataset.Tables)
                    {
                        sw.WriteLine("[{0}]", table.TableName);
                        int numberOfColumns = table.Columns.Count;

                        for (int i = 0; i < numberOfColumns; i++)
                        {
                            sw.Write(table.Columns[i]);
                            if (i < numberOfColumns - 1)
                                sw.Write(SEPERATOR);
                        }

                        foreach (DataRow dr in table.Rows)
                        {
                            sw.WriteLine();
                            for (int i = 0; i < numberOfColumns; i++)
                            {
                                sw.Write(dr[i].ToString());

                                if (i < numberOfColumns - 1)
                                    sw.Write(SEPERATOR);
                            }
                        }
                        sw.WriteLine();
                    }
                    sw.Flush();
                }
            }
        }
    }
}

Questions:

  • How would you handle malformed files?
  • Are all the Streams and usings necessary?
  • How can I organize the code with respect to easy unit testing?
  • What parts of the code would you parametrize? For instance, letting the user select the separating char.
\$\endgroup\$
  • 1
    \$\begingroup\$ Can you share an example of how you are using the extension methods? I assume something like var table = new DataTable(); table.ReadFromCsv("C:\some-file.csv"); \$\endgroup\$ – RobH Mar 4 '15 at 9:24
  • \$\begingroup\$ This is exactly how its used. Still don't know if it was a good idea cause you always have to create a DataTable or DataSet yourself and I still couldn't think of a situation you use an existing DataTable with it. Maybe the methods should create a DataTable themself and should not be extension mehtods \$\endgroup\$ – BoeseB Mar 4 '15 at 11:29
  • \$\begingroup\$ Hey - if you are reading CSV files you might want to check out: codeproject.com/Articles/9258/A-Fast-CSV-Reader It's a great project if you are reading in CSV data. \$\endgroup\$ – d347hm4n Mar 4 '15 at 11:30
  • \$\begingroup\$ Comments in German. Good thing nobody actually RTFMs. \$\endgroup\$ – FreeAsInBeer Mar 4 '15 at 13:55
8
\$\begingroup\$
if (array.Length < count)
{
    string[] newArray = new string[count];
    for (int s = 0; s<array.Length;s++)
    {
        newArray[s] = array[s];
    }
    for( int s = array.Length; s<count;s++)
    {
        newArray[s] = "";
    }
    array = newArray;

}

You can use Array.Resize to simplify this.

if (array.Length < count)
{
    var length = array.Length;
    Array.Resize(ref array, count);
    for (var i = length; i < array.Length; i++)
    {
        array[i] = string.Empty;
    }
}

if (line == null
    || (line.Trim() == "" || !line.Contains(SEPERATOR)
        || String.IsNullOrEmpty(line.Replace(';', ' ').Trim())))

I think you want to be using SEPERATOR instead of ; here.


sw.WriteLine("[{0}]", table.TableName);
int numberOfColumns = table.Columns.Count;

for (int i = 0; i < numberOfColumns; i++)
{
    sw.Write(table.Columns[i]);
    if (i < numberOfColumns - 1)
        sw.Write(SEPERATOR);
}

foreach (DataRow dr in table.Rows)
{
    sw.WriteLine();
    for (int i = 0; i < numberOfColumns; i++)
    {
        sw.Write(dr[i].ToString());

        if (i < numberOfColumns - 1)
            sw.Write(SEPERATOR);
    }
}
sw.WriteLine();

This can be simplified.

writer.WriteLine("[{0}]", table.TableName);
writer.WriteLine(string.Join(SEPERATOR.ToString(), table.Columns.Cast<DataColumn>()));
foreach (DataRow row in table.Rows)
{
    writer.WriteLine(string.Join(SEPERATOR.ToString(), row.ItemArray));
}

How can I organize the code with respect to easy unit testing?

I would consider making the methods take a TextReader (TextWriter) instead of a Stream. You can then pass a StringReader (StringWriter) from your unit tests, while client code will normally pass a StreamReader (StreamWriter). This will also allow client code to choose the encoding (which they really should be doing) instead of being forced to use UTF-8.

Another reason to consider doing this is that someone calling WriteToCsv might want to write to the stream after the call returns. But they will get an exception, since StreamWriter disposes of the underlying stream. For example, we get an ObjectDisposedException when we call WriteByte here:

using (var stream = new MemoryStream())
{
    using (var writer = new StreamWriter(stream))
    {
    }
    stream.WriteByte(0);
}

Finally, it makes code re-use a bit easier. For instance, WriteToSectionedCsv can be written in terms of WriteToCsv.

public static void WriteToCsv(this DataTable table, TextWriter writer)
{
    writer.WriteLine(string.Join(SEPERATOR.ToString(), table.Columns.Cast<DataColumn>()));
    foreach (DataRow row in table.Rows)
    {
        writer.WriteLine(string.Join(SEPERATOR.ToString(), row.ItemArray));
    }
}

public static void WriteToSectionedCsv(this DataSet dataSet, TextWriter writer)
{
    foreach (DataTable table in dataSet.Tables)
    {
        writer.WriteLine("[{0}]", table.TableName);
        table.WriteToCsv(writer);
    }
}
\$\endgroup\$
  • \$\begingroup\$ Thanks for your help, the code got so much shorter through your tipps. Especially the TextReader/TextWriter part simplifies the code alot \$\endgroup\$ – BoeseB Mar 4 '15 at 11:46
5
\$\begingroup\$

Two quick remarks:

\$\endgroup\$
  • \$\begingroup\$ Thanks fixed the typo. But still got to think about the naming coventions. I like to see the difference between a constant and a property by looking at the name. \$\endgroup\$ – BoeseB Mar 4 '15 at 11:48
5
\$\begingroup\$
var match = Regex.Matches(fileContetnt, PATTERN,
    RegexOptions.IgnorePatternWhitespace | RegexOptions.Multiline);  

Because Regex.Matches() returns a MatchCollection you should use the plural form for naming the variable.

var matches = ...  

Encoding encoding = Encoding.UTF8; //Encoding.Default;
//if (Utf8Checker.IsUtf8(filestream))
//    encoding = Encoding.UTF8;  

Commented code is dead code and should be removed to improve readability.


For naming variables you should ensure that they are spelled correctly.

string fileContetnt;  

should be

string fileContent;  

You should stick to the style you have choosen. You use sometimes braces {} for single statement if and sometimes you don't.

You sometimes stack using constructs and sometimes you don't.


You should consider to change the xml doxumentation from german to english. Assume you will hire a developer which comes from outside of Germany and only speaks english.


Instead of using Peek()

while (sr.Peek() > -1)
{
    line = sr.ReadLine();  

you can also check if line != null like

while ((line = sr.ReadLine()) != null)
{

this makes the check for line == null in the following if condition redundant.


Because extension methods can also be called like normal methods, you should add a null check like

    public static void ReadFromCsv(this DataTable table, string filepath)
    {
        if (table == null)
        {
            throw new ArgumentNullException("table");
        }
        using (Stream filestream = File.Open(filepath, FileMode.Open))
        {
            table.ReadFromCsv(filestream);
        }
    }  

Because you use the variable encoding only once, you can simplify the creation of the StreamReader like

        StreamReader sr = new StreamReader(filestream, Encoding.UTF8);  

The call to table.NewRow(); is redundant, because you don't use the returned DataRow because you are using the overloaded DataRowCollection.Add() method which takes an object[] instead of a DataRow. You can safely remove it.

\$\endgroup\$
  • \$\begingroup\$ Thanks, added the argument checks. removed the whole encoding part and got rid of Peek, table.NewRow and most of the usings \$\endgroup\$ – BoeseB Mar 4 '15 at 11:50
3
\$\begingroup\$

On top of what has already been mentioned:

Your implementation will break if any of the column names or values contains a ';'. Proper CSV parsing is a tad harder than most people naively assume. Now this may or may not matter because you might say "The data will never ever contain ;" however given the number of times this phrase was uttered and then proven wrong you should consider actually either implementing a proper CSV parser or use an of-the-shelf one. It's not that hard and will protect you against Murphy (at least for that case).

\$\endgroup\$
  • \$\begingroup\$ Well i continued someone elses project wich already contained a far worse implementation of the csv parsing and didn't think about just throwing it away and use a CSV Parsing Library instead. But reading your text, i can't agree more with you. Is there any project you can recommend? \$\endgroup\$ – BoeseB Mar 5 '15 at 7:49
  • 1
    \$\begingroup\$ @BoeseB At the company I'm working at we rolled our own but this one nuget.org/packages/Net.Code.Csv seems to handle most cases (including quotes inside quoted values) \$\endgroup\$ – ChrisWue Mar 5 '15 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.