SQL Server - Importing and Exporting Excel Spreadsheet Data

Views: 5.236
Reading Time: 5 minutes

Hello people,
Good night!

In this post, I will talk about a very interesting feature of SQL Server that works as a major differential of the database, which is the ability to natively integrate with Excel, allowing you to query and manipulate spreadsheets through the database without having to from any other external resources.

To make this possible, you need to install the OLEDB ACE or JET drivers to integrate with Excel. To learn more about this, visit the post. SQL Server - How to Install the Microsoft.ACE.OLEDB.12.0 and Microsoft.Jet.OLEDB.4.0 Drivers.

Sample worksheet that will be used in this post:
SQL Server - How to import data from Excel datasheet to database - Example1

SQL Server - How to import data from Excel datasheet to database - Example2

Importing Excel Data into the Bank

How to Import an Excel Spreadsheet into SQL Server

The most commonly used form for sure is reading data from an Excel spreadsheet to the SQL Server database. For this, we will use OPENROWSET and ACE OLEDB 12.0 driver, since my operating system and my bank are in 64 bits version and using an Office 2016 spreadsheet (JET plugin supports up to 2003 version).

The basic command for reading is like this:

Example:
SQL Server - How to import data from Excel datasheet to database - Import

Another way, which makes this integration much easier, is to create a Stored Procedure to make the data easier to use:

And then you can use it like this:
SQL Server - How to import data from Excel datasheet to database - Import2

You can return only specific columns as well:
SQL Server - How to import Excel spreadsheet to database4

Or import the result into a database table:
SQL Server - How to import data from Excel datasheet to database - Import3

Exporting Bank Data to Excel

How to enter / export data from SQL Server to Excel

Quite simply, you can also enter data from our database into an Excel spreadsheet. The basic syntax is as follows:

Insert Example:
SQL Server - How to insert export data from database to Excel spreadsheet

Query example proving that the record was entered:
SQL Server - How to insert export data from database to Excel spreadsheet2

Inserting from a table:
SQL Server - How to insert export data from database to Excel spreadsheet3

Once again, we can use a Stored Procedure to facilitate day-to-day operations:

Example of using procedure:
SQL Server - How to insert export data from database to Excel spreadsheet4

Updating Excel Data by Bank

How to update data from an Excel spreadsheet through SQL Server

Like other operations, you can also update data from an Excel spreadsheet through SQL Server. The basic syntax is this:

Example:
SQL Server - How to update data in Excel spreadsheet from database

Example with JOIN using local database tables:
SQL Server - How to update data in Excel spreadsheet from database2

Stored Procedure to make everyday life easier:

FncQuebra_Text function required to use SP:

SP Usage:
SQL Server - How to update data in Excel spreadsheet from database3

Unfortunately, erasing Excel spreadsheet data from SQL Server is not possible. If you try, you will encounter this error message:

OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “Deleting data in a linked table is not supported by this ISAM.”.
Msg 7345, Level 16, State 1, Line 1

That's it folks!
A hug and thanks for visiting.

sql server import read export update data from excel spreadsheet to bank import read export data spreadsheet datasheet

sql server import read export update data from excel spreadsheet to bank import read export data spreadsheet datasheet