Date Time values in SQL Bulk Insert

i am importing some excel files to sql server using c# console application with SqlBulkcopy and receiving following exception while importing date time values.

Exception: This exception is arising when we are going to do bulk copy, where one or more columns has datetime datatype. i think it has something to do with date format below is the error detail.

"The given value of type String from the data source cannot be converted to type datetime of the specified target column."

code snippet

  sExcelConnectionString  = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath + ";Extended Properties=" + "\"Excel 12.0;HDR=No;IMEX=1;\"";
OleDbConnection OleDbConn = new OleDbConnection(sExcelConnectionString);
                    OleDbTransaction tran; 
                    OleDbCommand OleDbCmd = new OleDbCommand(myExcelDataQuery, OleDbConn);
                    OleDbConn.Open();
                    OleDbDataReader dr = OleDbCmd.ExecuteReader();


                if (dr.HasRows && dr.FieldCount > 1)
                {
                     //DataTable dt = readReader(dr);
                    try
                    {

                        string sSqlConnectionString = connectionString.ToString();
                        string sClearSQL = "DELETE FROM " + sSQLTable;
                        SqlConnection SqlConn = new SqlConnection(sSqlConnectionString);
                        SqlCommand SqlCmd = new SqlCommand(sClearSQL, SqlConn);

                        SqlConn.Open();
                        //SqlCmd.ExecuteNonQuery();
                        SqlConn.Close();
                        SqlBulkCopy bulkCopy = new SqlBulkCopy(sSqlConnectionString);
                        bulkCopy.DestinationTableName = sSQLTable;
                        bulkCopy.BulkCopyTimeout = 0;
                        while (dr.Read())
                        {

                            bulkCopy.WriteToServer(dr);
                            imported = true;

                        }
                        OleDbConn.Close();
                        if (valToField.ToString() != "")
                        {
                            sClearSQL = "DELETE FROM " + sSQLTable + " where " + valToField + "='" + valToRemove.ToString() + "'";
                            SqlCmd = new SqlCommand(sClearSQL, SqlConn);
                            SqlConn.Open();
                            SqlCmd.ExecuteNonQuery();
                        }
                        SqlConn.Close();

                    }
                    catch (Exception ex)
                    {

                        LogMessageToFile("Error While Inserting Data from : " + excelFilePath.ToString() + "  to table :  " + dbtable.ToString() + ex.Message.ToString());

                    }
                }

Please suggest the solution

Thanks

Answers


The simple answer is to convert your excel dates to strings using the =TEXT(a1,"dd mmmm yyyy hh:mm:ss") function where a1 is a reference to the cell with the date in.

Note the four m's for the month; it's always best to use full month names and let SQL server deal with the date conversion just in case you have a date collation conflict.

As per comments below, this will work across cultures & languages =TEXT(a1,"yyyy-MM-ddTHH:mm:ss")


Don't reinvent the wheel. Use SSIS, is the right tool for this job. Create a flow that reads from the Excel source, do any transformation is appropriate, then save it the SQL Server using fast load. What you're trying to do is slower, less maintainable, less serviceable, lacks adaptability in case of changes, and ultimately incorrect (as your error shows). A proper import would have to properly validate and transform each record (eg. using Datetime.TryParse and change the output to a proper DateTime type).


"The given value of type String from the data source cannot be converted to type datetime of the specified target column."

The error message is explicit: there is a column being returned from the source as a String that you're trying to insert into a DateTime target column.


Need Your Help

jQuery custom events work in global scope, but not between objects?

javascript jquery events

Please see demo here: http://jsfiddle.net/mA6qm/1/ (expand console)

How to use Onvif Api with survillence cameras

.net camera surveillance onvif

My company provides security and surveillance services to its clients and i was asked to do R&D on how we can use onvif api. I visited their operations page and relevant wsdl definitions for so...