Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Go Back  Xtreme Visual Basic Talk > > > Problems reading an Excel file into a dataset


Reply
 
Thread Tools Display Modes
  #21  
Old 08-27-2007, 04:01 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default


Hi again,

Can you post the code in use. I will test it with my own setups (including Swedish regional settings etc).

The error may be resolved as the following KB-article discuss:
BUG: "Old format or invalid type library" error when automating Excel.

Quote:
I'm lacking the Microsoft.Office.Interop.Excel though. I've made a complete install of Office 2003.
If Office is installed after .NET Framework then the PIAs are not automatically installed.

OK - Let's see what the outcome of above will be.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #22  
Old 08-28-2007, 07:44 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I'm quite sure this is the reason:
"You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language."
This is a fact.

Will look at this later today.

Both Office and regional settings may differ between installations, so the solution needs to cover that.

If I don't get this to work I'll post some code.

Thanks for the heads-up.
Reply With Quote
  #23  
Old 08-28-2007, 01:36 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

It was the reason...

The question is though what happens now if Excel is e.g. Swedish and/or regional settings are English or something else.

When I add CultureInfo the code snippet becomes as follows, where I added a simple cell data extraction just for testing. It stores the data into a one-dimensional System.Array, but I will convert that to a two-dimensional "normal" array, as reading out indexed values via GetValue seems cumbersome, as I need to do a lot of them on often constant positions in the sheet.

No issues with empty cells (unless they should be) as far as I can see.

Looking good so far .

Thanks mate.

Code:
        Dim xlApp As New Excel.Application

        Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
        System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")

        Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open("C:\Projects\Rorkraft2\Projects\PipeCalc\PipeCalc\bin\Debug\as\1_4301.xls")
        Dim xlSheet As Excel.Worksheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
        Dim xlData As Excel.Range = xlSheet.Range("A1:BB30")
        Dim Arr As System.Array = CType(xlData.Value, Array)

        Dim index() As Integer = New Integer() {0}

        Dim content As String = ""
        For Each content In Arr
            Debug("[" & content & "]")
        Next

        'This will give 1620 (54 columns * 30 rows)
        MessageBox.Show(CStr(Arr.Length) & " " & content)

        'additional code

        xlBook.Close(SaveChanges:=False)
        xlApp.Quit()

        System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

        'cleaning up
        xlSheet = Nothing
        xlBook = Nothing
        xlApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
Reply With Quote
  #24  
Old 08-28-2007, 02:24 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

By the way, this seems redundant considering the object references are local and will disappear from scope after function termination, hence objects will be garbage collected anyway. Am I thinking too much Java?

Code:
        xlSheet = Nothing
        xlBook = Nothing
        xlApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
Reply With Quote
  #25  
Old 08-29-2007, 07:43 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

The fix from Microsoft doesn't work if I have Swedish Excel and Swedish regional settings. Then I get the same error message again, whether I set CultureInfo to "en-US" or doesn't set it at all. Tricky.
Reply With Quote
  #26  
Old 08-29-2007, 02:19 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

An observation:

After running TerminateExcel (see below) and exiting the application the Excel process continues running, and each time I start the application again a new Excel instance is launched. That means Excel quickly fills up the virtual memory.

Code:
    Private Function InitExcel() As Boolean
        Dim xlApp As New Excel.Application
    End Function

    Private Function TerminateExcel() As Boolean
        If xlApp IsNot Nothing Then xlApp.Quit()
        xlApp = Nothing

        GC.Collect()
        GC.WaitForPendingFinalizers()
        GC.Collect()
        GC.WaitForPendingFinalizers()
    End Function
Reply With Quote
  #27  
Old 08-29-2007, 07:12 PM
Form2 Form2 is offline
Newcomer
 
Join Date: Jul 2007
Location: Hawaii
Posts: 13
Default

I am looking for the same thing, I think. I need to load some of the
columns of .xls files into an array in my vb program.

While I am able to read .xls files by invoking Excel from within vb, that is
VERY slow (8 seconds per column in a 3800 row sheet), so I am
looking for snippits of code that will do direct reads of the file.

I can get the data into a single (very) long string the same way I read a .txt file (that takes 3 seconds).
The problem is then in locating the basic data fields amongst all of the other stuff.

There is a document here: http://sc.openoffice.org/excelfileformat.pdf

that describes the format, but it is 252 pages long, and would take me weeks to comprehend.
There must be someone who has already done this type of load.

Currently, I do a Save As operation in Excel to create a tab-delimited (.txt) file
and then use that, but that is cumbersome, so I am looking for a way to read the .xls files directly.

Anyone know of a simpler document, or of a vb subroutine that handles
the parsing and extraction of the actual cell data?

Tom
Reply With Quote
  #28  
Old 08-30-2007, 04:05 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I've found that instantiating Excel.Application takes a lot of time because it launches a complete session of Excel (taking ~26MB) each time. That must therefor happen only once per session. Operations on workbooks and worksheets are very quick though. I use this code to read out data to a 1D System.Array that I later move to a 2D array of Double.

I calculate the range based on the size of my 2D array.

Code:
            Dim xlData As Excel.Range = xlSheet.Range("A1:" & Range)
            Arr = CType(xlData.Value, Array)
Parsing Excel yourself would be very complex, so if you don't have many files it's probably better to save them as CSV and then parse that.

Last edited by redsmurph; 08-30-2007 at 05:44 AM.
Reply With Quote
  #29  
Old 08-30-2007, 07:03 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

I get back to You later as I need to finish a project.

Meanwhile you can read Mike's excellent writeup about COM Interop and how to close Excel's sessions:
Automating Office Programs with VB.Net / COM Interop
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #30  
Old 08-30-2007, 02:07 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

Quote:
Originally Posted by DennisW View Post
Meanwhile you can read Mike's excellent writeup about COM Interop and how to close Excel's sessions:
Automating Office Programs with VB.Net / COM Interop
Yes, that was really helpful. The only method that worked for me was the xlProcID/Kill approach, but on the other hand it worked every time, so I removed the garbage collection stuff completely. Also became much quicker that way.
Reply With Quote
  #31  
Old 02-17-2008, 05:39 PM
jankware jankware is offline
Newcomer
 
Join Date: Dec 2007
Posts: 8
Default

can you please post the new code your using when you receive this error?
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
 
Problems reading an Excel file into a dataset
Problems reading an Excel file into a dataset
 
-->