Accxel

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

Advertisements

One Response to “Accxel”

  1. Marcus from London Says:

    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

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: