Converting dateformat in dataset while fetching the data from Excel


You have an requirement you need to import the Excel sheet data and export the data it into Sql-server database table.But in excel When you type a specific Date format in Excel cell that date format will be different in dataset.So how to get the original Date format in dataset.This is the article i want to discuss here.

In my previous article i had discuss how to change the scientific notation in dataset.Now same as that if there is a Dateformat in Excel the original dateformat will get disappear and new dateformat will appear(i.e with date and time) in the dataset.

See Image here in the below...

Excel Date format

I entered the date format in Excel in the cell But you see the formula bar in Excel the date will get changes dd/mm/yyyy format so after importing the excel and fetching the data in dataset it will appear date and time in dataset.so how to get the Orignal date in dataset ? I will explain in the below code through out put images.

Excel Original Date format

See the Original Excel sheet image above the date format is clubbed with date and time we need to revert back the original date format in the dataset is 3 0ct 2014

For that we need to write the below code...



public bool GetUsersDSFromExcel(string strFileName, ref DataSet dsObj, string strSheet = "", string strFilepath = "")
{
string strConnString = "";
string strQuery = "";
OleDbConnection objCon = null;
OleDbDataAdapter objDa = null;
OleDbCommand objCmd = null;
DataTable dtexcel =new DataTable();
DataSet newdatasetexcel = new DataSet();
string loopCoulmnns = "";
try
{
if (!GetExcelConnString(strFileName, strSheet, ref strConnString, ref strQuery, strFilepath))
{
return false;
}

DataSet dst = new DataSet();
dst= (DataSet)Session["dstExcel"];
for (int i = 0; i < dst.Tables[0].Columns.Count; i++)
{

//if (dst.Tables[0].Columns[i].ColumnName.ToUpper().Contains("MOBILE"))
//{
// loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],\"#####\") as " + dst.Tables[0].Columns[i].ColumnName + " ";
//}
if (dst.Tables[0].Columns[i].ColumnName.ToUpper().Contains("DATE"))
{
loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],'D MMM YYYY') as TransactionDate";
}
else
{
//Format([MobileNo], \"#####\")
loopCoulmnns = loopCoulmnns + "," + dst.Tables[0].Columns[i].ColumnName;

}

}

if (loopCoulmnns.StartsWith(","))
{
loopCoulmnns = loopCoulmnns.Remove(0, 1);
}
if (loopCoulmnns.EndsWith(","))
{
int length = loopCoulmnns.Length;
loopCoulmnns = loopCoulmnns.Remove(length - 1, 1);
}

strQuery = "select " + loopCoulmnns + " from " + "[" + strSheet + "]" + " ";
objCon = new OleDbConnection(strConnString);
objCon.Open();
objCmd = new OleDbCommand(strQuery, objCon);
objDa = new OleDbDataAdapter();
objDa.SelectCommand = objCmd;
dsObj = new DataSet();
dsObj.Locale.NumberFormat = CultureInfo.CurrentCulture.NumberFormat;
if (!string.IsNullOrEmpty(strSheet))
{
objDa.Fill(dsObj, strSheet);
}
else
{

objDa.Fill(dsObj);
}

for (int i = 0; i < dsObj.Tables[0].Columns.Count; i++)
{

//if (dst.Tables[0].Columns[i].ColumnName.ToUpper().Contains("MOBILE"))
//{
// loopCoulmnns = loopCoulmnns + "," + "Format([" + dst.Tables[0].Columns[i].ColumnName + "],\"#####\") as " + dst.Tables[0].Columns[i].ColumnName + " ";
//}
if (dsObj.Tables[0].Columns[i].ColumnName.ToUpper().Contains("DATE"))
{
dsObj.Tables[0].Columns[i].ColumnName = dst.Tables[0].Columns[i].ColumnName ;
}

}

//newdatasetexcel.Tables.Add(dtexcel);
//for (int i = 0; i < dsObj.Tables[0].Columns.Count; i++)
//{
// DataColumn dcExcel = new DataColumn();
// dcExcel.ColumnName = dsObj.Tables[0].Columns[i].ColumnName;
// dcExcel.DataType = typeof(string);
// newdatasetexcel.Tables[0].Columns.Add(dcExcel);
//}
// foreach (DataRow fSrcRow in dsObj.Tables[0].Rows)
// {
// newdatasetexcel.Tables[0].ImportRow(fSrcRow);
// }
//if (newdatasetexcel.Tables[0].Rows.Count > 0)
//{
// DataRow []dr = newdatasetexcel.Tables[0].Select("MobileNo Like '%E%'");
// if (dr.Length > 0)
// {
// for (int i = 0; i < dr.Length; i++)
// {
// dr[0].ItemArray[i] = "bhushan";
// dr[0].AcceptChanges();
// }
// }
//}
}
catch (Exception ex)
{
if (!string.IsNullOrEmpty(strFilepath))
BCCommon.WriteToFile(strFilepath, "Exception in GetUsersDSfromexcel " + ex.Message + Constants.vbCrLf, true, true);
BCCommon.WriteToFile(m_strNoRowsFound, ex.Message + Constants.vbCrLf, true, true);
return false;
}
finally
{
if ((objCon != null))
objCon.Close();
objCmd = null;
objDa = null;
objCon = null;
}
return true;
}
private bool GetExcelConnString(string strFilename, string strSheet, ref string strConnString, ref string strQuery, string strFilepath = "")
{
DataSet dst =new DataSet();
if (!string.IsNullOrEmpty(strSheet))
{
strQuery = string.Format("SELECT * FROM [" + strSheet + "]");
}
else
{
//Format([MobileNo], \"#####\")


strQuery = string.Format("SELECT * FROM [Sheet1$]");
}
try
{
if (strFilename.EndsWith("xls"))
{
//strConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFilename & ";Extended Properties=Excel 8.0", Path.GetDirectoryName(strFilename))
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";Extended Properties=Excel 8.0;", Path.GetDirectoryName(strFilename));
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.EXCEL);
}
else if (strFilename.EndsWith("xlsx"))
{
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strFilename + ";Extended Properties=Excel 12.0;", Path.GetDirectoryName(strFilename));
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.EXCEL);
}
else if (strFilename.EndsWith("csv") | strFilename.EndsWith("txt"))
{
//strConnString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;", Path.GetDirectoryName(strFilename))
strConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Text;", Path.GetDirectoryName(strFilename));
//strQuery = String.Format("SELECT * FROM {0}", Path.GetFileName(strFilename))
strQuery = "SELECT * FROM [" + Path.GetFileName(strFilename) + "]";
m_strFileType = Convert.ToString(SynapseEnums.SynapseEnums.FILETYPE.NOTEPAD);
}

OleDbConnection objCon = null;
objCon = new OleDbConnection(strConnString);
objCon.Open();
OleDbCommand cmdselect = new OleDbCommand(strQuery, objCon);
OleDbDataAdapter datadapt = new OleDbDataAdapter(cmdselect);
datadapt.Fill(dst);
Session["dstExcel"] = dst;
}

catch (Exception ex)
{
if (!string.IsNullOrEmpty(strFilepath))
BCCommon.WriteToFile(strFilepath, "Exception in GetExcelConnString " + ex.Message + Constants.vbCrLf, true, true);
BCCommon.WriteToFile(m_strNoRowsFound, ex.Message + Constants.vbCrLf, true, true);
return false;
}
return true;
}




SO after implementing the above code the below image as shown below...

After Query date

Now our date format is ready.I had written specific columns in the query oldedbcommand

as



select Format([" + dst.Tables[0].Columns[i].ColumnName + "],'D MMM YYYY')as trasactiondate from sheet1$



I had not found this Format by google search.It will be available in Excel sheet see the below Image..

Right click the Date column in the Excel cell then choose format cells we can see the below figure.

Transactiondateformat

You find different date formats in the Excel that i specify it in the select columns not only date formats you can find different formats like currency Time and Percent and that you can specify columns in the select query through format keyword

Hope this article will be Useful..... for your purpose....


Attachments

Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 74 articles authored by srirama

Comments

Author: rajkumar09 Feb 2014 Member Level: Bronze   Points : 0

Nice Articles sir.

Author: srirama09 Feb 2014 Member Level: Gold   Points : 2

Thanks Raj I am Software Engineer Like you.My aim is to bring some thing new/innovative things through my articles.Thank you for acknowledging this Article.Hope you Enjoyed reading this Article and got some vital information.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: