I’ve been having some fun shuffling data between Excel and Access (well just Jet really) recently. Mainly this was to get around the huge memory leak that existing in previous excels when using Jet to query an open workbook. From my very limited trials this issue now seems fixed in 2007. (This code is for Office 2007 btw.)
Anyway here is the connection string and routine (in C#) to extract data from a workbook range and append it to an access table. I’m posting it here in the vain hope that the next time I need I will be able to find it. And maybe someone else might find it useful. And if not what are you doing instead?
public static void ImportFromExcelToAccess(string AccessFileFullName, string ExcelWBFullName, string AccessTableName, string SelectSQL)
{
string connect = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” +
ExcelWBFullName + “;Extended Properties=\”Excel 12.0;HDR=YES\”";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.Connection = conn;
string s = “INSERT INTO [MS Access;Database=" + AccessFileFullName + "].[" + AccessTableName + "] ” + SelectSQL;
cmd.CommandText = s;
Debug.Print(s);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
(Its static because I am calling it from Excel DNA)
I got most of it from the very nice man here.
Cheers
simon
Tuesday, 21st September, 2010 at 10:52 am |
Hi simon – I’ve been doing similar but you’d probably call it SQLxel rather than Accxel.
Here’s similar method, but appending to SQL Server via SqlBulkCopy. As this is part of a generic data handling class, some of the internals
(such as cnn, SourcePath and m_ErrMsg) are external to the method. There’s another ‘Excel’ class which retrieves sheet names from a given file.
public bool LoadSpreadsheet(string SourceFile, string SourceSheet, string TargetTable)
{
bool ReturnValue;
string cnn_str = @”Provider=Microsoft.Jet.OLEDB.4.0; Data Source=”
+ SourcePath + SourceFile + @”; Extended Properties=”"Excel 8.0;HDR=Yes”";”;
string SQL = “SELECT * FROM [" + SourceSheet + "$]“;
OleDbConnection cnn = new OleDbConnection(cnn_str);
OleDbCommand cmd = new OleDbCommand(SQL, cnn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
try
{
cnn.Open();
DataSet ds = new DataSet();
da.Fill(ds);
SqlBulkCopy sqlCopy = new SqlBulkCopy(Data_Loader.Properties.Resources.cnn_admin,
SqlBulkCopyOptions.TableLock);
sqlCopy.DestinationTableName = “dbo.” + TargetTable;
sqlCopy.BatchSize = m_BatchSize;
sqlCopy.WriteToServer(ds.Tables[0]);
sqlCopy.Close();
ReturnValue = true;
}
catch(Exception ex)
{
m_ErrMsg = ex.Message;
ReturnValue = false;
}
finally
{
cnn.Close();
}
return ReturnValue;
} // LoadSpreadsheet