VBA: Import table from Excel into SQL

3
votes

I am trying to import a data table from excel into SQL Server 2012, using VBA.
Preferably with help of a UDF.

The excel table looks something like this.

TableID    2012  2011  2010  2009
row 1       11     12   13    14
row 2       21     22   23    24
row 3       31     32   33    34
etc..

(I placed the numbers in the cells to designated their position. For example 11 = row 1, column 1)

The database table looks something like this.

Header:        id   |  year  |  row1  |  row2  |  row3  |  etc..
ExampData:  TableId    2012      11       21       31       ..
ExampData:  TableId    2011      12       22       32       ..

(This doesn't include the Primary Key column, which can be either a combination of id and year, or a NEWID())

I know how to import specific columns, for example I could run the following code:

INSERT INTO example_table (id, year, row1, row2, row3) VALUES ('001', '2012', '11', '21', '31')

This works great for individual columns, but how would I go about making it work for the entire table (multiple columns and rows).

2
sqlexcelvbaadodb
Is this a one time or infrequently run operation? If so, then you don't have to worry too much about efficiency. That is unless there are lots of rows to import. Why are you trying to improve the efficiency? – Nick DeVore
It will be on a regular basis, daily - multiple tables a day. – Ben Z.

2 Answers

3
votes

So I managed to get it working. This is using Excel 2010, SQL Sever 2012.
This assumes the row titles in excel are the same as the column headings in the sql.
This also assumes a similair layout of tables as in the question post.

To import data from Excel into the SQL server, using UDF we can do the following.
Create a Public Function in excel:

Public Function import_data(TableID, vHeader As Variant, vRow As Variant, vData As Variant)
End Function

And use your favorite connection method to connect to the database.
Then for the INSERT statement use the following:

'Declare the  variables
Dim vpush As String
Dim headerCount As Long
Dim rowTitles As String
Dim rowDatas As String
Dim i As Long

`Count the amount of columns 
headerCount = Application.CountA(vHeader)

`Create insert statement
For i = 1 To headerCount
   rowTitles = VBA.Join(WorksheetFunction.Transpose(vRow), ", ")
   rowDatas = VBA.Join(WorksheetFunction.Transpose(Application.Index(vData, , i)), "', '")
   vpush = "INSERT INTO example_table (id, " & rowTitles & ", year) VALUES ('" & TableID & "', '" & rowDatas & "', '" & vHeader(i) & "')"
Next i

Don't forget to .Execute (vpush).

Than anytime you want to import a table, you will do the following in an excel worksheet:

  1. In a cell type =import_data( and press CTRL + SHIFT + A
  2. Select TableId
  3. Select the heading row (assumed always to be years here)
  4. Select column with all row titles (which will be the heading titles in your sql)
  5. Select all data in table

That should do it.
(P.S. If this is not the most efficient way, or you see improvements.. feel free to edit or add)

2
votes

If you're using SQL Server 2008 or later, you can put multiple rows in your INSERT statement:

INSERT INTO example_table (id, year, row1, row2, row3)
VALUES ('001', '2012', '11', '21', '31'),
   ('002', '2012', '12', '22', '32'),
   ('003', '2012', '13', '23', '33')

Depending upon how large your spreadsheet is, you can do it in one large INSERT, or a few smaller ones.