I met a trouble about bulk insert
a XLSM (excel file)
to my SQL Database
.
The norm, I can bulk insert
from excel file 2010
to SQL database
with no trouble, everything's good. But when I unistalled 2010 replaced by excel 2016
version. My project had a
"Exception thrown: 'System.Runtime.InteropServices.SEHException' in System.Data.dll"
System.Runtime.InteropServices.SEHException (0x80004005): External component has thrown an exception.
at System.Data.OleDb.DataSourceWrapper.InitializeAndCreateSession(OleDbConnectionString constr, SessionWrapper& sessionWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
at System.Data.ProviderBase.DbConnectionInternal.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at WpfApplication5.MainWindow.Laygia() in G:\WpfApplication5 - Copy (8)-20181003T095641Z-001\WpfApplication5 - Copy (8)\WpfApplication5\MainWindow.xaml.cs:line 2463
This is my code :
using (FileStream fs = new FileStream(linkpath0, FileMode.Open))
{
using (StreamReader rd = new StreamReader(fs, Encoding.UTF8))
{// linkpath = C:\Users\Win8.1 VS10 X64\Google Drive
String linkpath = rd.ReadToEnd();
string path1 = linkpath + @"\xBanggia\xBanggia.xlsm";
string excelConnectionString = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0 Xml", path1);
// Create Connection to Excel Workbook
using (OleDbConnection connection =
new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand
("SELECT * FROM[Sheet$A0:L]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = @"Data Source=.;Initial Catalog=DemoPricetable;Integrated Security=True";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Infobanggia";
bulkCopy.WriteToServer(dr);
// MessageBox.Show("!!!!!!!!!!!!!!The data has been exported succefuly from Excel to SQL");
}
connection.Close();
}
}
}
}
When I debug it through exception around line :
OleDbCommand command = new OleDbCommand
("SELECT * FROM[Sheet$A0:L]", connection);:
This is what I tried:
I try to change library
of Office
and Excel
from 14.0->16.0
Using just only an excel file but sperate versions of excel
(2010 and 2016)
I guest that this problem relate around upgrading
the excel version
Thank a lot!!!!
User contributions licensed under CC BY-SA 3.0