Search code examples
c#exceloledb

oledb excel some date-columns return null

I am reading a xlsx-file via oledb. There are some rows where a column (containing a date-string) returns null and some rows where the column (also containing a date-string) returns the date-string. In excel the column-type is set to "date".

Here is my connection-string:

$"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={PATH_TO_FILE};Extended Properties=\"Excel 12.0 Xml;HDR=NO\""

Here is the command-text to query the data:

$"SELECT * FROM [SHEET_NAME$A4:BC] WHERE F1 IS NOT NULL"

Here is how i read the data from the data-record:

var test = dataRecord.GetValue(dataRecord.GetOrdinal("F39"));

Her are some examples what the inspector shows me when test contains the date-string:

{07.01.1975 00:00:00}
{03.08.1987 00:00:00}
{03.10.1988 00:00:00}
{01.05.1969 00:00:00}
{20.12.2016 00:00:00}
{18.07.2011 00:00:00}

In other cases the inspector only show:

{}

Here is a screenshot from the xlsx-document where i have marked a line in red where the return-value is empty and green where the actual date-string is returned:

enter image description here

The date-strings are formatted like dd.mm.yyyy

Why do these rows return an empty value instead of the date-string?

As suggested by AndyG i have checked if the date-string values might fail in dependece of the format ("dd.mm.yyyy" vs. "mm.dd.yyyy"). But there are cases which are invalid for "mm.dd.yyyy" that dont fail.

Solution

  • I was not able to solve the problem, but was able to bypass it, by changing the column-type in Excel to text.

    I had to copy the whole xls-file, delete the content of the copy, set the column-type to text, copy the content from the first file and paste it into the second file. Otherwise Excel was changing the date-strings to the numbers which are used to store the date.

    Now I can read the cells correctly.