Solved

App cannot read Excel file it created.

Posted on 2011-03-17
17
288 Views
Last Modified: 2012-05-11
I have a VB.NET app that creates an Excel file via Interop process.  The file appears fine and opens in Excel with no problem.  But if I attempt to import the same file into a datatable via OleDbDataAdapter, it throws the error: "External table not in the expected format."
If I attempt to import an Excel file that was not created by the app, it imports (loads the datatable) fine.  
Again, the exported file that I am attempting to import loads fine in Excel with no problem.
0
Comment
Question by:mdreed
  • 9
  • 8
17 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35157634
Hi,

Can you post the code that create the file and the post that intent to read it?
0
 

Author Comment

by:mdreed
ID: 35157703
Attached is the code used to create the Excel file and the code used to read it into a datatable.
Public Sub WriteToExcelSpreadsheet(ByVal fileName As String, ByVal dt As System.Data.DataTable)

        Dim iCol, iRow, iColVal As Integer
        Dim missing As Object = System.Reflection.Missing.Value
        Dim ExlApp = New Excel.Application
        Dim i As Integer
        ' Open the document that was chosen by the dialog
        Dim aBook As Excel.Workbook
        Try
            ''re-initialize excel app
            If ExlApp Is Nothing Then
                ''throw an exception
                Throw (New Exception("Unable to Start Microsoft Excel"))
            Else
                ''supresses overwrite warnings
                ExlApp.DisplayAlerts = False

                'aBook = New Excel.Workbook
                ''check if file exists
                If File.Exists(fileName) Then
                    aBook = ExlApp.Workbooks.Open(fileName)
                Else
                    aBook = ExlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet)
                End If
                With ExlApp
                    .SheetsInNewWorkbook = 1
                    '.Workbooks.Add()
                    .Worksheets(1).Select()
                    'For displaying the column name in the the excel file.
                    For iCol = 0 To dt.Columns.Count - 1
                        ''clear column name before setting a new value
                        .Cells(1, iCol + 1).Value = ""
                        .Cells(1, iCol + 1).Value = dt.Columns(iCol).ColumnName.ToString
                    Next
                    'For displaying the column value row-by-row in the the excel file.
                    For iRow = 0 To dt.Rows.Count - 1
                        Try
                            For iColVal = 0 To dt.Columns.Count - 1
                                If TypeOf dt.Rows(iRow).ItemArray(iColVal) Is String Then
                                    .Cells(iRow + 2, iColVal + 1).Value = "'" & Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                                Else
                                    .Cells(iRow + 2, iColVal + 1).Value = Trim(dt.Rows(iRow).ItemArray(iColVal).ToString)
                                End If
                            Next
                        Catch ex As Exception
                            '                           Err.WriteError("C:\ERRORS\", ex.Message, ex.StackTrace)
                            'err.WriteError(GetExecutingAssembly.Location.ToString() & "errorlog.txt", ex.Message, ex.StackTrace)
                            Console.Write("ERROR: " & ex.Message)
                        End Try
                    Next
                    If File.Exists(fileName) Then
                        .ActiveWorkbook().Save() 'fileName)
                    Else
                        .ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)
                    End If
                    .ActiveWorkbook.Close()
                End With
                Console.Write("File exported sucessfully")
            End If
        Catch ex As Runtime.InteropServices.COMException

            Console.Write("ERROR: " & ex.Message)
        Catch ex As Exception

            Console.Write("ERROR: " & ex.Message)
        Finally
            ExlApp.Quit()
            System.Runtime.InteropServices.Marshal.ReleaseComObject(ExlApp)
            aBook = Nothing
            ExlApp = Nothing
        End Try
    End Sub

Public Sub Get_Excel_Data()

        Dim dt As New System.Data.DataTable
        Dim myDataset As New DataSet()
        Dim cn As String

        '       Dim cn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        '                                       "Data Source=" & txtImportFile.Text & " ;" & _
        '                                       "Extended Properties=Excel 8.0;"
        '       Dim cn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        '                               "Data Source=" & txtImportFile.Text & " ;" & _
        '                               "Extended Properties=""Excel 8.0 xml;HDR=YES;"""
        Try
            Select Case FileExtension(txtImportFile.Text)
                Case "XLSX"
                    cn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & txtImportFile.Text & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"""
                Case "XLS"
                    cn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                                    "Data Source=" & txtImportFile.Text & " ;" & _
                                                    "Extended Properties=Excel 8.0;"
                Case Else
                    Dim msg As String = "Invalid file extension"
                    Throw New ApplicationException(msg)
            End Select
        Catch exBadExtension As Exception
            MsgBox(exBadExtension.Message, MsgBoxStyle.Exclamation, Me.Text)
            Exit Sub
        End Try

        Dim myData As New OleDbDataAdapter("SELECT * FROM [" & cboSheets.Text & "$]", cn)
        Try
            myData.Fill(dt)
            dtErrors = dt
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, Me.Text)
            Clear_Errors()
        End Try

    End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35158064
Ok, I tried you code using both Jet.OLEDB.4.0 and Microsoft ACE OLEDB 12, and works fine in my environment.

So, we have to check the following:

Wich connection string trhows you the error? Jet or ACE?
Wich Excel you are using? 2003/2007/ 2010?
What is the name of the Sheet you are trying to read?
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35158146
Also, can you post a file generate in your environment?
0
 

Author Comment

by:mdreed
ID: 35158169
The Case path is 'XLS' since it was created as an 'XLS' file and the file opens fine.
 
The failure is with the 'myData.Fill(dt)' statement.

As noted earlier, the data.fill works fine with and XLS created outside the app.
0
 

Author Comment

by:mdreed
ID: 35158209
Sample Export file (that won't import) is attached. Export.xls
0
 

Author Comment

by:mdreed
ID: 35158239
I notice that the attached file (above) is flagged with 'Possible file type mismatch'.
Apparently this web app doesn't think it's a real Excel file.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35158490
Ok, yes the file you post is a UnicodeText file, that is why you cannot read it, if this is a file you create with you application then your Excel configuration is saving the file like Unicode,

Try to modify the function that creates the file by setting the FILE TYPE manually, so change this line


.ActiveWorkbook().SaveAs(fileName, missing, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)

for this one

.ActiveWorkbook().SaveAs(fileName, Excel.XlFileFormat.xlWorkbookNormal, missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing)

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:mdreed
ID: 35158605
I had already tried that and it made no difference.  I just tried again to make sure, and it failed with the same error.
More information:
Excel 2002 opens the file with no comment or error, but Excel 2007 gives a warning saying that the file format is different than that of the file extension, but if you say to open the file anyway, it opens fine.
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35158903
Ok, there has to be something with Excel, have you tried to run the application in another machine? to see if it works in another environtment?
0
 

Author Comment

by:mdreed
ID: 35159309
I have only tried the import on my dev machine, but I have deployed the export function to another machine and the export works.  I need to get the import working, that is, figure out why my exported xls is not considered a 'real' Excel file before deploying the version that will import as well.
0
 
LVL 13

Accepted Solution

by:
gamarrojgq earned 500 total points
ID: 35159345
ok try this, create a file in your dev machine, and create another file in another machine where you deployed the application.

Open each file but not in Excel, but right click on each one, selects OPEN WITH and select NOTEPAD, and tellme if the 2 files are plain text or have the excel format (a lot of symbols)

If both files are plain text, then it would be a problem of the process, but if the file created on the other machine has execel format, then it would be your machine.
0
 

Author Comment

by:mdreed
ID: 35159510
I feel pretty lame about now.  The file being created out of the 'Exce' export routing is in fact a csv text file !
I rooted around on the net to find a descent routine for exporting a SQL datatable to Excel and found a dll called RKLib.ExportData.dll.  It is very quick(probably because it is only writing a text file) and easy to use, but it is clearly not writing an Excel formatted file.

Although I am about to award the 500 points, do you have a routine or piece of code that will create an Excel file from a datatable ( the 'dt' in my myData.Fill(dt) statement)?
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35159627
ok so, you are not using the WriteToExcelSpreadsheet function you post? because that function does exactly that, export a datatable to an Excel File, I did not see any reference to RKLib.ExportData.dll in the code you posted.
0
 

Author Comment

by:mdreed
ID: 35159798
I was testing the RKLib function in lieu of the WriteToExcelSpreadsheet at some point and I had forgotten to change it back.  Now, I am executing the latter and the file opens with no warning in Excel 2007 and imports (myData.Fill(dt)) correctly as well with no errors.

I guess that is the price one pays for trying different approaches to a problem.

Thanks for you patience.
0
 

Author Closing Comment

by:mdreed
ID: 35159808
Operator error . . .
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35159837
Ok, no problem, great that you can figure it out
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now