OpenRowSetExcelArrowSQL

My grandparents had a small farm in Iowa . We only lived a few miles away and my brothers and I spent a great deal of time at the farm with our grandparents when we weren’t in school.

I traded the rolling corn fields of Iowa for the rusty-reddish brown soil of Arizona many years ago, but I frequently think about my grandparents and working around the farm with the crops, the garden, the animals and the machinery.

However, today’s post is not about feeding the chickens, or shucking the corn – it is about inserting data from Excel Worksheets to SQL Tables using the T-SQL Function OpenRowSet.


SQLServer

You’ll need SQL Server installed for this to work. I’m using SQL Server 2012 Developer on my laptop, but you could also use SQL Server Express which is free but limited in some of its functionality.

You can pick up a Developer copy of SQL Server 2014 Developer Edition from CDW. It will cost you ~ US $50. You can download SQL Server Express here. It’s free! However, you will need the Developer Edition if you want to do any testing with SSAS Tabular or Multidimensional.


Security300

You’ll need to change the default security for the SQL Server Instance you want to use the OpenRowSet Function on. I’ll use Windows Authentication instead of SQL Server Authentication.

Services1

  • Close SQL Management Studio if it is open.
  • Launch Services.msc. I’m using Windows 8.1 so I went to Search, typed in services and selected View Local Services
  • Jump to SQL Server by typing “S” and then scroll as needed until you find the instance of SQL Server that you want to change security settings.

ServicesClickStopFinal

  • Right-click on the Service and click on Stop.

ServicesClickProperties

  • Right-click on the Service and click on Properties.

PropertiesDialog

  • In the Properties Dialog, Click on the Logon Tab, enter your Windows Login information, Click OK.
  • Right-click on the Service and click on Start.

Settings

Depending on your system configuration, you may need to download and install the correct data connectivity components if you are using Excel 15.0 file formats (.xlsx etc…)

InsertFinal

I’ll use a SQL INSERT statement with the OpenRowSet Function to insert data from an Excel Worksheet to a table in my database

DataSample

Here is the Excel data source, so I’ll need to create a Table in my SQL Database with appropriate fields and data types to receive the data.

NewDatabase

I added a new database “Tecumseh” to my default instance of SQL Server. Now I can add a Table to the Database.

use tecumseh

CREATE TABLE scores
(
Reps nvarchar(50),
TrxDate date,
Region nvarchar(50),
Score float(53),
);

ScoresTableCreatedFinal

The Scores table with the required fields has been added to the Tecumseh database. Next, I’ll write some SQL to insert the data from the Scores.xlsx Workbook to the Scores table. Make sure the Excel file is closed or you will receive file exclusivity warnings.

USE tecumseh

INSERT INTO dbo.scores
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
	        'Excel 12.0 Xml;HDR=YES;Database=C:\data\scores.xlsx',
		'SELECT * FROM [sheet1$]');

The last step is to check that what is in the SQL Table matches what is in the Excel Workbook.

SQLResults

ExcelRows

Both The SQL Table and the Excel Workbook have 590 records! Sweet success!

Final Thoughts

That’s it for today. OpenRowSet and other functions (BULK INSERT) can be used in place of developing Integration Packages (SSIS). I receive many disconnected and disjointed files from a variety of sources each week/month. I prefer to clean everything up and move it to a database such as SQL or MS Access. Your thoughts?

, , , , ,