Search code examples
sql-serverexcelexcel-2007sql-server-2014

Export Data from SQL Server 2014 to Excel

I tried to export data from SQL Server 2014 to Excel.

I tried like this 

insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0
Xml;HDR=YES;Database=D:\testing.xlsx;', 
'SELECT OI_NAME,OI_ADDRESSS FROM [Sheet1$]') select OI_NAME,
OI_ADDRESSS from OI_TEMP

But I get this error

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I searched on the internet, and found this:

The SQL Server Error Message if a user have no rights for SQL Server TEMP directory OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified 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)".

But I assign permission is still does not work.

Why?

Solution

  • To export data from SQL to Excel, you need to follow 2 steps:

    • Step 1: Connect Excel to your SQL database‍ (Microsoft SQL Server, MySQL, PostgreSQL...)
    • Step 2: Import your SQL data into Excel

    The result will be the list of tables you want to query data from your SQL database into Excel:

    enter image description here

    Step1: Connect Excel to an external data source: your SQL database

    1. Install An ODBC
    2. Install A Driver
    3. Avoid A Common Error
    4. Create a DSN

    Step 2: Import your SQL data into Excel

    1. Click Where You Want Your Pivot Table
    2. Click Insert
    3. Click Pivot Table
    4. Click Use an external data source, then Choose Connection
    5. Click on the System DSN tab
    6. Select the DSN created in ODBC Manager
    7. Fill the requested username and password
    8. Avoid a Common Error
    9. Access The Microsoft Query Dialog Box
    10. Click on the arrow to see the list of tables in your database
    11. Select the table you want to query data from your SQL database into Excel
    12. Click on Return Data when you’re done with your selection

    To update the export automatically, there are 2 additional steps:

    1. Create a Pivot Table with an external SQL data source
    2. Automate Your SQL Data Update In Excel With The GETPIVOTDATA Function

    I've created a step-by-step tutorial about this whole process, from connecting Excel to SQL, up to having the whole thing automatically updated. You might find the detailed explanations and screenshots useful.