I try to import an excel file, and in one of cells it contains the following bracelet character "("
and If I don't remove this character, it results with the below errors. If I remove the character there is no error. How to solve the issue ?
[InvalidOperationException: String or binary data would be truncated.] [InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.]
Here are the codes:
private void UploadData(string path, string dbTableName)
{
//Create connection string to Excel work book
string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";
//Create Connection to Excel work book
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
//Create OleDbCommand to fetch data from Excel
excelConnection.Open();
DataTable dbSchema = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //Get First Sheet Name
OleDbCommand cmd = new OleDbCommand("Select * from [" + dbSchema.Rows[0]["TABLE_NAME"].ToString() + "]", excelConnection);
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["MyDbConn"].ConnectionString);
//Give your Destination table name
sqlBulk.DestinationTableName = dbTableName;
try
{
sqlBulk.WriteToServer(dReader);
if (dbTableName == "TempTP")
{
SDatabaseManagerData.DatabaseManagerData.UpdateTP();
}
else if (dbTableName == "TempGTIP")
{
SDatabaseManagerData.DatabaseManagerData.UpdateGTIP();
}
lbl_Error.Visible = true;
lbl_Error.Text = "Database updated!";
}
catch (SqlException ex)
{
lbl_Error.Visible = true;
lbl_Error.Text = "Database updated edilemedi! Hata: " + ex.Message;
}
excelConnection.Close();
}