Importing the saved .xls through oledb (select * from sheet) doesn't work unless you open the file again and save it


We use Aspose to save excel spreadsheets. Our website does importing off of these files.
Something weird is going on. If I save from the site and directly try to import the file using:
Dim strConn As String
If ContainsHeaders Then
strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & SourceFile & “;Extended Properties=”“Excel 8.0;IMEX=1;HDR=YES”""
strConn = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & SourceFile & “;Extended Properties=”“Excel 8.0;IMEX=1;HDR=NO”""
End If

Dim myConnection As New OleDbConnection(strConn)
Dim myCommand As OleDbDataAdapter
Dim ExcelSheets As DataTable = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)

myCommand = New OleDbDataAdapter(“SELECT * FROM [” & ExcelSheets.Rows(0).Item(“table_name”) & “]”, strConn)
Dim myDataSet As DataSet = New DataSet
myCommand.Fill(myDataSet, “Test”)
myCommand = Nothing
myConnection = Nothing

My column count is 1 even though if you open the excel file it looks like everything is there.
If I then open and resave it, the column count is now correct.

Do you have an suggestions or knowledge of this type of problem???



Hi Karen,

Have you tried the latest hotfix?


It did not help and it introduced several other problems that we did not have before. Do you have any other suggestions?



Hi Karen,

Why don’t use Cells.ExportDataTable to export data in excel file?



Would I use the Cell.ExportDataTable instead of the Save?
What is the difference?



Hi Karen,

Please refer to
If you want to import data from an Excel file, you can try this:

Dim excel as Aspose.Excel = new Excel()


Dim ExcelSheets As DataTable

ExcelSheets = excel.Worksheets(0).Cells.ExportDataTable(1,1,5,5)

MS Excel allows the Excel file’s format to be more flexible than oledb driver.



I am dealing with the same problem as Karen. I understand that using ExportDataTable() would solve the issue but it is not straight forward to use. I have to deal with the dimensions of the worksheets rather than just straight forward import the whole excel file.

I would rather prefer a method like:


which exports the whole excel file into a DataSet with DataTables for each Worksheet in the file.



Because there may be many null values in worksheets, we haven’t thought of providing such a method. We will check the feasibility of your suggestion. Thank you.