oledb excel some date-columns return null

Question

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:

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.

Are the dates that work in an acceptable mm.dd.yyyy format, whereas those that fail are in dd.mm.yyyy? I.e. US vs UK format. — Andy G
"18.07.2011" returns the date-string and would be invalid for "mm.dd.yyyy". — Olli

Olli Olli · Accepted Answer · 2019-05-15T09:31:36

I was not able to solve the problem, but was able to bypass it, by changing the column-type in exel 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 the cells correctly.

oledb excel some date-columns return null

1 Answers