Problem reading empty string in CSV

Mar 16, 2012 at 2:40 PM

Hi,

I'm having trouble with the Cell.LoadFromText() function. I need to read a file with several text entries, some of which are just "". I'm setting the TextQualifier to '\"', and most of the file reads correctly. For some reason, when it gets to an empty string, it reads one of the quotes as the value of that cell. I tried the following code with an input file containing only:

"",""

"",""

The code:

var input = new FileInfo(@"~\..\..\..\TestData\emptystrings.csv");
var output= new FileInfo(@"~\..\..\..\TestData\em.xlsx");
var pack = new ExcelPackage();
pack.Workbook.Worksheets.Add("Empty");
pack.Workbook.Worksheets["Empty"].Cells.LoadFromText(file,new ExcelTextFormat
                                                              {
                                                                  TextQualifier = '\"'
                                                              });
pack.SaveAs(output);

 

The output looks like this :

","

","

If I add spaces between each pair of quotes in the input file, the problem goes away.

Thanks for any help in advance.

Mar 19, 2012 at 12:53 PM

I found the problem in the source. In the ExcelRangeBase class's LoadFromText method, if you comment out the statement:

if (QCount > 1)

{
    v += new string(Format.TextQualifier, QCount / 2);
}

from lines 1753-1756 and 1782-1785, it seems to be OK.

These look like they are trying to handle multiple text blocks per cell, or maybe to read nested qualifiers as text. With or without these statements, it throws an exception saying "Text delimiter is not closed..." if you put something like "one" 2 "three" in one cell. Excel itself doesn't handle nested qualifiers properly, so I see no point in trying here.

I don't see any side effects, but it should be tested properly. I don't see a unit test for this method, so I just ran the code in my first post and messed with the csv file a bit.

I hope someone finds this useful.