Results 1 to 18 of 18

Thread: [RESOLVED] Inserting records into database table from Excel worksheet

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Resolved [RESOLVED] Inserting records into database table from Excel worksheet

    I need some advice on a problem trying to insert into an Access 2016 database from an Excel 2016 worksheet. My application imports a few (not all) of the columns from the worksheet into the database table.

    This code executes w/o raising any errors, but does not result in any records being inserted into the database table:

    Code:
                                lngStart = timerProcedure.ElapsedMilliseconds
                                frm.tsslMessage.Text = "Inserting prices from worksheet into database, please wait..."
                                frm.ssMain.Refresh()
                                strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ofd.FileName.ToString & ";Extended Properties=""Excel 12.0;HDR=Yes"""
                                strSQL = "INSERT INTO [MS Access;Database=" & My.Settings.myDataSource & "].[Asset Prices] SELECT " & lngAssetID & " AS AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, CalcAdjClose AS AdjClose FROM [" & xlsWS.Name & "$]"
                                Using myCon As New OleDb.OleDbConnection(strProvider)
                                    Using myCmd As New OleDb.OleDbCommand(strSQL, myCon)
                                        myCon.Open()
                                        intResult = cmd.ExecuteNonQuery()
                                        myCon.Close()
                                        Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., database I/O)
                                        If _DEBUG Then
                                            Debug.Print("Insert " & Format(intResult, "#,##0") & " prices rows into database: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                        End If
                                    End Using '...myCmd
                                End Using '...myCon
    I know how to insert from Excel into the database table via a pair of data adapters. For example, this code works:

    Code:
                                '...load the prices rows of the worksheet into the dtTemp datatable
                                lngStart = timerProcedure.ElapsedMilliseconds
                                frm.tsslMessage.Text = "Loading temporary prices table from worksheet, please wait..."
                                frm.ssMain.Refresh()
                                strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ofd.FileName.ToString & ";Extended Properties=""Excel 12.0;HDR=Yes"""
                                strSQL = "SELECT " & lngAssetID & " AS AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, CalcAdjClose AS AdjClose FROM [" & xlsWS.Name & "$]"
                                Using myCon As New OleDb.OleDbConnection(strProvider)
                                    Using myCmd As New OleDb.OleDbCommand(strSQL)
                                        Using myAdp As New OleDb.OleDbDataAdapter(strSQL, myCon)
                                            dtTemp.Reset()  '...resets the DataTable to its original state (removes all data, indexes, relations, and columns)
                                            myAdp.Fill(dtTemp)
                                            For Each row As DataRow In dtTemp.Rows
                                                row.SetAdded()  '...necessary for the myAdp.Update(dtTemp)
                                            Next
                                            If _DEBUG Then
                                                Debug.Print("Load " & Format(dtTemp.Rows.Count, "#,##0") & " prices rows into datatable: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                                If dtTemp.Rows.Count > 0 Then
                                                    Debug.WriteLine("Rows = {0}, Columns = {1}", CStr(dtTemp.Rows.Count), CStr(dtTemp.Columns.Count))
                                                    For iRow As Integer = 0 To 9
                                                        Debug.Print("Row #" & iRow)
                                                        For iCol As Integer = 0 To dtTemp.Columns.Count - 1
                                                            Debug.WriteLine(vbTab & "{0} = {1}", dtTemp.Columns(iCol).ColumnName, dtTemp.Rows(iRow).Item(iCol).ToString)
                                                        Next iCol
                                                    Next iRow
                                                End If '...dtTemp.Rows.Count > 0
                                            End If '..._DEBUG
                                        End Using '...myAdp
                                    End Using '...myCmd
                                End Using '...myCon
    
                                '...save the prices rows of the dtTemp datatable to the Asset Prices table
                                lngStart = timerProcedure.ElapsedMilliseconds
                                frm.tsslMessage.Text = "Saving prices to database, please wait..."
                                frm.ssMain.Refresh()
                                If dtTemp.Rows.Count > 0 Then
                                    lngStart = timerProcedure.ElapsedMilliseconds
                                    Using myAdp As New OleDb.OleDbDataAdapter
                                        strSQL = "INSERT INTO [Asset Prices] (AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, AdjClose) VALUES (@AssetID, @RecDate, @OpenPrice, @HighPrice, @LowPrice, @ClosePrice, @DayVol, @AdjClose)"
                                        myAdp.InsertCommand = New OleDb.OleDbCommand(strSQL, con)
                                        myAdp.InsertCommand.Parameters.Add("@AssetID", OleDb.OleDbType.BigInt).SourceColumn = "AssetID"
                                        myAdp.InsertCommand.Parameters.Add("@RecDate", OleDb.OleDbType.Date).SourceColumn = "RecDate"
                                        myAdp.InsertCommand.Parameters.Add("@OpenPrice", OleDb.OleDbType.Double).SourceColumn = "OpenPrice"
                                        myAdp.InsertCommand.Parameters.Add("@HighPrice", OleDb.OleDbType.Double).SourceColumn = "HighPrice"
                                        myAdp.InsertCommand.Parameters.Add("@LowPrice", OleDb.OleDbType.Double).SourceColumn = "LowPrice"
                                        myAdp.InsertCommand.Parameters.Add("@ClosePrice", OleDb.OleDbType.Double).SourceColumn = "ClosePrice"
                                        myAdp.InsertCommand.Parameters.Add("@DayVol", OleDb.OleDbType.BigInt).SourceColumn = "DayVol"
                                        myAdp.InsertCommand.Parameters.Add("@AdjClose", OleDb.OleDbType.Double).SourceColumn = "AdjClose"
                                        myAdp.Update(dtTemp)
                                        Application.DoEvents() '...allow the OS to complete any pending tasks (e.g., database I/O)
                                        If _DEBUG Then
                                            Debug.Print("Save " & Format(dtTemp.Rows.Count, "#,##0") & " prices records to database: " & Format(timerProcedure.ElapsedMilliseconds - lngStart, "#,##0") & " milliseconds")
                                        End If
                                        lngPriceCount = dtTemp.Rows.Count
                                    End Using '...myAdp
                                End If '...dtTemp.Rows.Count > 0
    In other projects, I import from a CSV text file into the database without having to use the pair of data adapters approach. For example:

    Code:
                strSQL = "INSERT INTO [Asset Prices] ( AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, AdjClose ) " & _
                    "SELECT AssetID, [Date], Open, High, Low, Close, Volume, AdjClose " & _
                    "FROM [Text;Database=" & strFolder & ";HDR=yes].[" & strFile & "]"
    I am wanting to avoid the overhead of creating 2 data adapters and a temporary data table, and instead would like to do a single INSERT INTO from the worksheet to the database (like I can do from a CSV text file to the database).

    Any suggestions would be most appreciated.

    Thanks.
    Last edited by Mark@SF; Jun 28th, 2017 at 02:09 PM.

  2. #2
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Inserting records into database table from Excel worksheet

    You can delete these two lines:

    myCon.Close()
    Application.DoEvents()\

    Since you are rightly making use of the Using construct for the connection, you don't need to worry about closing it...and probably shouldn't. The End Using will clean up after you and do it safely, even if an exception is thrown. As for the DoEvents, that would possibly matter if you were doing things in other threads, but you don't appear to be, so it costs you a bit and gains you nothing. There DB I/O stuff is happening synchronously, so it will have already happened by the time the DoEvents executes, rendering the DoEvents useless.

    I also see that you are getting the return from ExecuteNonScalar, which is the key piece you need. What is that showing you? If it is 0, then the INSERT isn't doing anything, so that's the problem. If it's greater than 0, then the INSERT is doing what it is supposed to...and life will get a bit weirder. So, which is it?
    My usual boring signature: Nothing

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Inserting records into database table from Excel worksheet

    Hi Shaggy Hiker,

    The ExecuteNonScalar returns 0.

    Thanks for helping to look into this.

  4. #4
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Inserting records into database table from Excel worksheet

    I don't see ExecuteNonScalar, I see ExecuteNonQuery

    Also, enclose the code in a Try/Catch block, maybe it will catch the error.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Inserting records into database table from Excel worksheet

    wes4dbt -

    You are correct, my mistake from responding too quickly to Shaggy Hiker. The code uses ExecuteNonQuery, not ExecuteNonScalar.

    I have a Try/Catch block around the entire procedure. As per your suggestion, I added another Try/Catch block around the Cmd.ExecuteNonQuery statement.

    No error is raised.
    Last edited by Mark@SF; Jun 28th, 2017 at 04:12 PM.

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Inserting records into database table from Excel worksheet

    How can I "see" into the execution of the INSERT INTO query? It's a "black box" to me, but the query syntax must be OK since there are no errors raised.

    Code:
    strSQL = "INSERT INTO [MS Access;Database=" & My.Settings.myDataSource & "].[Asset Prices] SELECT " & lngAssetID & " AS AssetID, RecDate, OpenPrice, HighPrice, LowPrice, ClosePrice, DayVol, CalcAdjClose AS AdjClose FROM [" & xlsWS.Name & "$]"
    I have seen a number of examples on various websites showing code that imports all the columns from the worksheet ("...] SELECT * FROM [..."), but I'm only interested in a few of the columns, not all of them. I'm assuming that I can do the select as shown in the code snippet above because (1) that is how I've done it when importing from a CSV text file to the database, and (2) no errors are raised in the first code snippet shown in my initial post.

    I've studied this for so long that I think my eyeballs are starting to bleed! Hopefully, someone has had more luck with this than I have and can point me in the right direction...
    Last edited by Mark@SF; Jun 28th, 2017 at 04:20 PM.

  7. #7
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: Inserting records into database table from Excel worksheet

    Sorry, I conflated ExecuteScalar and ExecuteNonQuery. Fortunately, Mark knew what I was talking about and answered...with the same mistake I made. Still, it's the easier answer. If you are getting 0 back, then the INSERT query is failing, but it IS trying. An exception would not have let you get far enough to see the 0. I do like wrapping all DB stuff in exception handling, because you can get exceptional situations when working with a DB (like inopportune network interruptions, and so forth), but in this case, I don't think you'll catch anything.

    So, what's wrong with the INSERT statement? That I'm not so clear on. I haven't written an INSERT INTO SELECT statement before, but what you have certainly looks right. I'm not sure that you need all that decoration on the destination table. After all, your connection string is all about that stuff, so you should be able to just get away with the table name and nothing else.
    My usual boring signature: Nothing

  8. #8
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: Inserting records into database table from Excel worksheet

    you can insert selected columns from the worksheet. I was curious about your approach so I gave it a try.

    This is a simple one column insert but it works, so your concept is sound.
    Code:
            Dim strProvider As String, strSQL As String, intResult As Integer
            strProvider = "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=c:\ajunkProject\Book3.xlsx;Extended Properties=""Excel 12.0;HDR=Yes"""
            strSQL = "INSERT INTO [MS Access;Database=c:\ajunkproject\water.accdb].[groupsInsert] SELECT groupid FROM [sheet1$]"
            Using myCon As New OleDb.OleDbConnection(strProvider)
                Using myCmd As New OleDb.OleDbCommand(strSQL, myCon)
                    myCon.Open()
                    intResult = myCmd.ExecuteNonQuery()
                End Using '...myCmd
            End Using '...myCon
            MessageBox.Show("finish")
    One thing I did notice is your excuting "cmd", cmd.ExecuteNonQuery() I think you should be executing myCmd.

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Inserting records into database table from Excel worksheet

    Not sure what you mean here...

    Quote Originally Posted by Shaggy Hiker View Post
    I'm not sure that you need all that decoration on the destination table.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: Inserting records into database table from Excel worksheet

    wes4dbt -

    Bingo!! You found the problem. Making this change solved the problem:

    Code:
    intResult = myCmd.ExecuteNonQuery()
    THANK YOU SO MUCH!

    I'll mark this thread as "Resolved".

  11. #11
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    Totally did not see that.

    How did the code compile? cmd must be a CommandObject that was in scope for the code to run at all. Are you mixing commands created in Using constructs and commands created not in Using constructs?
    My usual boring signature: Nothing

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    Shaggy Hiker -

    Good question. The procedure that uses this code also declares the "cmd" OleDb.OleDbCommand object for use with the Access database connection string.

    When you connect to an Excel workbook, you have to use a different connection string than when connecting to an Access database (different data providers, etc.).

    Here's the connection string for the workbook (that I use with the myCmd OleDb.OleDbCommand object):

    Code:
    "Provider=Microsoft.Ace.OLEDB.12.0;Data Source=" & ofd.FileName.ToString & ";Extended Properties=""Excel 12.0;HDR=Yes"""
    Here's the connection string for the database (that I use with the cmd OleDb.OleDbCommand object):

    Code:
    "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dataSource & "';Persist Security Info=True;Jet OLEDB:Database Password="
    Because of this, I use 2 OleDb.OleDbCommand objects.

    As I said, my eyeballs were bleeding from staring and the code for so long and I just missed where I had mixed up the two OleDb.OleDbCommand objects.

    Thanks for all your help with my code. I really, really appreciate it!
    Last edited by Mark@SF; Jun 28th, 2017 at 05:26 PM.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    A final note...

    When I began looking into the technique of using a single INSERT INTO code block vs. using a pair of data adapters and a data table, I originally thought that the first approach (single code block) would be faster than the second approach (data adapter pair) simply because it appeared to be less complex and (I thought) would use less overhead resources.

    In fact, after getting the problem of the mixed up OleDb.OleDbCommand objects resolved and then benchmarking each technique (by importing 8,366 rows from a workbook to a database table), I discovered that the second approach was about 30% faster than the first approach. The single code block's INSERT INTO technique took about 30 seconds to complete whereas using a pair of data adapters only took about 20 seconds to complete. These performance numbers are on a machine having an IntelĀ® I7 5930K CPU running at 3.7GHz (with 6 cores and 12 threads) and 32 GB of RAM. Your mileage may vary...

  14. #14
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    5,196

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    Interesting, I would have thought the first way was faster also. I've done the same thing, spend hours trying something new because you think it will be an improvement, and it just makes things worse.

  15. #15
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    That's a really interesting post. Are you using the code that wes4dbt showed in #8, or your original code? If you are using your original code, did you remove the DoEvents? That's a slow method anyways, though it should only add a few ms at most.

    You might use a Stopwatch object to time different pieces and figure out where the cost is coming from. I'm surprised that it is so much slower, too (or any slower, actually), but I don't know what it is doing internally. It suggests that there is a real cost to reading each row when combined with an INSERT.
    My usual boring signature: Nothing

  16. #16

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    Shaggy Hiker -

    The code that I benchmarked has the DoEvents statements, and they are in both techniques (single INSERT INTO code block and the pair of data adapters code blocks) so I would think the effect would be equalized.

    I used a Stopwatch timer to get the benchmark data.

    If you look at the code block that has the first data adapter, which loads the contents of the worksheet into a DataTable, you can see that there is a For...Each block for each row of the DataTable that calls the row.SetAdded() method. Even with this iteration across almost 8000 rows, the pair of data adapters technique is faster than the single INSERT INTO technique.

    The row.SetAdded() method is necessary because the scope of the adapter.Update() is for added, modified, or deleted rows of the DataTable. The myAdp.Fill(dtTemp) statement fills the DataTable, but does not set the rows' RowState to Added. I thought this odd (and it caught me by surprise when my adapter.Update() at first didn't update the database tables until I added the loop with the SetAdded statement). If you could fill the DataTable *and* also set the RowState.Added property of each row at the same time (without having to do an additional For...Each loop across all the rows), then I think the pair of data adapters technique would be even faster.

    I guess this confirms that there is "no free lunch". Even though the single code block technique (INSERT INTO) has less lines of code and looks simpler than the double code blocks technique (pair of data adapters and data table), it apparently is more resource intense or has more overhead and is considerbly slower in the final analysis.
    Last edited by Mark@SF; Jun 29th, 2017 at 08:06 PM.

  17. #17

    Thread Starter
    Hyperactive Member
    Join Date
    Mar 2013
    Location
    San Francisco, CA
    Posts
    487

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    I took a second look at the data adapter class on MSDN and noticed that it's Fill method has a AcceptChangesDuringFill property that is by default set to True. So I modified my code to set this property to False before calling the Fill() method.

    Code:
    Using myCon As New OleDb.OleDbConnection(strProvider)
        Using myAdp As New OleDb.OleDbDataAdapter(strSQL, myCon)
            myAdp.AcceptChangesDuringFill = False
            myAdp.Fill(dtTemp)
        End Using '...myAdp
    End Using '...myCon
    With this change, the pair of data adapters technique now executes in 14 seconds, instead of the 20 seconds that I reported earlier. This means that the pair of data adapters technique is about 50% faster than the single block of code (INSERT INTO) technique.
    Last edited by Mark@SF; Jun 29th, 2017 at 10:25 PM.

  18. #18
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,989

    Re: [RESOLVED] Inserting records into database table from Excel worksheet

    With that property cleared, the RowState for all rows would be Added, which should make it a bit quicker to decide to perform an insert.

    I'm still surprised that it is faster than the INSERT SELECT. It acts like it is opening and closing connections for each record.
    My usual boring signature: Nothing

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width