Exporting Excel sheet to SQL Server table using VBA Recordset

0
votes

I am trying to export 98 columns, ~ 200000 rows to SQL server table from Excel sheet. I am following code from this link https://www.excel-sql-server.com/excel-sql-server-import-export-using-vba.htm#Introduction

It worked fine when i had less data, but it takes time for large data.

I tried bulk insert, insert into string formation methods, but not able to get speed. can you please share your thoughts on ways to improve performance when export from excel to SQL server using VBA

2
sql-serverexcelvbaado

2 Answers

0
votes

You can create a XML, send it to SQL stored procedure and there decode it and insert.

It can be super simple XML - no need for a parser. Here is a sample of code I'm using. But not sure what you mean by takes time. This I use every day to move about 5k rows and it takes few seconds - it was never checked or optimized for speed.

Simple XML from VBA:

For i = 1 To RowsCount
    xml = xml & "<ROW><COLUMN1>" & Range("A1").Offset(i, 0).Value & "</COLUMN1><COLUMN2>" & Range("B1").Offset(i, 0).Value & "</COLUMN2></ROW>"
Next i

xml = "<DATA>" & xml & "</DATA>"

Then, send it to stored procedure, decode and insert:

insert into MyTable
SELECT * FROM OPENXML(@handle, '/DATA/ROW', 2) WITH 
    ([COLUMN1] [nvarchar](12), [COLUMN2] [int])
0
votes

It sounds like you are looping through all records, and by it's very nature, that process will be slow. Also, Excel is relatively slow, especially when compared to SQL Server. Maybe you can convert your job to SQL, and run the SQL job. Below is a sample script, which uses the Where clause. Just change it to suit your needs (i.e., maybe you don't need to use a Where clause).

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DB_Name;Data Source=Server_Name"


'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub