1

When I import from excel to dataset my date format changes

My code is as follows:

DataSet ds = new DataSet();
string myConnStr = "";

if (ds != null)
{
    myConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + "; " + "Extended Properties=\"Excel 12.0;HDR=YES\"";
}
else
{
    myConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
}

OleDbConnection myConn = new OleDbConnection(myConnStr);
try
{
    OleDbCommand cmd = new OleDbCommand("select * from [Sheet1$] ",myConn);
    OleDbDataAdapter adapter = new OleDbDataAdapter();
    adapter.SelectCommand = cmd;
    myConn.Open();
    adapter.Fill(ds);
 }
catch
{ }
finally
{
    myConn.Close();
}

If I am reading row by row from excel then I can use DateTime.FromOADate Which I cant do it here. Please Help

Ilya Ivanov
  • 23,148
  • 4
  • 64
  • 90
Rohit
  • 10,056
  • 7
  • 50
  • 82
  • In what way does the format change? How do you know? What do you want it to be? Can you not convert on display? – Floris Apr 10 '13 at 11:51
  • My date in excel is 06-11-2012 however it changes to 41219 in dataset and I am not displaying it – Rohit Apr 10 '13 at 11:52
  • You are seeing the time serial number! That is actually how Excel stores date/time. You need to convert back to Date/Time. Do you know how to do that? – Floris Apr 10 '13 at 11:53
  • 1
    http://stackoverflow.com/questions/965017/datetime-format-mismatch-on-importing-from-excel-sheet check this, the accepted answer might be helpful – Deepanshu Goyal Apr 10 '13 at 11:53
  • If you have the option of storing the date as a string in excel, you can then do the conversion after the import. Or go with the solution @Deepanshu pointed to. – Floris Apr 10 '13 at 11:58

1 Answers1

1

one solution would be converting your Integer back to Datetime

A sample code would be like

public DateTime ExcelSerialDateToDT(int nSerialDate)
{

int l = nSerialDate + 68569 + 2415019;
int n = ((4 * l) / 146097);
l = l - ((146097 * n + 3) / 4);
int i = ((4000 * (l + 1)) / 1461001);
l = l - ((1461 * i) / 4) + 31;
int j = ((80 * l) / 2447);
int nDay = l - ((2447 * j) / 80);
l = (j / 11);
int nMonth = j + 2 - (12 * l);
int nYear = 100 * (n - 49) + i + l;

return DateTime.Parse(nMonth + "/" + nDay + "/" + nYear);

 }

Worked for me....

  • Worked for me as well but I doubt if it would be able to convert all the serialdates to Actual Date..Thanks – Rohit Apr 12 '13 at 04:23