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;\"";
