Pulling date and time values from excel (CSV) to C#

Question

Hi everyone,

I’m building a application that monitors information on a website. The website allows you to save stuff off it as a CSV.

My problem arises when I try to pull time and date information from Excel.

For those who want to see what spreadsheet I am working on: http://ets.aeso.ca/ets%5Fweb/ip/Market/Reports/CSMPriceReportServlet?contentType=html

if you want the spreadsheet: change the html in the link to csv

I’m having trouble with A6 downward and B6 downward column.

When I pull out the A6 column, the dynamic type is string. My question regarding this part is: Is there any way I can parse the string into a DateTime so I can format it in a standard way?

When I pull out the B6 column, the dynamic type for the time (HH:MM:SS AM/PM) becomes double. Is there any way I can parse the double into a DateTime so I can format it in a standard way as well?

Thanks so much for your time you guys!

Cheers, -Jeremiah Tantongco

Answer 1

Get .Text rather than .Value

Answer 2

if the file format is static then you can pull the data as string in first place say a5 + ":" + a6 (09/16/2009 16:15:00) reformate it as per your requirements and save is as datatime

Answer 3

Thanks for the responses! I did some more digging and was able to solve both my issues.

For parsing a custom time formatted string into a datetime in C#, use:

DateTime.ParseExact(yourString, formatString, null);

format string is the scheme of your custom time formatted string

Ex: "09/17/2009 14" becomes "MM'/'dd'/'yyyy HH"

For converting datetime from excel that is stored as a double, use:

DateTime.FromOADate(yourDouble);