CODEWITHSUNDEEP
c#sqlsql-serversqlbulkcopy
steave finner
steave finner

Reputation: 501

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

Upvotes: 0

Views: 4898

Answers (3)

Rich Andrews
Rich Andrews

Reputation: 4188

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

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294217

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

Upvotes: 1

to StackOverflow
to StackOverflow

Reputation: 124696

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

Upvotes: 0

Related Questions