[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.

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?

Hi Shaggy Hiker,

The ExecuteNonScalar returns 0.

Thanks for helping to look into this.

I don't see ExecuteNonScalar, I see ExecuteNonQuery

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

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.

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...

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.

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.

Not sure what you mean here...

Quote:

---

Originally Posted by Shaggy Hiker

I'm not sure that you need all that decoration on the destination table.

---

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".

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?

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!

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...

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. :(:cool::p

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.

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.

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.

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.