Populate GridView with excel data then export to access? RRS feed

  • Question

  • Ok, so basically, I have a excel spreadsheet that has Store numbers and and access database that has Store numbers.. I have written code that lets the user open the excel file and select the column they want to move into to database (they select which column that data should be moved into). This data should go into the database table based on the store number.

    I'm not quite sure how to do the latter. I was looking around and I think I should use a GridView or something, populate two columns (one with Store numbers and one with the data to be moved).. Then let the user edit any info they want then export the data to the database selected to the table/column selected based on the store number in the gridview. I know this is possible but I can't quite wrap my brain around how I should go about it. I've spent the last day or so readin millions of white papers and examples but none seem to do close enough to what I need for me to edit the code for my purpose. Any help would be appreciated. Thanks.
    Friday, January 11, 2008 4:09 PM

All replies

  • Here's a little more to go off of.

    I tried to do this to populate the gridview. It runs without error, but the GridView stills shows blank afterwards. Anty thoughts?

        Public Sub test()
            Dim oConnect As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & OriginExcelFile & ";Extended Properties=Excel 8.0")

            Dim oSelect As New OleDb.OleDbCommand("SELECT * FROM [Sheet1$A:F];", oConnect)

            Dim oAdapter As New OleDb.OleDbDataAdapter(oSelect)

            oAdapter.Fill(dsDataSet, "Table")

            Me.DataGridView1.DataSource = dsDataSet
            Me.DataGridView1.DataMember = "Table"
        End Sub
    Friday, January 11, 2008 5:16 PM
  • i believe what you need here is this

     Me.DataGridView1.DataSource = dsDataSet.Tables("Table")

    keep in mind that Table needs to be the name of your table

    or a generic would be to use the index of the table -   dsDataSet.Table(0)    0 would be the first table or the only table if you have just one in your dataset.


    and you don't need the datamember line



    As far as the rest, i do not have a full picture of what you are wanting to do.

    Would you mind writing out step by step the whole process you want the user to be able to do?

    If you could be real specific it might help.  From what i read in your post, i think that it sounds more complicated than it really is.



    Friday, January 11, 2008 6:22 PM
  • Sorry for a response that's so late, I've been working on a different project past couple of weeks but now need to get this one done.

    -Basically, I want the user to select an excel document (this works)
    -The excel document has an unique identifier that is identical to the unique identifiers in the destination table)
    -the user selects which column in the excel doument they want to put into the database (this works)
    -user selects which table the data should be going to
    -user selects destination field in the database's table
    -I want to fill a data grid with two columns, the unique identifier and the column from the excel doc to be put into the data base table.
    -The user can then edit data in the data grid.
    -When the user is satisfied with the data to be put into the database then can push the data into the database where it goes (by the unique identifier)

    This unique identifier is a Store think of it like I have an excel document with some information on a few of the stores but NOT all of them that are in the database. So it has to compare data from the store number column in the excel document and put it int he desired field in the table on that store number for that record.

    Hope this is a better explanation.

    Thursday, January 31, 2008 10:24 PM
  • Could really use some help here please. If what I have isn't the route I should be taking, I'm fine with doing it a different way just need some guidance as to how I should
    Friday, February 1, 2008 5:06 PM
  • Hi G,


    I didn't forget about you, i just haven't been able to think straight the last few days.  I read your posts several times and i just can't get my head on straight enough to give you any good advice today.  But it might help you to take a look at my webiste.  I have over 700 code samples.  The names tell basicly what they do and possibly you may find what it is you need there.  You would ofcourse have to put several examples together to do what you want but i think it is all there.  Hopefully as you see them you will be able to put it together.  I wish i could think straight and help you more.  I have just been staring at my computer today and can't even get going on my own project.  I think my brain needs a rest.  I will try to get back to you if you don't get it straight.


    I hope you find what you need on my site


    Friday, February 1, 2008 6:09 PM
  • Thanks Jeff. I'll be looking a your website the rest of the day apparently. I've been debating whether to start this project over in ASP.NET 2 but not sure that I'd be able to perform the Excel stuff the same. The code I have now seems to kinda half least it runs with no errors and pulls the column headers correctly...however for some reason it refuses to populate my datagridview with anything.
    Friday, February 1, 2008 8:02 PM
  • I think the best place to start on this is figuring out how to populate a DataGridView with data that we pull out of Excell.

    Sounds simple, but Im lose already.
    Friday, February 1, 2008 8:14 PM
  • Ok, if you want to start there.  If you go to the website and look under database section, there is an excel download.  that one contains quite a few examples for this.  There are also examples to work with a program called spreadsheet gear.  The download for spreadsheet gear is also under database.  There are quite a few examples so it would be easier to download the folders than for me to post them here.  Take a look at them and see if they help you.  If not then post back and i can see what i can do to help more.

    Friday, February 1, 2008 8:51 PM
  • Ok, in the mean time here is the section of code rewritten. It works but there are a few nuances. First, obviously it selectsonly one row by variable and I want row 1 and the variable based row. I'm not sure how to make the select into an And statement I was thinking something like

    ("Select * from [initial$A" AND" & selecteditem & "]", ExcelConnection)

    but I'm sure the And is incorrect.

     I tried concatinating the variable into the select statement but it didn't work. Secondly, there are nulls all through it. Is there an easy way to extract those?
    Thanks again for the help

      Dim ConnectionString As String

            ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & OriginExcelFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"""

            Dim ExcelConnection As New System.Data.OleDb.OleDbConnection(ConnectionString)


            Dim da As New System.Data.OleDb.OleDbDataAdapter

            da.SelectCommand = New System.Data.OleDb.OleDbCommand("Select * from [initial$" & selecteditem & ":" & selecteditem & "]", ExcelConnection)

            Dim ds As New DataSet

            da.Fill(ds, "initial")

            Me.DataGrid.SetDataBinding(ds, "initial")

    Friday, February 1, 2008 9:23 PM
  • I didn't realize you were trying to get specific cell data.

    I wonder if it would be easier to reference the cell and get the data instead of using a select statement and a connection.


    For what it appears you want to do, automation might be just fine.  Automation is sometimes slower, especially if you have large amount of records but in this case if you just need to reference a few cell selections at a time then it should be just fine.


    You can use com to automate excel or you can use spreadsheet gear which will allow you to work with excel files without the need to start the excel process.  You can also embed spreadsheet gear workbook views into a form to view and work with the spreadsheets.  Or you can do everything in code.


    Can you tell me a little more detail how everything works and i can give some more details on the automation and spreadsheet gear so you can decide what would be best for you. 

    Friday, February 1, 2008 9:59 PM
  • I need two take two columns from the spreadsheet and all data in each column.
    One column is static, it's also the unique identifier for the table in the database
    The second column is selected by the user.

    The gridview should populate with all the data from those two columns.

    User can edit data in the form

    User can then push the data into the table. Only the data from the second column will go into the database table. It will go into the table based on the first column in the excel spread sheet Example:

    Database Table:

    UI    Comments    MoreStuff    Blah
    1      stuff                               data
    2      stuff                               data
    3      stuff                               data
    4      stuff                               data


    UI     SomeStuff    DiffStuff       VeryDiffStuff
    1       xyz              tuv              qkldn
    2       xyz              utv              qkldn
    3       xyz              abc              qkldn
    4       xyz              fgh              qkldn

    User Selects DiffStuff column from spreadsheet

    GridView in Form
    UI    DiffStuff
    1      tuv
    2      utv
    3      abc
    4      fgh

    User Selects MoreStuff as the database field int he table

    DataBase table afterwards - FINAL RESULT We're looking for.
    UI    Comments    MoreStuff    Blah
    1      stuff              tuv             data
    2      stuff              utv             data
    3      stuff              abc            data
    4      stuff               fgh            data
    Friday, February 1, 2008 10:28 PM
  • Ok,  i think this is what you are looking for


    look at the select statement

    it will select a column named id and a column named column2

    then show the data from all records for just those 2 columns

    you can follow this pattern to allow your user to select the columns by using your variables instead of the column names



    Dim opendlg As New OpenFileDialog


            opendlg.Filter = "Excel Files (*.xls)|*.xls|All Files (*.*)|*.*"

            opendlg.InitialDirectory = "c:\"


            If opendlg.ShowDialog() = Windows.Forms.DialogResult.OK Then


                Dim pathname As String = opendlg.FileName


                Dim con As System.Data.OleDb.OleDbConnection

                con = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & _

                "data source=" & pathname & ";Extended Properties=Excel 8.0") ';HDR=Yes;IMEX=1")


                Dim select_string As String = "select [id], [column2] from [Sheet1$]"

                Dim adapter As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(select_string, con)




                Dim dt As New DataTable



                Me.DataGridView1.DataSource = dt




            End If

    Friday, February 1, 2008 11:39 PM
  • I think that's exacaly what I'm looking for on this half of the project. I'm going to work on it soon and I'll let you know how it works. Thanks man!
    Saturday, February 2, 2008 1:01 AM