LinqToExcel returning blank rows for .csv files

Question

I've been using LinqToExcel to import data from .xlsx files successfully for a while. Recently, however, I was sent a .csv file that I'm unable to read the data of.

Let's say that the file contains the following data:

Col1 Col2 Col3
 A    B    C
 D    E    F

I've created a class for mapping the columns as such:

public class Test
{
    [ExcelColumn("Col1")]
    public string Col1 { get; set; }

    [ExcelColumn("Col2")]
    public string Col2 { get; set; }

    [ExcelColumn("Col3")] 
    public string Col3 { get; set; }
}

Then I try to read the data like so:

var test = from c in excel.Worksheet<Test>()
           select c;

The query successfully returns two Test-objects, but all property values are null.

I even tried to read the data without class and header:

var test = from c in excel.WorksheetNoHeader()
select c;

In this case, the query also returns two rows, both with three cells/values. But again all of these values are null. What could be the issue here?

I should also note that the file opens and looks perfectly fine in Excel. Furthermore using StreamReader, I'm able to read all of its rows and values.

Tu deschizi eu inchid · Accepted Answer · 2020-12-10T18:34:12.287

What type of data is in each of those columns? (string, numeric, ...)

According to Initializing the Microsoft Excel driver

TypeGuessRows

The number of rows to be checked for the data type. The data type is determined given the maximum number of kinds of data found. If there is a tie, the data type is determined in the following order: Number, Currency, Date, Text, Boolean. If data is encountered that does not match the data type guessed for the column, it is returned as a Null value. On import, if a column has mixed data types, the entire column will be cast according to the ImportMixedTypes setting. The default number of rows to be checked is 8. Values are of type REG_DWORD.

See post Can I specify the data type for a column rather than letting linq-to-excel decide?

The post Setting TypeGuessRows for excel ACE Driver states how to change the value for TypeGuessRows.

When the driver determines that an Excel column contains text data, the driver selects the data type (string or memo) based on the longest value that it samples. If the driver does not discover any values longer than 255 characters in the rows that it samples, it treats the column as a 255-character string column instead of a memo column. Therefore, values longer than 255 characters may be truncated. To import data from a memo column without truncation, you must make sure that the memo column in at least one of the sampled rows contains a value longer than 255 characters, or you must increase the number of rows sampled by the driver to include such a row. You can increase the number of rows sampled by increasing the value of TypeGuessRows under the HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel registry key.

One more thing we need to keep in mind is that the registry HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows only applies to Excel 97- 2003. For Excel 2007 and higher version, Excel Open XML (.XLSX extension) actually uses ACE OLE DB provider rather JET provider. If you want to keep the file extension as .XLSX, you need to modify the following registry key according to your Excel version:

Excel 2007: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows Excel 2010: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows Excel 2013: HKEY_LOCAL_MACHINE\Software\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

This seems to be my exact issue. All cells have the "General"-format, but most of the columns contain text rather than numbers. I tried opening the file and setting the format to "Text" and then LinqToExcel was able to read the data! Normally, however, I can't open the file and change this manually. Nor can I make changes to the registry. So how do i get around this issue? — Hwende, Dec 11 '20 at 08:09
Have you considered using Nuget package `DocumentFormat.OpenXml`? — Tu deschizi eu inchid, Dec 11 '20 at 17:19
I ended up using the Nuget package CsvReader to handle my .csv files instead. — Hwende, Dec 14 '20 at 09:14

score 0 · Answer 2 · answered Dec 10 '20 at 16:53

Did you try to materialize your query by calling ToList or ToArray at the end?

I tried to recreate your case and had no trouble reading the data from the Excel file using the following code snippet:

var excel = new ExcelQueryFactory(FilePath);
List<Test> tests = (
    from c in excel.Worksheet<Test>()
    select c
)
.ToList();

It returns two objects with all properties filled properly.

One minor thing, when I added ToList initially, I got the following exception:

The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.'

Which according to what they say in the official docs seems reasonable since I was missing Microsoft Access Database Engine 2010 Distributable on my machine.

Yeah, It's the same issue with ToList(). And I have Microsoft Access Database Engine 2010 Distributable on my machine (no errors). — Hwende, Dec 10 '20 at 17:04

LinqToExcel returning blank rows for .csv files

2 Answers2

Linked