0

I am trying to create a Datatable reading an excel file, which has a DateTime Column,rest all other columns are string. When trying to convert the DateTime column to Datetime Datatype ,I get an exception "Unable to cast object of type 'System.DateTime' to type 'System.String'." Could someone please guide me how do I resolve this. 

try
        {
            string Ext = Path.GetExtension(Constants.XlFile);
            string connectionString = "";
            if (Ext == ".xls")
            {   //For Excel 97-03
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =" + Constants.XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
            }
            else if (Ext == ".xlsx")
            {    //For Excel 07 and greater
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source =" + Constants.XlFile + "; Extended Properties = 'Excel 8.0;HDR=YES'";
            }
            OleDbConnection conn = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter();
            cmd.Connection = conn;
            conn.Open();
            DataTable dtSchema;
            dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            string ExcelSheetName = dtSchema.Rows[0]["TABLE_NAME"].ToString();
            conn.Close();
            //Read all data of fetched Sheet to a Data Table
            conn.Open();
            cmd.CommandText = "SELECT * From [" + ExcelSheetName + "]";
            dataAdapter.SelectCommand = cmd;
            dataAdapter.FillSchema(dt, SchemaType.Source);
            dt.Columns[0].DataType = typeof(String);
            dt.Columns[1].DataType = typeof(String);
            dt.Columns[2].DataType = typeof(String);
            dt.Columns[3].DataType = typeof(String);
            dt.Columns[4].DataType = typeof(DateTime);
            dt.Columns[5].DataType = typeof(String);
            dt.Columns[6].DataType = typeof(String);
            dt.Columns[7].DataType = typeof(String);
            //dataGrid2.Columns[4].CellTemplate.ValueType = typeof(DateTime);
            dataAdapter.Fill(dt);
            dataGrid2.DataSource = dt;

            conn.Close();

        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
Nilesh Chaturvedi
  • 41
  • 4
  • What does the date value look like coming from Excel? It must be a string format or your columns are mismatched. –  Jul 16 '18 at 14:49
  • Its coming as System.String. How do I change it to DateTime. I am converting it before loading fillschema – Nilesh Chaturvedi Jul 16 '18 at 14:56
  • This is fine: dt.Columns[4].DataType = typeof(DateTime);. I'm assuming it's the cell or cells coming from Excel which is/are not in a format capable of converting from string to datetime. Check your Excel data. I bet there are entries in there that are invalid datetime formats. –  Jul 16 '18 at 15:02
  • I checked the excel but could not find any issues. How can I check which particular data has the issue. – Nilesh Chaturvedi Jul 16 '18 at 15:56
  • loop through and manually add the columns instead of using a table adapter. I showed you below with documentation. –  Jul 16 '18 at 16:34
  • @dickrichie : Thanks Buddy, yes it was the excel..... I had to loop around to check the exact cell. Now the issue seems to have been resolved. – Nilesh Chaturvedi Jul 28 '18 at 03:24

1 Answers1

0

Try loading into a data reader and loop through values.

        SqlDataReader dr = cmd.ExecuteReader();


        foreach (DataRow drow in dtSchema.Rows)
        {
                //manually add columns here from dr
        }

go here for details: Populate data table from data reader