July 2025 Discount: Comprehensive Database Performance Health Check | Testimonials

Home

SQL SERVER – Export Data From SQL Server to Microsoft Excel Datasheet

Pinal Dave
SQL Interview Questions and Answers
231 Comments

Question: How to Export Data From SQL Server to Microsoft Excel Datasheet?

Answer: 

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio – Query Editor.

SQL SERVER - Export Data From SQL Server to Microsoft Excel Datasheet dbtoexcel 

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively, you can download sample Spreadsheet from here.

Run following code in SQL Server Management Studio – Query Editor.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

Open contact.xls spreadsheet you will see first five records of the Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open, it may throw an error. You can change your spreadsheet name as well name of the Sheet1 to your desired name.

Let me know what you think of this blog post. It is very short but indeed a very effective query. I have been using it for a while and it works just fine.

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Scripts, SQL Server, SQL Utility
Previous Post
SQL SERVER – Download Frequently Asked Generic Interview Questions
Next Post
SQL SERVER – Introduction to Aggregate Functions

Related Posts

231 Comments. Leave new

  • Guru
    January 26, 2011 6:02 pm

    I am new to SQL Server.I have few questions.Any help would be highly appreciated.

    I have set of query and need to run as a schedule job.

    1.First,I want to know how to redirect the output of the query in Excel format to particular location.

    2.How to set a schedule task for that query.Need to be stored in the same folder.So i cant able to set a same file name for the result.

    Reply
  • Ram
    March 23, 2011 1:46 pm

    yes its working but i want that the excel should be overwrite instead of just appending records to it .

    i mean every time i execute this query to insert records from sql table to excel it should fill with the 1st row . but its appending thats not desireable in my case.

    PLS HELP . M LOOKING FOR IT FOR QUIET A DAYS.

    THANKS

    Reply
  • talles
    March 31, 2011 12:25 am

    My query is correct,but when I change the file xls (values from the collumns).My query keep the last result.Why happend this?
    Thanks for your attention!

    Reply
  • Tiffany
    April 12, 2011 9:34 pm

    I ran this script as a select * successfully with no errors, but the spreadsheet is missing data past the 11th column?

    Reply
  • Shrikant Shelke
    April 25, 2011 12:15 pm

    USE [Volkswagen1];
    GO
    INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=c:\contact.xls;’,
    ‘SELECT * FROM [Sheet1$]’)
    SELECT TOP 5 vLogin_UserID, VLogin_Password,vLogin_UserType,userCreationDate,lastLoginDate
    FROM Tbl_Login
    GO

    This code is run on local server successfully.But when i use Tbl_Login table which is present in server database (another PC) not work, error occurred.
    suggest the changes.

    Reply
  • Mir
    April 26, 2011 5:54 am

    I ran the query mentioned above and got this error.
    OLE DB provider ‘Microsoft.Jet.OLEDB.4.0’ cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
    I know it does not work on a 64 bit machine. Is there any work around to that.

    Thanks,

    Reply
  • Anand M
    May 3, 2011 2:30 pm

    what i can do for this error message :

    Msg 7399, Level 16, State 1, Line 2
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 2
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    can any one know ple tell me …….

    Reply
  • Soma
    May 3, 2011 4:33 pm

    Hi Pinal,
    I got error while trying to read excel file from network path

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    here is my code

    insert INTO Temp_TS_Contract_Document_Staging
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
    ‘Excel 8.0;Database=\\computername\PDFAttachment\format_images1.xls’,’SELECT * FROM [Sheet1$]’)

    while its works fine with the local system
    insert INTO Temp_TS_Contract_Document_Staging
    SELECT *
    FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
    ‘Excel 8.0;Database=C:\format_images1.xls’,’SELECT * FROM [Sheet1$]’)

    Reply
    • madhivanan
      May 4, 2011 8:02 pm

      It means that the server doesnot have JET provider. Also the file should be in server’s directory

      Reply
  • Robin
    May 24, 2011 1:18 pm

    Hi

    I am stuck up with a problem. If I try

    SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=E:\test.xls’, [Sheet1$])

    the query executes properly.

    But if i try

    SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0′,’E:\test.mdb’;’Admin’;”, tmptable)

    im getting the error

    OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” returned message “Cannot open database ”. It may not be a database that your application recognizes, or the file may be corrupt.”.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    Please advice me what should i do.

    Reply
  • neelima
    June 15, 2011 12:56 pm

    great code..thanks

    Reply
  • Amar
    June 21, 2011 12:44 am

    Hi,

    Found error when access from excel data.the error is…

    Msg 7399, Level 16, State 1, Line 1
    The OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)” reported an error. The provider did not give any information about the error.
    Msg 7303, Level 16, State 1, Line 1
    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    any one can healp on that…..

    Reply
  • Vinay
    June 30, 2011 3:32 pm

    Hi,

    I am using the same first code but I am getting the same error as

    Cannot initialize the data source object of OLE DB provider “Microsoft.Jet.OLEDB.4.0” for linked server “(null)”.

    Everything I have done according to the instruction and even the file is not opened while executing the commands.

    Please suggest the needful to be done.

    Reply
  • Balu
    July 29, 2011 1:53 am

    Hi All,

    I need compare ID Column in existing SQL table and new Excel Sheet.

    How to write Query

    Reply
    • madhivanan
      July 29, 2011 3:21 pm

      Select t1.* from your_table as t1 inner join
      (
      select * from Openrowset….. — as shown in the post
      ) as t2 on t1.Id=t2.id

      Reply
  • balu
    July 29, 2011 3:49 pm

    Select t1.account_no from newtest as t1 inner join
    (
    select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0’,
    ‘Excel 8.0;Database=C:\Documents and Settings\kyros\Desktop\Delwise sub base.xls;HDR=YES’,
    ‘SELECT * FROM [Sheet1$]’)
    ) as t2 on t1.account_no=t2.account_no

    Hi Thanks. It work fine.

    i need to insert a new table in SQL database that matching records. How to right Query

    Please Help me

    Reply
    • madhivanan
      July 29, 2011 5:01 pm

      insert into new_table (col_list)
      Select t1.account_no from newtest as t1 inner join
      (
      select * from Openrowset (‘Microsoft.Jet.OLEDB.4.0′,
      ‘Excel 8.0;Database=C:Documents and SettingskyrosDesktopDelwise sub base.xls;HDR=YES’,
      ‘SELECT * FROM [Sheet1$]‘)
      ) as t2 on t1.account_no=t2.account_no

      Reply
  • balu
    July 29, 2011 3:51 pm

    i need to insert on Excel data only. how to write the query

    Reply
  • Balu
    July 29, 2011 4:46 pm

    Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

    Ex: This is SQL Table Columns
    ———————————-

    month1 month2 month3 month4
    400 300 200 100

    This is Excel Sheet Table
    ——————————–
    Amount
    800

    i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

    inserting on New SQL Tables.

    Ex:

    Amount month1 month2 month3 month4
    0 200 0 0 0

    next day do the same process

    How to write the Query please help me.

    Reply
  • Balu
    July 29, 2011 5:20 pm

    Hi Thanks. Its works fine. but the Account_no column show values

    for this format. i need to store whole numbers. Ex:9072790008

    9.07279e+008
    9.07299e+008
    9.07308e+008

    Please Help me

    Reply
  • Apostar on line
    August 2, 2011 10:11 am

    Great post. Thanks for the nice read

    Reply
  • Balu
    August 2, 2011 6:54 pm

    Hi i need to less the amount for three column. Matching to Sql Table and Excel Sheet

    Ex: This is SQL Table Columns
    ———————————-

    month1 month2 month3 month4
    400 300 200 100

    This is Excel Sheet Table
    ——————————–
    Amount
    800

    i need to less Excel Amount(800) Column to sql table month column that is month1 month2 month3 month4

    inserting on New SQL Tables.

    Ex:

    Amount month1 month2 month3 month4
    0 200 0 0 0

    next day do the same process

    How to write the Query please help me.

    Reply
  • Congenital heart defects
    August 8, 2011 5:25 am

    Great stuff man, come and visit my site

    Reply
« Older Comments Newer Comments »

Leave a Reply

Pinal Dave is an SQL Server Performance Tuning Expert and independent consultant with over 22 years of hands-on experience. He holds a Masters of Science degree and numerous database certifications.

Pinal has authored 14 SQL Server database books and 94 Pluralsight courses. To freely share his knowledge and help others build their expertise, Pinal has also written more than 5,800 database tech articles on his blog at https://blog.sqlauthority.com.

Pinal is an experienced and dedicated professional with a deep commitment to flawless customer service. If you need help with any SQL Server Performance Tuning Issues, please feel free to reach out at pinal@sqlauthority.com.

Pinal is also a CrossFit Level 1 Trainer (CF-L1) and CrossFit Level 2 Trainer (CF-L2).

Nupur Dave is a social media enthusiast and an independent consultant. She primarily focuses on the database domain, helping clients build short and long-term multi-channel campaigns to drive leads for their sales pipeline.

Comprehensive Database Performance Health Check

Is your SQL Server running slow and you want to speed it up without sharing server credentials? In my Comprehensive Database Performance Health Check, we can work together remotely and resolve your biggest performance troublemakers in less than 4 hours.

Once you learn my business secrets, you will fix the majority of problems in the future.

SQL Server Performance Tuning Practical Workshop

Have you ever opened any PowerPoint deck when you face SQL Server Performance Tuning emergencies? SQL Server Performance Tuning Practical Workshop is my MOST popular training with no PowerPoint presentations and 100% practical demonstrations.

Essentially I share my business secrets to optimize SQL Server performance.