Date Time values in SQL Bulk Insert

c# sql sqlbulkcopy sql-server

Question

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

Popular Answer

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")



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why