SQL Server - Importing and Exporting Excel Spreadsheet Data

Views: 8.960 views

In this article

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:

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:

SELECT * 
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)

SELECT *

FROM OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

)

Example:

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

CREATE PROCEDURE [dbo].[stpImporta_Excel](
    @Caminho VARCHAR(5000), 
    @Aba VARCHAR(200), 
    @Colunas VARCHAR(5000)
)
AS
BEGIN

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'SELECT * from OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') A'

    EXEC(@Exec)
 
END

CREATE PROCEDURE [dbo].[stpImporta_Excel](

@Caminho VARCHAR(5000),

@Aba VARCHAR(200),

@Colunas VARCHAR(5000)

)

BEGIN

DECLARE @Exec VARCHAR(MAX)

SET @Exec = 'SELECT * from OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='

+ @Caminho

+ ';'', ''SELECT '

+ @Colunas

+ ' FROM ['

+ @Aba

+ '$]'') A'

EXEC(@Exec)

END

And then you can use it like this:

You can return only specific columns as well:

Or import the result into a database table:

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 INTO 
OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
)
SELECT 'Paulo', 32, 2584.44

INSERT INTO

OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

)

SELECT 'Paulo', 32, 2584.44

Insert Example:

Query example proving that the record was entered:

Inserting from a table:

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

CREATE PROCEDURE [dbo].[stpInsere_em_Excel](
    @Caminho VARCHAR(MAX), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas varchar(MAX)
)
AS
BEGIN

    IF (@Colunas = '*')
    BEGIN
    
        SELECT 
            @Colunas = isnull(nullif(@Colunas,'*') + ',','') + b.name
        FROM 
            sysobjects a WITH(NOLOCK)
            JOIN syscolumns b WITH(NOLOCK) ON a.id = b.id
        WHERE 
            a.xtype = 'U'
            AND a.name = @Tabela

    END		
    

    DECLARE @Exec VARCHAR(MAX)

    SET @Exec = 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''SELECT '
        + @Colunas
        + ' FROM ['
        + @Aba
        + '$]'') '
        + 'SELECT '
        + @Colunas
        + ' FROM '
        + @Tabela

    EXEC(@Exec)
 
END

CREATE PROCEDURE [dbo].[stpInsere_em_Excel](

@Caminho VARCHAR(MAX),

@Aba varchar(200),

@Tabela varchar(200),

@Colunas varchar(MAX)

)

BEGIN

IF (@Colunas = '*')

BEGIN

SELECT

@Colunas = isnull(nullif(@Colunas,'*') + ',','') + b.name

FROM

sysobjects a WITH(NOLOCK)

JOIN syscolumns b WITH(NOLOCK) ON a.id = b.id

WHERE

a.xtype = 'U'

AND a.name = @Tabela

END

DECLARE @Exec VARCHAR(MAX)

SET @Exec = 'INSERT INTO OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='

+ @Caminho

+ ';'', ''SELECT '

+ @Colunas

+ ' FROM ['

+ @Aba

+ '$]'') '

+ 'SELECT '

+ @Colunas

+ ' FROM '

+ @Tabela

EXEC(@Exec)

END

Example of using procedure:

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:

UPDATE A
SET A.Nome = 'Teste 1'
FROM OPENROWSET (
    'Microsoft.ACE.OLEDB.12.0', 
    'Excel 12.0;Database=C:\Teste.xlsx;', 
    'SELECT * FROM [Aba1$]'
) A
WHERE A.Nome = 'Paulo'

UPDATE A

SET A.Nome = 'Teste 1'

FROM OPENROWSET (

'Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;Database=C:\Teste.xlsx;',

'SELECT * FROM [Aba1$]'

) A

WHERE A.Nome = 'Paulo'

Example:

Example with JOIN using local database tables:

Stored Procedure to make everyday life easier:

CREATE PROCEDURE [dbo].[stpAtualiza_em_Excel](
    @Caminho varchar(max), 
    @Aba varchar(200), 
    @Tabela varchar(200), 
    @Colunas_Join varchar(max), 
    @Colunas_Update varchar(max)
)
AS
BEGIN
        
    DECLARE 
        @join VARCHAR(MAX) ,
        @update VARCHAR(MAX);


    SELECT  
        @join = ISNULL(@join + ' and ', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Join, ',') AS a;
    

    SELECT  
        @update = ISNULL(@update + ',', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))
    FROM    
        dbo.fncQuebra_Texto(@Colunas_Update, ',') AS a;
    
    
    DECLARE @Exec VARCHAR(MAX)
    
    SET @Exec = 'UPDATE A '
        + 'SET '
        + @update
        + ' FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='
        + @Caminho
        + ';'',	''Select * From ['
        + @Aba
        + '$]'') A'
        + ' JOIN '
        + @Tabela
        + ' b'
        + ' ON '
        + @join

    EXEC(@Exec)
 
END

CREATE PROCEDURE [dbo].[stpAtualiza_em_Excel](

@Caminho varchar(max),

@Aba varchar(200),

@Tabela varchar(200),

@Colunas_Join varchar(max),

@Colunas_Update varchar(max)

)

BEGIN

DECLARE

@join VARCHAR(MAX) ,

@update VARCHAR(MAX);

SELECT

@join = ISNULL(@join + ' and ', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))

FROM

dbo.fncQuebra_Texto(@Colunas_Join, ',') AS a;

SELECT

@update = ISNULL(@update + ',', '') + 'a.' + LTRIM(RTRIM(s)) + ' = b.' + LTRIM(RTRIM(s))

FROM

dbo.fncQuebra_Texto(@Colunas_Update, ',') AS a;

DECLARE @Exec VARCHAR(MAX)

SET @Exec = 'UPDATE A '

+ 'SET '

+ @update

+ ' FROM OPENROWSET (''Microsoft.ACE.OLEDB.12.0'', ''Excel 12.0;Database='

+ @Caminho

+ ';'', ''Select * From ['

+ @Aba

+ '$]'') A'

+ ' JOIN '

+ @Tabela

+ ' b'

+ ' ON '

+ @join

EXEC(@Exec)

END

FncQuebra_Text function required to use SP:

CREATE FUNCTION [dbo].[fncQuebra_Texto] (
    @str NVARCHAR(4000) ,
    @separator CHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH tokens ( p, a, b )
    AS ( 
        SELECT   1, 1, CHARINDEX(@separator, @str)
        UNION ALL
        SELECT   p + 1, b + 1, CHARINDEX(@separator, @str, b + 1)
        FROM     tokens
        WHERE    b > 0
    )
    SELECT  
        p - 1 zeroBasedOccurance ,
        SUBSTRING(@str, a, CASE WHEN b > 0 THEN b - a ELSE 4000 END) AS s
    FROM    
        tokens
);

CREATE FUNCTION [dbo].[fncQuebra_Texto] (

@str NVARCHAR(4000) ,

@separator CHAR(1)

)

RETURNS TABLE

RETURN

(

WITH tokens ( p, a, b )

AS (

SELECT 1, 1, CHARINDEX(@separator, @str)

UNION ALL

SELECT p + 1, b + 1, CHARINDEX(@separator, @str, b + 1)

FROM tokens

WHERE b > 0

)

SELECT

p - 1 zeroBasedOccurance ,

SUBSTRING(@str, a, CASE WHEN b > 0 THEN b - a ELSE 4000 END) AS s

FROM

tokens

);

SP Usage:

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

Adriano said:

5 April 2021 19 gies: 27

Goodnight! I have a base in Excel and I brought it to SQL, but how do I update the entire table in SQL? Example I added another line in excel with more information and now I want it to bring in SQL.

Danilo said:

12 February 2021 to 14: 45

TOP !!… I'll look here how to do this with CSV files,
Thanks

Other Languages

Subscribe to a blog by email

Blog Views

Power Alerts

Azure Banking Admin Course

SQL Server Security Course

My Certifications

Posts Archive

Categories

Recent Posts

SQL Server - Importing and Exporting data from Excel spreadsheets

How to Import an Excel Spreadsheet into SQL Server

How to enter / export data from SQL Server to Excel

How to update data from an Excel spreadsheet through SQL Server

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

You may also like ...

Comments

Leave a Comment Cancel reply

How to Import an Excel Spreadsheet into SQL Server

How to enter / export data from SQL Server to Excel

How to update data from an Excel spreadsheet through SQL Server

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

You may also like ...

SQL Server - How to disable “sa” login minimizing impacts

SQL Server - How to Integrate Database with Ryver and Send Messages Using CLR (C #)

SQL Server - How to identify and monitor total free disk space used by database datafiles

Comments

Leave a Comment Cancel reply