Solved

Excel rows into DataSet

Posted on 2014-01-28
6
398 Views
Last Modified: 2014-03-30
Below is an example of how my excel spread sheet is formatted.

example layout
I would like to take the contents and save them into a dataset.  The first eight columns are associated with each row to the right (if that makes sense).

I am able to query the rows that have data but that's as far as I have gotten.

Any guidance would be appreciated.

        Dim str As String = lsbTabs.GetItemText(lsbTabs.SelectedItem)

        Dim DtSet As System.Data.DataSet
        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim connstring As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & someExcelFile & ";Extended Properties=""Excel 12.0;HDR=NO"";"

        MyConnection = New System.Data.OleDb.OleDbConnection(connstring)

        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [" + str + "$] WHERE F1 <> '' AND F2 <> '' AND F3 <> '' AND F4 <> '' AND F5 <> ''", MyConnection)
0
Comment
Question by:Jedidia
  • 3
  • 2
6 Comments
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39815497
"Fill" your dataset from your oledbdataadapter

MyCommand.Fill(DtSet)

I'm not exactly sure how the merged cells will affect the fill method. You might have to iterate through all the rows and update the fields in the merged columns with the values from the first row.
0
 

Author Comment

by:Jedidia
ID: 39815520
Thanks.  I fill the dataset and it only returns the first rows.   See the attached.  It skips all the rows with the "x"s.

example 2
0
 
LVL 18

Expert Comment

by:UnifiedIS
ID: 39815551
I'm guessing that has to do with the merged cells.
Which column is "F" that you have in your WHERE clause?
What do you get if you modify the WHERE criteria?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Jedidia
ID: 39815634
I remove the WHERE clause and the dataset looks like the below.

example 3
Perhaps I can manipulate the dataset to populate the missing data based on the first row..??
0
 
LVL 18

Accepted Solution

by:
UnifiedIS earned 500 total points
ID: 39815723
Yes, you could probably come up with a way to loop through the rows to do that assuming the sort order remains intact.
Something like this should get you started. Basically, I'm checking for values in the first cells, if they are blank, use the values from the previous row.

dim dr as datarow
dim drPreviousRow as datarow
for x as integer = 0 to DtSet.tables(0).rows.count-1
dr=DtSet.tables(0).rows(x)
if dr.item(0) = "" AND dr.item(1) = "".... then
   if not drpreviousrow is nothing then
      dr.item(0) = drPreviousRow.item(0)
      dr.item(1) = drPreviousRow.item(1)....
   endif
endif
drPreviousRow = dr
next
0
 
LVL 10

Expert Comment

by:broro183
ID: 39961281
I can't tell if your cells are still meant to be merged, but it looks like they aren't merged in your last screenshot.

Perhaps I can manipulate the dataset to populate the missing data based on the first row..??

This vba code gives an example of how you can populate the missing data. It assumes that it is okay to convert the whole of the current region to values. If this isn't desired, the range can be changed using .Resize.


Option Explicit

Sub Macro1()
    With ActiveSheet.Range("A1").CurrentRegion
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With
End Sub

Open in new window


hth
Rob
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

912 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

21 Experts available now in Live!

Get 1:1 Help Now