The task was to provide users with an interface where they can upload the 'csv' files, these files were to be processed and loaded to Database by a Console application. The code in Console application could not handle the 'xls' files so we thought, OK, lets convert 'xls' to 'csv' in the code, Seemed like fun. The idea was to convert it right after uploading within 'csv' file.

As Microsoft does not recommend using the  Excel objects in ASP.NET, we decided to use the Jet engine to open xls. (Ace driver is used for xlsx)

The code was pretty straight, can be found on following links:

http://www.c-sharpcorner.com/uploadfile/yuanwang200409/102242008174401pm/1.aspx

http://www.devasp.net/net/articles/display/141.html

FIRST BUMP 'OleDbException (0x80004005): Unspecified error' ( Impersonation ): The ablove code ran fine in my test web site and test console application, but it gave an 'OleDbException (0x80004005): Unspecified error' in main web site, turns out imperonation was set to True and as soon as I changed it to False, it did work. on My XP box, web site was running under user

                  'ASPNET'  with imperosnation set to FALSE

                  'IUSR_*' i.e IIS guest user with impersonation set to TRUE

The weired part was that both users had same rights on the folders I was saving files to and on Excel app in DCOM Config. 

We decided to give it a try on Windows Server 2003 with web site set to windows authentication ( impersonation = true ) and yes it did work.

SECOND BUMP 'External table not in correct format': I got this error with some files and it appeared that the file from client has some metadata issues  ( when I opened the file in Excel and try to save it ,excel  would give me this error saying File can not be saved in current format ) and the error was caused by that. Some people were able to reslove the error by using "Extended Properties=HTML Import;" in connection string. But it did not work for me.

We decided to detour from here and use Excel object :( as we had no control on client setting the meta deta of Excel files.

Before third bump there were a ouple of small thingies like 'Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80070005'

Fix can be found at

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

THIRD BUMP ( Could not get rid of the EXCEL process  ):  I has all the code in place to 'Quiet' the excel, but, it just did not work. work around was done to Kill the process as we knew no other application on server was using EXCEL.  The normal steps to quite the excel application worked just fine in console application though.

 

FOURTH BUMP: Code worked with one file 1 on my machine and with the other file 2 code will break. and the same code will work perfectly fine with file 2 on some other machine . We moved it to QA  ( Windows Server 2003 )and worked with every file just perfect. But , then there was another problem: one user can upload it and second cant, permissions on folder and DCOM Conifg checked.

Another Detour: Uplooad the xls as it is and convert in Console application.

 

Lesson Learnt:  If its 'xlsx' use 'ACE Driver' or read xml within excel as recommneded by MS. If xls and you know its always going to be properly formatted  'jet Engine' 

Code:

Imports Microsoft.Office.Interop

Private Function ConvertFile(ByVal SourceFolder As String, ByVal FileName As String, ByVal FileExtension As String)As Boolean

    Dim appExcel As New Excel.Application
    Dim workBooks As Excel.Workbooks = appExcel.Workbooks
    Dim objWorkbook As Excel.Workbook 

    Try          
        objWorkbook = workBooks.Open(CompleteFilePath )                   
        objWorkbook.SaveAs(Filename:=CObj(SourceFolder & FileName & ".csv"), FileFormat:=Excel.XlFileFormat.xlCSV)  

    Catch ex As Exception
        GenerateAlert(ex.Message().Replace("'", "") & " Error Converting File to CSV.")

        LogError(ex )
        Return False
     Finally           
          If Not(objWorkbook is Nothing) then
              objWorkbook.Close(SaveChanges:=CObj(False))
          End If
          ReleaseObj(objWorkbook)                           
          ReleaseObj(workBooks)
          appExcel.Quit()
          ReleaseObj(appExcel)                      

          Dim proc As System.Diagnostics.Process
          For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
              proc.Kill()
          Next

        DeleteSourceFile(SourceFolder & FileName & FileExtension)
    End Try
 Return True
 End Function
 

Private Sub ReleaseObj(ByVal o As Object)  
  Try
     System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
  Catch ex As Exception

          LogError(ex )
  Finally
     o = Nothing
   End Try

End Sub

    Protected Sub DeleteSourceFile(Byval CompleteFilePath As string)
        Try
            Dim MyFile As FileInfo = New FileInfo(CompleteFilePath)
            If  MyFile.Exists Then
                File.Delete(CompleteFilePath)
            Else
             Throw New FileNotFoundException()
            End If
        Catch ex As Exception
            GenerateAlert( " Source File could not be deleted.")

             LogError(ex)
        End Try
    End Sub 

The code to kill the process ( Avoid it if you can ):

Dim proc As System.Diagnostics.Process
For Each proc In System.Diagnostics.Process.GetProcessesByName("EXCEL")
    proc.Kill()
Next