none
How do I query multiple ranges from Excel

    Dotaz

  • I query a sheet as follows.
    Select * from [Accounts$:A7:D7]

    If I also want to pull in another range, how do I do that or is it even possible?

    I have tried
    Select * from [Accounts$A7:D7,E8:G8]
    and
    Select * from [Accounts$A7:D7;E8:G8]

    Is there anyway to do this?


    JC

    8. března 2012 17:21

Odpovědi

  • Hello JC,

    The following (VS2010 VB.NET code) uses OleDb to read your first range into a DataTable followed by executing another select statement to obtain the second range which has one row. The row from the second select is imported into the first DataTable. Review the results below the code. If using VS2008 then you will need some line continuations. Lastly replace my file name with yours in the private variable ExcelFileName. Hope this helps.

    Private ExcelFileName As String = IO.Path.Combine(Application.StartupPath, "Sample.xls")
    Private Sub Button1_Click(
        ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtRange1 As New DataTable
        Dim dtRange2 As New DataTable
        Using cn As New OleDbConnection With
            {
                .ConnectionString =
            <Value>
         provider=Microsoft.Jet.OLEDB.4.0; 
         data source='D:\Dotnetland2010\Excel\ReadRangeDual_1\bin\Debug\Sample.xls'; 
         Extended Properties="Excel 8.0; IMEX=1;HDR=No;"            
            </Value>.Value
            }
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Accounts$A7:D7]", cn)
            dtRange1.Load(cmd.ExecuteReader)
            cmd.CommandText = "SELECT * FROM [Accounts$E8:G8]"
            dtRange2.Load(cmd.ExecuteReader)
            dtRange1.ImportRow(dtRange2.Rows(0))
            dtRange1.TableName = "T1"
            dtRange1.WriteXml("Demo.txt")
        End Using
    End Sub

    Results viewed as XML

    <?xml version="1.0" standalone="yes"?>
    <DocumentElement>
      <T1>
        <F1>1</F1>
        <F2>2</F2>
        <F3>3</F3>
        <F4>4</F4>
      </T1>
      <T1>
        <F1>5</F1>
        <F2>6</F2>
        <F3>7</F3>
      </T1>
    </DocumentElement>


    KSG

    8. března 2012 19:39

Všechny reakce

  • Hello JC,

    The following (VS2010 VB.NET code) uses OleDb to read your first range into a DataTable followed by executing another select statement to obtain the second range which has one row. The row from the second select is imported into the first DataTable. Review the results below the code. If using VS2008 then you will need some line continuations. Lastly replace my file name with yours in the private variable ExcelFileName. Hope this helps.

    Private ExcelFileName As String = IO.Path.Combine(Application.StartupPath, "Sample.xls")
    Private Sub Button1_Click(
        ByVal sender As System.Object,
        ByVal e As System.EventArgs) Handles Button1.Click
        Dim dtRange1 As New DataTable
        Dim dtRange2 As New DataTable
        Using cn As New OleDbConnection With
            {
                .ConnectionString =
            <Value>
         provider=Microsoft.Jet.OLEDB.4.0; 
         data source='D:\Dotnetland2010\Excel\ReadRangeDual_1\bin\Debug\Sample.xls'; 
         Extended Properties="Excel 8.0; IMEX=1;HDR=No;"            
            </Value>.Value
            }
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM [Accounts$A7:D7]", cn)
            dtRange1.Load(cmd.ExecuteReader)
            cmd.CommandText = "SELECT * FROM [Accounts$E8:G8]"
            dtRange2.Load(cmd.ExecuteReader)
            dtRange1.ImportRow(dtRange2.Rows(0))
            dtRange1.TableName = "T1"
            dtRange1.WriteXml("Demo.txt")
        End Using
    End Sub

    Results viewed as XML

    <?xml version="1.0" standalone="yes"?>
    <DocumentElement>
      <T1>
        <F1>1</F1>
        <F2>2</F2>
        <F3>3</F3>
        <F4>4</F4>
      </T1>
      <T1>
        <F1>5</F1>
        <F2>6</F2>
        <F3>7</F3>
      </T1>
    </DocumentElement>


    KSG

    8. března 2012 19:39
  • If you want to pull them in as a single result then I would suspect that you need to do a single UNION query. Is this what you are asking?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    8. března 2012 19:44
  • If you want to pull them in as a single result then I would suspect that you need to do a single UNION query. Is this what you are asking?

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Paul,

    I did not suggest a union as there are unequal columns in the two ranges and thought that the following would be limiting as in aliasing a non-existing column.

    Using cn As New OleDbConnection With {.ConnectionString = "Your connection string"}
        cn.Open()
        Dim cmd As OleDbCommand = New OleDbCommand(
           <SQL>
                SELECT F1,F2,F3, F4 
                FROM [Accounts$A7:D7] 
                UNION ALL 
                SELECT F1, F2, F3, '' As F4 
                FROM [Accounts$E8:G8]
            </SQL>.Value, cn)
        dt.Load(cmd.ExecuteReader)
        DataGridView1.DataSource = dt
    End Using


    KSG

    8. března 2012 19:58
  • Thanks all. I thought of doing both way and ran up against the same issue with Union. The adding of the row to the first range solves what I need, thanks.

    John


    JC

    8. března 2012 20:23
  • Kevin,

    Wasn't sure what he wanted to do when I first looked at it, but I think I understand now. I don't think it's possible to combine multiple rows from an Excel Worksheet into a single row, unless there is a column to join on. I would expect that a DataTable will need to be assembled from two separate queries (one for each range).


    Paul ~~~~ Microsoft MVP (Visual Basic)

    8. března 2012 20:33
  • Thanks all. I thought of doing both way and ran up against the same issue with Union. The adding of the row to the first range solves what I need, thanks.

    John


    JC


    John, I would keep Paul's suggestion for future reference in the event you do have equal fields in each range. This way you have a method for both equal and unequal ranges to select from. Any ways good to hear you have a solution to work with.

    KSG

    8. března 2012 20:59