CwvReader not loading lines starting with #

Question

I'm trying to load a text file (.csv) into a SQL Server database table. Each line in the file is supposed to be loaded into a single column in the table. I find that lines starting with "#" are skipped, with no error. For example, the first two of the following four lines are loaded fine, but the last two are not. Anybody knows why?

ThisLineShouldBeLoaded

This one as well

#ThisIsATestLine

#This is another test line

Here's the segment of my code:

    var sqlConn = connection.StoreConnection as SqlConnection;
    sqlConn.Open();

    CsvReader reader = new CsvReader(new StreamReader(f), false);

    using (var bulkCopy = new SqlBulkCopy(sqlConn))
    {
      bulkCopy.DestinationTableName = "dbo.TestTable";
      try
      {
        reader.SkipEmptyLines = true;
        bulkCopy.BulkCopyTimeout = 300;
        bulkCopy.WriteToServer(reader);
        reader.Dispose();
        reader = null;
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        System.Diagnostics.Debug.WriteLine(ex.Message);
        throw;
      }
    }

Answer 1

# is the default comment character for CsvReader. You can change the comment character by changing the Comment property of the Configuration object. You can disable comment processing altogether by setting the AllowComment property to false, eg:

reader.Configuration.AllowComments=false;

SqlBulkCopy doesn't deal with CSV files at all, it sends any data that's passed to WriteServer to the database. It doesn't care where the data came from or what it contains, as long as the column mappings match

Update

Assuming LumenWorks.Framework.IO.Csv refers to this project the comment character can be specified in the constructor. One could set it to something that wouldn't appear in a normal file, perhaps even the NUL character, the default char value :

CsvReader reader = new CsvReader(new StreamReader(f), false, escape:default);

or

CsvReader reader = new CsvReader(new StreamReader(f), false, escape : '\0');