How to properly import excel columns that have wrap text enabled using Microsoft.ACE.OLEDB and SqlBulkCopy

c# excel oledb sqlbulkcopy sql-server

Question

I'm importing rows from an excel sheet into an sql server table using bulk insert. Some columns in the excel source have wrap text enable and they are causing some issues.

If have say "wrapped text (1)" and "wrapped text (2)", they will appear like below in the excel cell.

wrapped text (1) 
wrapped text (2)

The problem i'm getting is, when that row is imported into SQL server,they will appear like below

wrapped text (1)wrapped text (2)//Note that there is no space between the two texts

I would wish that they appear like below

 wrapped text (1) wrapped text (2)//note the space between them

Below is the peace of code i'm using for the import

 public static void insertdata1(string strexcelConnectionString, string strcommand, string strsqlConnectionString, string strtrunsqlQuery, string strtablename)
    {
        using (OleDbConnection connection = new OleDbConnection(strexcelConnectionString))
        {
            connection.Open();
            OleDbCommand command = new OleDbCommand(strcommand, connection);
            //truncate the table before inserting new data.
            SqlConnection cnntrunc = new SqlConnection(strsqlConnectionString);
            //open the connection.
            cnntrunc.Open();
            //begin the transaction.
            SqlTransaction myTrans = cnntrunc.BeginTransaction();//New a transaction                        
            SqlCommand truntble = new SqlCommand();
            truntble.Transaction = myTrans;
            truntble.Connection = cnntrunc;
            try
            {
                //Create DbDataReader to Data Worksheet.
                using (DbDataReader dr = command.ExecuteReader())
                {                     
                    //Bulk Copy to SQL Server.
                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(strsqlConnectionString))
                    {
                        bulkCopy.DestinationTableName = strtablename;
                        bulkCopy.WriteToServer(dr);
                    }
                    //commit the transaction.
                    myTrans.Commit();
                }
            }
            catch (OleDbException ex)
            {
               //my error handling code here
            }
            catch (InvalidOperationException ex)
            {
                myTrans.Rollback();
                //more logging here
                throw ex;
            }
            finally
            {
                cnntrunc.Close();
            }
        }
    }

And blow is my excel connection string

strexcelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(strfilename) + "; Extended Properties=\"Excel 12.0 XML;HDR=YES;IMEX=1;\"";

Accepted Answer

After my BulkInsert, i'm updating my problematic columns as described here. How to check my data in SQL Server have carriage return and line feed?




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