Error inserting data from an excel file into sql

Question

I have a problem when I insert data from excel file into the database because a field that I am entering says it is not of the type time and does not allow inserting , but in the excel file the text that is written there is only hours or type time. Here’s a print of the excel field I’m talking about:

Here is the excel table configuration:

This is the code I use to write the data in the sql table:

string ConecçãoDB = ConfigurationManager.ConnectionStrings["ConecçaoDB"].ConnectionString;
            string Table = ConfigurationManager.AppSettings["table"];

            string ssqltable = Table;

            string ssqlconnectionstring = ConecçãoDB;
            string sclearsql = "delete from " + ssqltable;


            var connectionString ="Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+filename+";Extended Properties='Excel 8.0;HDR=YES'";
            // var connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filename + ";Extended Properties=\"Excel 12.0;IMEX=0;HDR=YES;TypeGuessRows=0;ImportMixedTypes=Text\"";
            var sql = "SELECT * FROM [" + comboBox1.Text + "$]";
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);

            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();
            MessageBox.Show(sql);
            OleDbConnection oledbconn = new OleDbConnection(connectionString);
            OleDbCommand oledbcmd = new OleDbCommand(sql, oledbconn);
            oledbconn.Open();
            using (OleDbDataReader dr = oledbcmd.ExecuteReader())
            {
                using (SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring))
                {
                    bulkcopy.DestinationTableName = ssqltable;
                    bulkcopy.ColumnMappings.Add(comboBox2.Text, "TransDate");
                    bulkcopy.ColumnMappings.Add(comboBox3.Text, "TransTime");
                    bulkcopy.ColumnMappings.Add(comboBox4.Text, "CardNo");
                    bulkcopy.ColumnMappings.Add(comboBox5.Text, "VoucherNo");
                    bulkcopy.ColumnMappings.Add(comboBox6.Text, "Quantity");
                    bulkcopy.ColumnMappings.Add(comboBox7.Text, "TransactionValue");

                    bulkcopy.WriteToServer(dr);
                }
            }
            oledbconn.Close();
            button2.Enabled = true;
            oledbconn.Close();

This is the mistake you make :

Invalidcastexception: Invalid conversion of 'System.Datetime' into 'System.Timespan'.

Answer 1

If the Excel column contains Date, or Time, or DateTime, regardless of format, ACE.OLEDB will always map it to a type DateTime.

So the conversion error is occurring because the column TransTime in SQL Server is of type Time and that of Excel is DateTime.

To fix the problem, you can try one of these alternatives:

Option 1: change the column TransTime from SQL Server to DateTime

Option 2: try to change the query to:

        SELECT cstr(TransDate) as TransDate, TransTime, ...  FROM [Plan1$]

Obs: I couldn’t test this second option because I don’t have SQL Server installed on my machine but I believe it will work. The function cstr(TransDate) will make a Cast from the time to string and SQL Server accustomed to automatic cast string for time.