Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements
Read Excel using SQL Statements Read Excel using SQL Statements
Read Excel using SQL Statements
Go Back  Xtreme Visual Basic Talk > > > Read Excel using SQL Statements


Reply
 
Thread Tools Display Modes
  #1  
Old 02-27-2013, 08:39 AM
sanderson sanderson is offline
Junior Contributor
 
Join Date: Feb 2006
Location: Minnesota, USA
Posts: 217
Default Read Excel using SQL Statements


Hello:

I am trying to read excel data into data tables, and then join the tables. It is working, however I am having difficulty with assigning the data types. In the code below, I am trying to have the [Item] string be a string value, yet the system keeps defaulting it to double. The result is that the data is incorrect. This is my first issue.

Secondly, I want to join the two tables and have one nice data set. Somehow, I cannot have non-unique values. As of now, there is all kinds of data with multiple values for [Item] and [SONum]. [SONum] is the field I am trying to join on.

My question can be specific or general. If there is a good reference for this out there somewhere, I would love to know about it. We have so many excel systems that we need to read, and are not ready to move the data into sql as of yet. Obviously, that will be the eventual answer.

Thanks in advance for the help. Code below...

Code:
Public Class Form1

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ds1 As String = "\\stylmark.com\fs-styl\Public\HISTORY\icedet 12mos122912.xlsx"
        Dim ss1 As String = "SELECT [Item], [SONbr], [OrdDate], [Eng], [Cfg#Rev] FROM [MASTER$] " & _
                            "ORDER BY [Item] DESC "
        Dim dt1 As New DataTable
        dt1.Columns.Add("Item", GetType([String]))
        dt1.Columns.Add("SONbr", GetType([Double]))
        dt1.Columns.Add("OrdDate", GetType([DateTime]))
        dt1.Columns.Add("Eng", GetType([String]))
        dt1.Columns.Add("Cfg#Ref", GetType([String]))

        dt1 = GetExcelMethod(ds1, ss1)

        Dim ds2 As String = "\\stylmark.com\fs-styl\Public\PSE\MasterSchedule.xls"
        Dim ss2 As String = "SELECT [SONbr], [Description], [Actual Eng# Hours] FROM [Schedule$] " & _
                            "WHERE [SONbr] IS NOT NULL " & _
                            "ORDER BY [SONbr] "
        Dim dt2 As DataTable = GetExcelMethod(ds2, ss2)

        Dim ds As New DataSet("DataSet")
        ds.Tables.Add(dt1)
        ds.Tables.Add(dt2)

        GetColumnType(dt1)
        GetColumnType(dt2)

        'Dim drel As New DataRelation("EquiJoin", dt1.Columns("SONbr"), dt2.Columns("SONbr"))
        'ds.Relations.Add(drel) ' Columns currently do not have unique values

        'Dim jt As New DataTable("JoinedTable")
        'jt.Columns.Add("Item", GetType([Double])) ' This needs to be a string value!
        'jt.Columns.Add("SONbr", GetType([Double]))
        'jt.Columns.Add("OrdDate", GetType([DateTime]))
        'jt.Columns.Add("Eng", GetType([String]))
        'jt.Columns.Add("Cfg#Ref", GetType([String]))
        'jt.Columns.Add("Description", GetType([String]))
        'jt.Columns.Add("Actual Eng# Hours", GetType([Double]))

        'ds.Tables.Add(jt)

        'For Each dr As DataRow In ds.Tables("Table1").Rows
        '    Dim parent As DataRow = dr.GetParentRow("EquiJoin")
        '    Dim current As DataRow = jt.NewRow()
        '    For i As Integer = 0 To ds.Tables("Table1").Columns.Count - 1
        '        current(i) = dr(i)

        '    Next
        '    current("Dname") = parent("Dname")
        '    jt.Rows.Add(current)

        'Next
        DataGridView1.DataSource = dt1
        MessageBox.Show(DataGridView1.RowCount)

    End Sub

    Private Sub GetColumnType(dt As DataTable)
        For c As Integer = 0 To dt.Columns.Count - 1
            Debug.WriteLine(dt.Columns(c).ColumnName.ToString & ", " & dt.Columns(c).DataType.ToString)

        Next

    End Sub

    Private Function GetExcelMethod(ByVal ds As String, ByVal ss As String) As DataTable
        Dim dt As New DataTable()
        Dim csbuilder As New OleDbConnectionStringBuilder()
        csbuilder.Provider = "Microsoft.ACE.OLEDB.12.0"
        csbuilder.DataSource = ds
        csbuilder.Add("Extended Properties", "Excel 12.0 Xml;HDR=YES")

        Using connection As New OleDbConnection(csbuilder.ConnectionString)
            connection.Open()
            Dim selectSql As String = ss

            Debug.WriteLine(selectSql)
            Using adapter As New OleDbDataAdapter(selectSql, connection)
                adapter.Fill(dt)

            End Using
            connection.Close()

        End Using

        Return dt

    End Function

End Class
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
Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements Read Excel using SQL Statements
Read Excel using SQL Statements
Read Excel using SQL Statements
 
Read Excel using SQL Statements
Read Excel using SQL Statements
 
-->