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...
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.
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...
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.
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
Follow
Nice Articles sir.