C# errors updating Excel file: "External table is not in the expected format", and "Operation must use an updateable query"

A client has been using a Dynamics GP Purchasing Receipt Import application that I developed over a year ago.  The application reads receipt data from an Excel file, imports the receipts and matches them against Purchase Orders, and then updates each row in the Excel file with a status, the receipt number assigned by GP, and any notes regarding the transaction.

The client recently setup some new GP Terminal Servers running Server 2008 R2 and Office 2010, so I transitioned the Receipt Import to the new servers.

After the server move, when they tried to run the import, it was not importing all of the receipt lines, and we saw that some of the rows in the Excel file were not updated with a status or receipt number.  In the log files, we found two different error messages:

• External table is not in the expected format
• Operation must use an updateable query

Both of these are relatively common JET or ACE OLEDB driver errors when working with Excel files.  At first I thought that the updates to the Excel file were consistently failing, but some rows were updated successfully.

I then thought that maybe there was a data type issue or formatting issue in some Excel rows that was preventing them from being updated--something I've seen regularly, and is one of the reasons why I no longer recommend using Excel as a data source.  But after reviewing the data for the rows that had update errors, I didn't see anything wrong with the data.

I researched both errors, but the common solutions for each were not applicable.  I've done plenty of integrations using Excel files, so I knew that my connection string was fine, my Excel provider was correct, and that the Excel file permissions were not the issue.  After all, most rows were updating properly--but every once in a while, an update would fail.

After a dozen rounds of debugging and stepping through the code, I couldn't figure out the exact cause, but I accidentally found a workaround to avoid the errors.

It seems that there might be an issue with the Microsoft ACE driver when it attempts to connect to an Excel 2007 or 2010 file.  In some cases, the Connection Open command or an Execute command will simply fail, returning one of the two errors listed above.

It seems neither of the errors is accurate, but rather the connection to the Excel file is somehow bad or in an invalid state.  My guess is that this occurs when there are frequent connections and updates, which is what my import is doing--updating hundreds of rows in the Excel file, one at a time, after each receipt line is imported into GP.

While stepping through the code, after the exception was caught, I jumped back to the point where my connection string was built and the connection was opened--just to try the same update query again and confirm where the exception occurred.  But the second time the code ran, the connection opened fine, and the query executed properly.

One moment, the query fails, the next moment it works fine, as long as the connection was re-opened.

So, I added an additional try / catch block around my connection open and execute commands, and in case of an exception, the code simply tries to reconnect to the file a second time.


try
{
    //Do not use IMEX=1 on updates to Excel, as the IMEX parameter prevents updates
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();
                   
    rowsAffected = xlCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
    log.Write("Re-trying UpdateExcelRow for query (" + ex.Message + "): " + xlQuery, false, true);
    //Try and close and then re-open the connection
    xlConn.Close();
    connString = "provider=" + Properties.Settings.Default.excelProvider + ";Data Source='" + fileName + "';Extended Properties=\"" + Properties.Settings.Default.excelWriteSettings + "\";";
    xlConn = new OleDbConnection(connString);
    xlCmd = new OleDbCommand(xlQuery, xlConn);

    xlConn.Open();

    rowsAffected = xlCmd.ExecuteNonQuery();
}


It's a bit like using duct tape and twine, but this approach appears to have resolved the issue, and was much quicker than diagnosing the underlying cause, which may be an ACE driver issue.  I was able to watch the exceptions occur occasionally, but the second connection Open and Execute commands always seemed to work.

The new code is now in production, so I'll see if that fully resolves the issue, or if there is some other gremlin waiting to cause new problems.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com