[RESOLVED] [Excel] Need to read date data as date problem

[RobDog888]

So I get this excel sheet (*.xls) from another company and reading it in to our system, no problems until I get to a few specific cells where they have dates. The dates are not date formatted cells nor text. When I look (right click . cell formatting... ) it doesnt show any formatting at all. When I read in the cells they read in as NULL. I cannot manually reformat the cells to date format as its all for an automated process dealing with many files every day.

Anyone have any tips on reading date data that reads in as null?

Thanks

Ps, I am connecting to it via OleDb in C# 2015 for a website project and the sheet is read in to a datatable.

order.DateLienFiled = string.IsNullOrEmpty(dt.Rows[7].ItemArray[13].ToString()) ? 
    (DateTime?)null :
            Convert.ToDateTime(dt.Rows[7].ItemArray[13].ToString());

[westconn1]

can you post a sample?

[RobDog888]

You mean attach a sample excel workbook? Sure, I'll purge out the sensitive data and leave a couple date fields.

[RobDog888]

Ok all the red date cells need reading but contain dates that read in as null. There is no formatting specified on those cells. Im sure using the EOM they probably could be read but since this is a ASP.NET project I highly doubt I can install anything on the server.

[RobDog888]

You can see it reads in as a DBNull when I manually changed the formatting to Date with mm/dd/yyyy formatting and that still didnt help. Its array element 13 for that cell.

Something about the OleDb provider seems it can only read text?

When I manually format the cell as TEXT then it works but I cannot change the formatting with an OleDb provider connection

Edit: I may see if I can have teh provider append an apostrophe preceding the date data to automatically format the cell as TEXT.

[GBeats]

EDIT**** nvm i just saw in your last post you changed it to this format......

well i check these cells i get a custom formatting, (Excel 2016) (mm/dd/yyyy) and removing the formatting i get the usual internal date (43160.## ...), maybe the DB is expecting something else?

[GBeats]

just saw somewhere converting tthe value to an OADate might work.

https://forums.asp.net/t/2139081.asp...ate+correctly+

[westconn1]

it is a bit interesting

in excel i could see formatting for the dates rows 13 to 16 as mm/dd/yy, but the dates in rows 9 and 10 were general
some of the dates are dd/mm/yyyy and others mm/dd/yyyy

not only were the dates in rows 13 to 16 returning null, but also all $ values in those rows

i sometimes got dates returned for the rows 8 to 10, depending on my connection string

EDIT: on further testing, if i limit the range for the query to a1316, all dates are returned
i am not sure if that will help you as i guess you do not know the length of the data, but it would appear you can use any arbitrary row for end of range, so maybe that can work for you

[RobDog888]

EDIT**** nvm i just saw in your last post you changed it to this format......

well i check these cells i get a custom formatting, (Excel 2016) (mm/dd/yyyy) and removing the formatting i get the usual internal date (43160.## ...), maybe the DB is expecting something else?

Yea when removed formatting the date goes to a number that Excel uses to represent the date. If you then change the formatting to TEXT then it re-shows the date. I wish I could just manually change the formatting and be done but every day we will get a new set of workbooks for importing.

[RobDog888]

I found this connection type which is supposed to make unknown date formats to be read as text basically. I ust tried it and it didnt change a thing,

ImportMixedTypes=Text

https://docs.microsoft.com/en-us/off...t-excel-driver


Im thinking after wesconn1's post that maybe I can connect to the first half range to read in the basic info and then reconnect to just the invoice section to get the GuessRows to determine the proper date data type in Excel.

The issue will still persist for the first half as its truely mixed data and I cant separately connect to each cell as probably wont be great on performance.

[westconn1]

I cant separately connect to each cell as probably wont be great on performance.

it would possibly work well enough if you read rows 1 to 8, 9 to 12 then the rest

brief testing returned all the dates

[Zvoni]

Does it help telling you, that i opened your sample-file on my Linux-Machine in Libre-Office Calc?
Rows 8 - 10 show specific cell formating "dd.mm.yyyy" (german format?!?!?!)
And rows 13 - 16 show specific cell formating "mm.dd.yyyy"
So, in Calc i cannot recreate your statement "it doesnt show any formatting at all"
Eh, and don't mind the delimiter. It's a dot in Germany....

[RobDog888]

it is a bit interesting

in excel i could see formatting for the dates rows 13 to 16 as mm/dd/yy, but the dates in rows 9 and 10 were general
some of the dates are dd/mm/yyyy and others mm/dd/yyyy

not only were the dates in rows 13 to 16 returning null, but also all $ values in those rows

i sometimes got dates returned for the rows 8 to 10, depending on my connection string

EDIT: on further testing, if i limit the range for the query to a13:p16, all dates are returned
i am not sure if that will help you as i guess you do not know the length of the data, but it would appear you can use any arbitrary row for end of range, so maybe that can work for you

Ok changing the query to a range for the invoicing table worked, Thanks!

I query the entire sheet as original but when I get to the invoicing table I create a second datatable querying just the range. I read the range for my values and then on to the next table etc.

Also, I changed the connectionstring around and added ImportMixedTypes and changed headers to NO. Everything seems to be working now upon initial testing. Seems the sampling rows of the standard 8 was causing havoc on the invoicing table as excel didnt know how to translate those data types. Also the header designation removal seems to help too from what I had read on the intewebs.

So bottom line is that the bad design/layout of the spreadsheet was causing issues reading it with a standard connection string.

Thanks guys!!

public string connectionString = "Provider=Microsoft.{0}.OLEDB.{1};Data Source={2};Extended Properties =\"Excel {3};ImportMixedTypes=Text;HDR=NO;IMEX=1\"";

[RobDog888]

Does it help telling you, that i opened your sample-file on my Linux-Machine in Libre-Office Calc?
Rows 8 - 10 show specific cell formating "dd.mm.yyyy" (german format?!?!?!)
And rows 13 - 16 show specific cell formating "mm.dd.yyyy"
So, in Calc i cannot recreate your statement "it doesnt show any formatting at all"
Eh, and don't mind the delimiter. It's a dot in Germany....

Yea the dates are formatted two different ways and a third where theres no formatting at all, not even General or Text.

I just posted the issue/solution, appreciate your time in looking at my sieeu