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
  #1  
Old 08-15-2007, 05:03 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default Problems reading an Excel file into a dataset


Based on the info at "Read Excel Data into a Dataset" in Help I get it to work, but it always ignores the first row (whatever it contains) and up until the first row without any numeric values. This even though I make an explicit choice of rows/columns, like so:
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & sheet & "$A1:BZ30]", cn)

To be expected, or is there a workaround?

Also, is there a way to read an Excel sheet directly into a table instead of a dataset? Now I do Excel --> dataset --> table, which is not very efficient.

Thanks in advance
Reply With Quote
  #2  
Old 08-15-2007, 09:12 AM
MKoslof's Avatar
MKoslofProblems reading an Excel file into a dataset MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

What database are you trying to read into? MS Access, SQL Server? There are tons of different ways you can bypass the middle man in SQL Server, data import and export tools are abundant.

For MS Access, the most straight forward way would be to use the Jet OLEDB 4.0 driver and create a direct query between the source table and the source spreadsheet. You could do this via dynamic sql or a parameterized query in MS Access, etc.
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #3  
Old 08-19-2007, 05:08 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

To better explain things: I have a set of Excel files that I need to import into arrays on demand. The customer doesn't want to deal with databases at this time, and the Excel files will change over time, so this is the way forward for the short term. I do use OLEDB to import one Excel file and sheet at a time into a dataset, like this:

Private Function LoadExcel(ByRef ds As System.Data.DataSet, ByVal file As String, ByVal sheet As String) As Boolean
Dim cn As System.Data.OleDb.OleDbConnection
Dim cmd As System.Data.OleDb.OleDbDataAdapter

Try
cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;da ta source=" & file & ";Extended Properties=Excel 8.0;")
cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [" & sheet & "$]", cn)
cn.Open()
cmd.Fill(ds)
cn.Close()
Catch ex As Exception
VisaFel("Invalid combination")
Return False
End Try

Return True
End Function

I then also convert the dataset into an array with only floating point values for quicker reference. Note 'y = 1' to compensate for the fact that the import skips the first row for whatever reason, even if it contains values.

x = 0
y = 1 'TODO: Solves Excel import skipping empty rows
For Each table In MechDataDataSet.Tables
For Each row In table.Rows
For Each column In table.Columns
Mechdata(x, y) = GetVal(row(column).ToString)
x += 1
Next column
x = 0
y += 1
Next row
Next table

This is possibly a very crude way to get an Excel sheet into a table, but it works .
Reply With Quote
  #4  
Old 08-19-2007, 06:29 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

It looks like you're missing one setting in the connection string.

If the first row contains Fieldnames (column names) then use:
"Extended Properties='Excel 8.0;HDR=Yes'"

If the first row contains data then use:
"Extended Properties='Excel 8.0;HDR=No'"
__________________
Kind regards,
Dennis

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

Didn't think of that.

Thanks
Reply With Quote
  #6  
Old 08-22-2007, 02:39 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

FYI: Even with "HDR=No" it skips completely empty intial rows.
Reply With Quote
  #7  
Old 08-22-2007, 05:40 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Is it possible for you to upload to the board a sample of the Excel file?
__________________
Kind regards,
Dennis

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

Sure. Here's a stripped down sample where the first row is ignored. If I add some text to the first row it's included.

Thanks for helping out.
Attached Files
File Type: txt 1_4301.xls.txt (15.0 KB, 8 views)
Reply With Quote
  #9  
Old 08-23-2007, 12:49 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I also noted that sometimes when row = column then row(column).ToString returns "" even though there's a value in the sheet.

I looked for a "copy dataset to array" method but didn't find any.
Reply With Quote
  #10  
Old 08-27-2007, 04:23 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I know now what's going wrong, but not how to fix it.

Strangely, if there's text or numbers on one row, the Excel file parser assumes there's text or numbers also on that same column the row under, even though all data in the sheet is set to Category General. I found one case where the reverse applied, but in that case it was one cell with text at the top that became blank and then a lot of numbers vertically from there that were handled correctly.

Hence, if it says cell per cell (the general case):

Some text, 23, 43
12, Banana, 32

It will read out:
Some text, 23, 43
<blank>, <blank>, 32

Anyone knows if this can be controlled, by explicitly selecting data type for a cell? Ideally it would read in everything as strings and I would then parse for validity even if it's numbers.

This looks like a serious "magic when magic is not called for" shortcoming to me.

Many thanks in advance. I really need to crack this.
Reply With Quote
  #11  
Old 08-27-2007, 04:44 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

It doesn't matter what type I use in the sheet. If I set a cell to Text it can still be considered a Double (as seen via column.DataType.Name) even when it clearly contains an unparsable text.

I tried setting DataType on the column, but I can't change type that way.
Reply With Quote
  #12  
Old 08-27-2007, 04:59 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

It seems that Fill is the suspect here.
Reply With Quote
  #13  
Old 08-27-2007, 05:38 AM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

It's even worse than I thought: If a cell contains a number but the cell is considered a String, the value will be "". My theory is that it thinks it should be a Double and instead of then returning "1" it considers the types incompatible so nothing is returned.

The only thing I want is that everything is considered String. How do I control that? I need to lock the type for all cells somehow, so Fill doesn't get "creative".
Reply With Quote
  #14  
Old 08-27-2007, 05:51 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi again,

Sorry for late reply but I didn't get any message until Your latest comments.

Excel worksheets does not play by the usual database rules unless we strictly design the worksheets to be genuine datatables.

The first 25 cells (or as in older versions of Excel the first 8 cells) in each column controls the datatype each column will represent. This explain why some data is left out when aquire it from worksheets.

The present layout of the worksheet does not make it workable to use a database approach for it (which your approaches directly confirm).

Actually, the best approach is simple to open it, require the wanted data and then close it. Is that possibile for you?
__________________
Kind regards,
Dennis

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

Yes, that's sufficient, as long as I don't need to parse the Excel file. Even possible to copy the data directly to an array?
Reply With Quote
  #16  
Old 08-27-2007, 11:02 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

See if the following example will work for you:

Code:
Imports Excel = Microsoft.Office.Interop.Excel Imports System.Windows.Forms Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook = xlApp.Workbooks.Open("c:\1_4301DW.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) 'This will give 1620 (54 columns * 30 rows) MessageBox.Show(CStr(Arr.Length)) 'additional code xlBook.Close(SaveChanges:=False) xlApp.Quit() 'cleaning up xlSheet = Nothing xlBook = Nothing xlApp = Nothing GC.Collect() GC.WaitForPendingFinalizers() GC.Collect() GC.WaitForPendingFinalizers() End Sub End Class
__________________
Kind regards,
Dennis

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

I'm lacking the Microsoft.Office.Interop.Excel though. I've made a complete install of Office 2003.

This seemed interesting:

This sample requires Microsoft Office 2003 with .NET Programmability Support and Microsoft Office Primary Interop Assemblies.
Reply With Quote
  #18  
Old 08-27-2007, 03:02 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I secured that both .NET Programmability Support and Microsoft Office Primary Interop Assemblies are installed, but Microsoft.Office.Interop.Excel doesn't show up as an addable reference, and it can't be resolved. I might be missing the point though.
Reply With Quote
  #19  
Old 08-27-2007, 03:15 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

Reply With Quote
  #20  
Old 08-27-2007, 03:32 PM
redsmurph redsmurph is offline
Freshman
 
Join Date: Jan 2005
Posts: 43
Default

I get this after I have added references to Office 11.0 and Excel 11.0:

An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in PipeCalc.exe

Additional information: Gammalt format eller ogiltigt typbibliotek. (Undantag från HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
(translated: Old format or invalid type library. (Exception from HRESULT...))

Seems I need to take some other route.
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
 
-->