Click here to Skip to main content
15,917,005 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
ASM
Hey i am trying to import data from excel to sql server , it works fine till i am not passing date but now i want to pass the date to sql server it provides error as datatype not matches.
Anyone has logic or please suggest me what can i do to ..




my code is

C#
string path = string.Concat(Server.MapPath("~/TempFiles/"), FileUpload1.FileName);
               //Save File as Temp then you can delete it if you want
               FileUpload1.SaveAs(path);


               string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

               // Create Connection to Excel Workbook
               using (OleDbConnection connection =
                            new OleDbConnection(excelConnectionString))
               {
                   OleDbCommand command = new OleDbCommand
                           ("Select * FROM [Sheet1$]", connection);

                   connection.Open();

                   // Create DbDataReader to Data Worksheet
                   using (DbDataReader dr = command.ExecuteReader())
                   {

                       // SQL Server Connection String
                       string sqlConnectionString = @conn;

                       // Bulk Copy to SQL Server
                       using (SqlBulkCopy bulkCopy =
                                  new SqlBulkCopy(sqlConnectionString))
                       {
                           bulkCopy.DestinationTableName ="Table1";
                           bulkCopy.WriteToServer(dr);
                           Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                       }
                   }
               }





this code perfectly works till i am not passing date...
Posted

Might be the column in the Excel Sheet is not in a valid date format.
Change it to Date Type.
Select the Column in the Excel Sheet -> Right Click -> Format Cells -> Number Tab -> Select Date -> Choose your desired Type -> Ok
Then you try to Import...
Hope it works...
 
Share this answer
 
Comments
Member 10202727 24-Jan-14 0:00am    
i had tried this but not working...

did you try this anytime ??
Karthik_Mahalingam 24-Jan-14 0:03am    
yes i have came into this situation earlier..
what is your sql column Type ?
which version of sql ?
sql= Date or DateTime column?
Member 10202727 24-Jan-14 0:17am    
its datetime storing value as '1980-02-19 00:00:00.000'
and using sql server 2008
Karthik_Mahalingam 24-Jan-14 1:07am    
in excel which format ?
Member 10202727 24-Jan-14 1:29am    
date
It works i tried to convert it in datatble den change datatype and then insert

C#
string sqlConnectionString = @conn;
command.CommandType = CommandType.Text;
                OleDbDataAdapter objAdapter1 = new OleDbDataAdapter(command);
                DataTable dt = new DataTable();
                DataSet objDataset1 = new DataSet();

                objAdapter1.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[0][5].ToString() != "")
                    {
                        DateTime dt1 = cf.texttodb(dt.Rows[0][5].ToString());
                        dt.Rows[i][5] = dt1;
                    }}
 using (SqlBulkCopy bulkCopy =
                               new SqlBulkCopy(sqlConnectionString))
                {
                    bulkCopy.DestinationTableName = "Tablename";
                    bulkCopy.WriteToServer(dt);
                    Label1.Text = "The Client data has been exported successfully from Excel to SQL";
                }



in this i had created a function txtdob which converts my string to datetime format Thank you i tried it workes if u feel so mark it as answer
 
Share this answer
 
Comments
Christian Graus 24-Jan-14 3:48am    
You are the one who can mark this as the answer, if it worked. We cannot
Member 10202727 24-Jan-14 3:57am    
ohh is it i thought if you think it is the correct1 then u can also marked it... ;)
Better convert the particular excel field into columns

steps-1: Select the field to want to convert.

step-2 : Then Data ---> Text to columns --->(Option Delimited) Next -->Next--->In column data format, choose the format ---> and finish.

Save and try to upload.
 
Share this answer
 
Comments
satheeshkumar chinnadurai 24-Jan-14 5:45am    
sing (SqlBulkCopy copy = new SqlBulkCopy(cn))
{
copy.ColumnMappings.Add(4,"Dates");
copy.DestinationTableName = "Censis";
copy.WriteToServer(dtExcelRecords);


}


here 4 excel sheet column name
"Dates" is table column name pass like 2012-05-05
This[^] article talks about using dates in Excel for bulk inserts.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900