Exception import Excel data to SQL Server

Question

I've have an page where the user uploads an Excel document. The document contain member information, and after submitted the files content should replace the members data contained in the SQL Server table.

How ever it's like after I added the fileUploader the codes causes an exception.

if (fileUploader.HasFile)
{
     string contentType = fileUploader.PostedFile.ContentType;
     // Excel 2007 || Excel 2003
     if (contentType.Equals("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet") || contentType.Equals("application/vnd.ms-excel"))
     {
        hidePanels();
        Members members = new Members();
        if (members.getAllNotExportedMembers().Count > 0)
        {
           //error message
        }
        else
        {
           bool backupSucceed =
                        members.backUpDatabase(Server.MapPath("~/backup-db") + @"\db-backup-" +
                                               DateTime.Now.ToString().Replace("/", "-").Replace(":", "-") + ".bak");
           if (backupSucceed)
           {
               bool fileUploaded = Helper.saveFile(fileUploader.FileBytes,
                                                            Server.MapPath("~/admin/kar/"),
                                                            fileUploader.FileName, 1, User.Identity.Name);
                if (fileUploaded)
                {
                     bool truncateSucceed = members.truncateTable();
                     if (truncateSucceed)
                     {
                        importExcel(Server.MapPath("~/admin/kar/"), "2012.xlsx");
                     }
                }
            }
        }
     }
}

If I comment everything out except:

bool truncateSucceed = members.truncateTable();
if (truncateSucceed)
{
    importExcel(Server.MapPath("~/admin/kar/"), "2012.xlsx");
    showSuccesPanel("Success");
}

It works as it should and doesn't cause an exception.

The exception:

System.Data.OleDb.OleDbException (0x80004005): External table is not in the expected format.
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at admin.admin_uploadMemberList.importExcel(String filePath, String fileName) in uploadMemberList.aspx.cs:line 91

OleDB connectionstring:

string conn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + fileName
+ @";Extended Properties=""Excel 12.0;HDR=Yes;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0""";

How can this be?

By the way, you're missing the "T" in "TypeGuessRows" in your connection string. — Bridge

KLIM8D KLIM8D · Accepted Answer · 2012-08-02T22:27:50

Allright I found what were causing the problem. It was caused by the saveFile method - if I were uploading a file and a file with the same name already existed - the exception was thrown.

public static bool saveFile(this Byte[] fileBytes, string filePath, string fileName, int category, string uploadedBy)
{
    try
    {
        FileStream fileStream = new FileStream(filePath + "/" + fileName, FileMode.Create, FileAccess.ReadWrite);
        fileStream.Write(fileBytes, 0, fileBytes.Length);
        fileStream.Dispose();
        fileStream.Close();
        FileUpload fileUpload = new FileUpload();
        return fileUpload.createFileUpload(fileName, category, filePath, uploadedBy, DateTime.Now);
    }
    catch (Exception ex)
    {
        mLog.logMessage(ex, HttpContext.Current.Request.Url.ToString(), 1);
        return false;
    }
}

It just overwrites the file as it should - but maybe someone can explain why I'm getting the exception then.

Exception import Excel data to SQL Server

1 Answers