Easily Import and Export Excel Data to SQL Server
Importing Data from MSSQL Server
Over the years Microsoft has greatly improved how Excel integrates with other databases, including of course, Microsoft SQL Server. Each version has seen many improvements in ease of functionality to the point where extracted data from many sources is as easy as it gets.
In this example, we will extract data from a SQL Server (2016) but this will equally well with other versions. Follow these steps to extract data:
From the Data tab click on the Get Data drop-down menu as shown in figure-1 below and select the From Database section and finally From SQL Server Database which will the display of an input panel to enter the server, database and credentials.
The SQL Server database connection and query interface shown in figure-2 allows us to enter the name of the server and optionally the database where the data we need is stored. If you don’t specify the database, in the next step you will still need to select a database, so I highly recommend that you enter a database here to save yourself the extra steps. Either way, you will need to specify a database.
Or, write a query by click on the Advanced options to expand the custom query section which is shown in figure-3 below. Although the query field is basic, meaning that you should use SSMS or another query editor to prepare your query if it is modestly complex or if you need to test it before using it here, you can paste in any valid T-SQL query that returns a result set. This means that you can use this for INSERT, UPDATE or DELETE SQL operations.
-
A couple of additional information regarding the three options under the query field. These are “Include relationship columns”, “Navigate full hierarchy” and “Enable SQL Server failover support”. Of the three I find the first one the most useful and is always enabled by default.
- The “Include relationship columns” option will tell SQL Server to automatically include any columns in another table that has a relationship with any of the columns in the tables that are included in the query.
- Navigate full hierarchy expands the tree view of the tables and columns that are included in the query
iii. Enable SQL Server failover support ensures that, in case, the server connection fails or if there are too many connections on one server, you the connection will automatically switch to another connection/server in the cluster. This option is only useful if you have a SQL Server farm that is configured as a cluster with failover.
Export Data to Microsoft SQL Server
While it is very easy to extract data from a database like MSSQL, uploading that data is a bit more complicated. To upload to MSSQL or any other database, you either need to use VBA, JavaScript (2016 or Office365), or use an external language or script. The easiest in my opinion is to use VBA as it is self-contained in Excel.
Basically, you need to connect to a database, assuming of course you have “write” (insert) permission on the database and the table, then
- Write an insert query that will upload each row in your dataset (it is easier to define an Excel Table – not a DataTable).
- Name the table in Excel
- Attach the VBA function to a button, or macro
Enable Developer Mode
Next, open the VBA editor from the Developer tab to add VBA code to select the dataset and upload to SQL Server.
Sub UploadToDatabase() Dim connection As ADODB.connection Dim command As ADODB.command Dim query As String Dim xlSheet As Worksheet Dim recordset As ADODB.recordset Set xlSheet = ActiveSheet 'If you are using username and password (not your Windows login) ' connection.Open "Provider=SQLOLEDB;" & _ ' "Data Source=The_Name_of_your_Server;" & _ ' "Initial Catalog= Autzen2200;" & _ ' "User ID=user1; Password=pass1" 'or 'If you are using Windows login connection.Open "Provider=SQLOLEDB;" & _ "Data Source=The_Name_of_your_Server;" & _ "Initial Catalog= Autzen2200;" & _ "Integrated Security=SSPI;" query = "INSERT INTO your_SQL_table_name " & _ "SELECT * from your_excel_table_name " If connection.State = adStateOpen Then command.CommandType = adCmdText command.CommandText = query command.ActiveConnection = connection ' Execute once and display... 'Set recordset = command.Execute ' OR with no result set command.Execute End If recordset.Close connection.Close Set connection = Nothing Set command = Nothing Set recordset = Nothing End Sub
Note:
Query = “INSERT INTO your_SQL_table_name” & _ “SELECT * from your_excel_table_name ”
Using this method, while easy, assumes that all the columns (count and names) match the number of columns in your database table and have the same names. Otherwise you will need to list the specific column names, like:
Query = “INSERT INTO your_SQL_table_name (column1, column2, column3, etc.)” & _
“SELECT column1, column2, column3 from your_excel_table_name”
If the table doesn’t exist, you can export the data and create the table using one simple query as the following:
Query = “SELECT * INTO your_new_table FROM excel_table_name”
Or
Query = “SELECT col1, col2, col3 INTO your_new_table FROM excel_table_name”
The first way, you create a column for every column in the excel table. The second option allows you to select all the columns by name or a subset of the columns from the Excel table.
These techniques are the very basic way to import and export data to Excel. Creating tables can get more complicated if you can to add primary keys, indexes, constraints, triggers and so on but is another subject.
This design pattern can be used for other databases as well like MySQL or Oracle. You would just need to change the driver for the appropriate database.
This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.
© 2019 Kevin Languedoc
Related
Comments
AUTHORCan you share your code so I can have a better idea what you are doing. Also, did you define table in Excel?
I am trying to export data from excel to SQL server with your code.
However, I am getting error on command.execute line. Error shown is "invalid object name KKKMMM" where KKKMMM is table name in my active worksheet. pl help.
