Tutorial: How to automate importing data from Excel into a database using a macro (VBA)

  1. Use the Import and Export Data Wizard if there’s a lot of data

The Setup

Start with creating an Excel Macro. From the “Developer” tab in the ribbon (it may not be there by default; you can add it in from the Options menu), click the button for “View Code”.

Public Function ImportData()End

Concept Overview

We’ll be running an INSERT statement, where the SQL follow will follow this format:

INSERT INTO myTableName 
(field1, field2)
VALUES
(row1Value1, row1Value2),
(row2Value1, row2Value2)
  • Dynamically generated “values” that is a concatenation of the individual rows of data within the spreadsheet

Let’s write some code!

First we need to create a connection string. I’ll leave the exact value up to you. You’ll of course have to be sure that the user has the appropriate database security permissions. The example below is using Active Directory — so it’s detecting my Windows login and not using a local SQL user account.

Const strDbConn As String = "server=myServerName;Database=myDatabaseName;Trusted_Connection=Yes;Driver={ODBC Driver 17 for SQL Server}"

ADODB Error?

Inserting data with SQL: the header

We need to create the beginning part of the SQL statement, which will be static (i.e. not changing).

Inserting data with SQL: the actual data

We need a way to dynamically generate the values that will be inserted into the database. Before we create the SQL, we need a way to loop through the rows in Excel, from the top to the bottom. The first step in that is to determine how many rows we have.

  • Create a variable that holds the beginning of the SQL statement
  • Loop through each row in the dataset, creating the INSERT … VALUES SQL statement
  • After each row of data, add in a comma
  • Remove the last comma in the statement
  • Run the SQL

How to overcome a data import limitation

I ran into a problem of, sometimes the concatenated “values” got too long, and it crashed Excel. I figured I should limit the script to import in 500 rows at a time.

Putting it together

To make this even more useful, we may need to first remove any existing data.

  • Runs the function that imports the data
  • Runs an update query to indicate when the data was updated

The Entire Code

Bonus: remove blank values with VBA Code

I wrote a separate article on how you can potentially speed up the import process: removing some rows that do not add value (literally and figuratively). This could potentially significantly reduce the number of rows, thereby reducing the processing time!

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store