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;
      }
    }

SqlBulkCopy doesn't deal with CSV files at all. It's CsvReader that loads CSV data. `#` is a comment character so CsvReader ignores those lines — Panagiotis Kanavos, Dec 10 '20 at 22:47

Panagiotis Kanavos · Answer 1 · 2020-12-11T15:40:13.990

0

# 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');

edited Dec 11 '20 at 15:40

answered Dec 10 '20 at 22:55

Panagiotis Kanavos

120,703
13
188
236

Thanks for your comments. This turns it into a different issue. I'm using LumenWorks.Framework.IO.Csv package (corporate standard), the reader doesn't have a AllowComment property... – JQW Dec 11 '20 at 15:24
@user14804033 it must have some other property to disable comments or change the comment character. There's no standard comment character. CSV is a very simple format. The [semi-official standard](https://tools.ietf.org/html/rfc4180) doesn't specify much more than quoting and delimiters. What your CsvReader does is custom behavior so it needs to be able to change – Panagiotis Kanavos Dec 11 '20 at 15:27
@user14804033 PS. It looks like that particular name refers either to a 10-year old CodeProject article or a 3-year old NuGet. You can specify the comment character in the constructor. Try passing something that won't appear in the files, eg a graphical character, or even the NUL character (`'0x00'`) – Panagiotis Kanavos Dec 11 '20 at 15:35
Thank you very much! This has worked for me - using NUL as the comment character. – JQW Dec 11 '20 at 16:22

CwvReader not loading lines starting with #

1 Answers1