Click here to Skip to main content
15,887,821 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to import data from excel to database with adding some columns. But when i add date column in datatable it gives error "String was not recognized as a valid DateTime."

What I have tried:

try
       {
           string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=Yes'", path);
               connection = new OleDbConnection();
               connection.ConnectionString = excelConnectionString;
        OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
               connection.Open();
               DbDataReader dr = command.ExecuteReader();
       DataTable table = new DataTable("Customers");
       table.Load(dr);
       table.Columns.Add("UploadedFromDate", typeof(DateTime));
       table.Columns.Add("UploadedToDate", typeof(DateTime));
       table.Columns.Add("CreatedDate", typeof(DateTime));

           foreach (DataRow row in table.Rows)
           {

               row["UploadedFromDate"] = DateTime.ParseExact(txtfromdate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null);
               row["UploadedToDate"] = DateTime.ParseExact(txttodate.Text.Trim(), "yyyy-MM-dd HH:mm tt", null);
               row["CreatedDate"] = System.DateTime.Now;
           }
Posted
Updated 26-Oct-17 3:38am
Comments
cvogt61457 26-Oct-17 9:35am    
What format is the date in Excel?
Karthik_Mahalingam 27-Oct-17 5:57am    
what is the value you are getting from txtfromdate.Text.Trim()
Karthik_Mahalingam 27-Oct-17 5:59am    
refer this https://stackoverflow.com/questions/3025361/c-sharp-datetime-to-yyyymmddhhmmss-format
and check whether you are using the right format for your input
Member 12962919 27-Oct-17 6:31am    
thanks problem solved

1 solution

The error message is self-explanatory. Whatever is in "txtfromdate.Text.Trim()" (or whatever line the error message occurs on) can't be converted to a date using "yyyy-MM-dd HH:mm tt". We can't see your data so we don't know what the data is or why that might be, but if the text doesn't match *exactly* it willl error. Either use TryParseExact using a range of formats, or try handling when the text can't be converted so the error isn't raised. It all depends on what you want the flow of your code to be.
 
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