• Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 66
  • Last Modified:

Learning How to Import from Excel OR CSV to Bound Data Table

Hello Experts.

A while back, I received some awesome assistance with learning how to fetch data from an Excel range, and place it directly into a DataGridView container using the following code:
    Private Sub RefreshData()
        'Create the connection string to connect to the Microsoft Excel Workbook
        Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
                                    & XL & ";Extended Properties = ""Excel 12.0 Xml;HDR=YES"""
        'Create a standard SELECT SQL statement
        Dim selectStatement As String = _
            "SELECT Player_No, Player_Name, SL, Team_ID, Team_No, Team_Name FROM [Roster$]"
        Dim adapter As New OleDbDataAdapter(selectStatement, connectionString)
        Dim excelData As New DataTable
        'Create a DataAdapter that will be used to populate a DataTable with data
        Try
            'The code to open a connection and execute a statement 
            adapter.Fill(excelData)
        Catch ex As InvalidOperationException
            MessageBox.Show(ex.Message)
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        Finally
            'Any tidy up code here to release resources
        End Try
        'Display the data in the excelDataGridView
        PlayerDataGrid.DataSource = excelData
    End Sub

Open in new window

Since then, my project has transitioned to using all bound data tables which originate from Access. One offshoot is that I no longer use just the one datagridview, but have separated the data into a more relational database format (Roster data in one table, Team data in another, etc.).
I thought for sure I could do this easily enough, but I'm stuck scratching my head. Perhaps someone could point me in the right direction?
Also, I would like to at the very least allow users who have older versions of Excel (.XLS) and even those with no Excel (for example, they could format it as a .CSV) to accomplish the same result.
If you want the scheme for the Roster and Teams data files, it looks something like this:
Roster and Teams SchemaThe desired initial layout of the DataRoster.xlsx, .xls or .csv file would be something like this:
APA_ID, Player_Name, SL, Team_ID, Team_No, Team_Name

Just to clarify the difference between Team_ID and Team_No, the APA (American Poolplayers Association) which manages everything, changes the Team_No each season, so I assign my own Team_ID for the Teams data table so I can maintain long term team statistics. Because of that, I would populate the Roster data table first, then do a SELECT DISTINCT to populate the Teams data table.

Anyway, once I learn how to do the smaller subset of columns, I should be able to figure out how to add all the other fields from the two tables.

Thanks!
Tony G.
0
Tony Gardner
Asked:
Tony Gardner
  • 3
  • 3
6 Comments
 
HainKurtSr. System AnalystCommented:
sorry, what is your question / problem here ?
I am a bit lost...
0
 
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Sorry if I didn't explain well. As you can see in the code snippet, the data is being retrieved from Excel via OLEDB and deposited directly to a DataGriView.

What I'm not sure how to do is deposit the data into the bound Roster and Teams data tables.

The second objective would be learn how to retrieve the data from a CSV file with the same column structure, and deposit that data into the bound Roster and Teams data tables.

Hope that helps!
0
 
HainKurtSr. System AnalystCommented:
it is populating DataTable, excelData
then binds them to grid...
so your data is in DataTable, which you can use to bind to any control or use data inside...
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Okay, my friend. Your gentle nudge took me just a bit further. Here's where I'm at now:

First, I created three simple files:
  • RosterData.XLSX
  • RosterData.XLS; and
  • RosterData.CSV

Next, I modified the RefreshData subroutine to select a different connectionString for each as follows:
    Private Sub RefreshData()
        'Create the connection string to connect to the Microsoft Excel Workbook
        Dim connectionString As String = ""
        Dim XL_Len As Integer = Len(XL) - 1
        Dim FileType As String = XL.ToString.Substring(XL_Len - 3, 3)
        FileType = FileType.ToString.ToUpper
        Select Case FileType
            Case "LSX" ' Excel 2012+ (XLSX)
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & XL _
                    & ";Extended Properties = ""Excel 12.0 Xml;HDR=YES"""
            Case "XLS" ' Excel 2002 - 2007
                connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & XL _
                    & ";Extended Properties = ""Excel 8.0;HDR=YES"""
            Case "CSV" ' Comma Separated Values
                connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & XL _
                    & ";Extended Properties = ""Text;HDR=Yes;FMT=Delimited"""
        End Select

        'Create a standard SELECT SQL statement
        Dim selectStatement As String = _
            "SELECT APA_ID, Player_Name, SL, Team_ID, Team_No, Team_Name FROM [Roster$]"
        Dim adapter As New OleDbDataAdapter(selectStatement, connectionString)
        Dim UserData As New DataTable
        'Create a DataAdapter that will be used to populate a DataTable with data
        Try
            'The code to open a connection and execute a statement 
            adapter.Fill(UserData)
        Catch ex As InvalidOperationException
            MessageBox.Show(ex.Message)
        Catch ex As OleDbException
            MessageBox.Show(ex.Message)
        Finally
            'Any tidy up code here to release resources
        End Try
        'Display the data in the excelDataGridView
        'PlayerDataGrid.DataSource = excelData <= RETIRED
        Dim LoadData =
        (
            From xlData In UserData.AsEnumerable()
            Select F1 = xlData!APA_ID, F2 = xlData!Player_Name, F3 = xlData!SL, F4 = xlData!Team_No
         ).ToList
        'SNAPDataSet.Roster.Clear() <= Commented out until everything else is ready!
        For Each row In LoadData
            CreateRosterRecord(row.F1, row.F2, row.F3, row.F4)
        Next
    End Sub

Open in new window


Here's what CreateRosterRecord() looks like:
    Private Sub CreateRosterRecord(ByRef F1 As Integer, ByRef F2 As String, ByRef F3 As Integer, ByRef F4 As Integer)
        ' Roster Table Uses the Following Schema:
        '   0: APA_ID       4: Matches_Played
        '   1: Player_Name  5: Matches_Won
        '   2: SL           6: Player_Picture
        '   3: Teams_Key
        Try
            Dim row As DataRow = SNAPDataSet.Roster.NewRow()
            row("APA_ID") = F1
            row("Player_Name") = F2
            row("SL") = F3
            row("Teams_Key") = F4
            row("Matches_Played") = 0
            row("Matches_Won") = 0
            row("Player_Picture") = Nothing
            SNAPDataSet.Roster.Rows.Add(row)
            '            Me.RosterTableAdapter.Insert(F1, F2, F3, F4, "0", "0", Nothing)
        Catch ex As InvalidOperationException
            MessageBox.Show(ex.Message)
        Catch ex As DataException
            MessageBox.Show(ex.Message)
        Finally
            Call BindingNavigatorSave_Click(Me.DataRosterSave, Nothing)
        End Try
    End Sub

Open in new window


BindingNavigatorSave_Click just puts all the save buttons in one neat and tidy subroutine:
    Private Sub BindingNavigatorSave_Click(sender As Object, e As EventArgs) Handles _
        MyDivisionSave.Click, MyPlayersSave.Click, NotesSave.Click, DataLocationsSave.Click, DataTeamsSave.Click, _
        DataRosterSave.Click, DataScheduleSave.Click, DataMatchSave.Click, DataRackSave.Click, DataEventSave.Click, _
        DataSkillLevelSave.Click, DataTablesSave.Click, DataNotesSave.Click
        ' Save Buttons
        Dim DBS As BindingSource = DirectCast(DirectCast(sender, ToolStripButton).GetCurrentParent, BindingNavigator).BindingSource
        Me.Validate()
        DBS.EndEdit()
        Me.TableAdapterManager.UpdateAll(Me.SNAPDataSet)
    End Sub

Open in new window


Let me know if I overlooked anything.

Thanks Again,
Tony G.
0
 
HainKurtSr. System AnalystCommented:
I am a bit lost here again...
what are you trying to do with all these codes?

Dim LoadData =
        (
            From xlData In UserData.AsEnumerable()
            Select F1 = xlData!APA_ID, F2 = xlData!Player_Name, F3 = xlData!SL, F4 = xlData!Team_No
         ).ToList
        'SNAPDataSet.Roster.Clear() <= Commented out until everything else is ready!
        For Each row In LoadData
            CreateRosterRecord(row.F1, row.F2, row.F3, row.F4)
        Next

Open in new window


is not this enough?

        For Each row In UserData
            CreateRosterRecord(row("F1"), row("F2"), row("F3"), row("F4"))
        Next

Open in new window


after that, what is the question here again?
0
 
Tony GardnerSr. Programmer/AnalystAuthor Commented:
Wow, I didn't think it was that bizarre! O.K. The LINQ query was derived from an example LINQ to DataSet example in my favorite WROX book "2012 Programmer's Reference" by Rod Stephens. The primary purpose being to get from the UserData DataSet to an iEnumerable list which I could loop through and load to the Roster table. The loop successfully accomplishes my objective, so I'm pretty happy with it. Of course, if you have any constructive insights, they are VERY welcome!

I'll be working on getting the CSV portion working for the remainder of the week.

Cheers,
Tony G.
0

Join & Write a Comment

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now