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.
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)
231 Comments. Leave new
The reason many are getting this message:
[ “Microsoft.Jet.OLEDB.4.0″ for linked server “(null)” ] is because Excel must exist on the same machine you have the xls file on. The spreadsheet itself does not carry the Jet database engine.
hi dave i tried above code its working for excel but i want to export data from access data base to sql server and vise versa how can do this
pls help me i am a ferser……………..
@Rizwan
There are a tleast 5 ways of doing this, please look at the below link for more details.
https://support.microsoft.com/en-us/help/321686/how-to-import-data-from-excel-to-sql-server
IM
Hi,
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=C:temptest.xls;’,
‘SELECT aftercare_id,participant_id,r_followup_period_id FROM [Sheet1$]’)
SELECT [aftercare_id],[participant_id],[r_followup_period_id]
FROM view_1
go
The above code works, but it will allow me to put only 7 columns, if i put ‘*’ it is not inserting data into excel, could you please tell me how to fix this problem
i have 30 columns in my view. i want to use all columns, could you please respond to me as early as possible.
Thanks,
Roy
@Jith,
Sample from below link :
insert into OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=D:testing.xls;’,
‘SELECT * FROM [SheetName$]’) select * from SQLServerTable
Please visit this link to get more details.
Link :
~ IM.
Hi,
I have a problem here. My codes works well. Everything seems fine. My problem is, whenever I upload large files like 18 mb .xls file, It shows page cannot be displayed. I’m working with a project for a large scale company. I really need this. this one works in SQLquery but not in my app. I hope someone would help me quick. INSERT INTO dbo.data SELECT * FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0;Database=C:Documents and Settings…analysis.xls’, [data$])
you can contact me here: jaythree03@gmail.com
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Destination – Configuration” (37).
(SQL Server Import and Export Wizard)
You need to install the latest service pack for SQL Server version you are using
Hi I’ve tried this example with minor changes like:
INSERT INTO OPENROWSET (‘Microsoft.ACE.OLEDB.12.0;Database=C:\Documents and Settings\Thato\My Documents\contact.xlsx;’,’SELECT * FROM [Sheet1$]’)
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
——————–
I get a syntax error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘)’.
Please help
That should be
INSERT INTO OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
'Database=”C:Documents and SettingsThatoMy Documentscontact.xlsx”;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 CallName, Surname
FROM DTIHeadcount
Where Period = 200904
Thanks …. it helped me to upload xlsx data in sql 2005
it is not working with sql server 2008 and an office 2010 file.
In a simple way, we can export the resultset to .csv file using the following scenario:
1. Go to Tools>>Options>>Query Results>>Sql Server>>Results to text: specify the output format to “Tab delimited”
2.While executing the query – set Resultset to show as text instead of in grid (Ctrl + Q + T)
3. Select all from the Resultset and save Results as “name.csv” or “name.xls”
– Praveen
Thanks Praveen.That was quick & simple
Hi!!!
Is possible to insert data from SQL Server to an Excel Spreadsheet that has a password protection? I need this because I want to prevent User Editing…
Tks a lot!!!
Great topic. Thanks
Worked for me, thanks!
I can’t find the URL that I wanted to cite, but just to let folks know, the “Jet” drivers for this type of wonderful thing are not supported on the 64 bit version of SQL Server. Nice, huh?
Error :- 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)”.
Solution :
Make sure your the file path and SQL server are in the same PC
Hello sir,
For the solution you gave regarding exporting data from SQL server to Excel sheet, is there any alternative that the file should be manually created along wid a header row.
Is there any way the excel sheet too can be created with a header row defined by running a command line script from the sql promt so that the file along wid header is created at the same time just before data is inserted into that excel sheet i.e creating a dynamic query in which the file path , filename, column headers are specified in variables
Please help out.
Thanx
USE [hrnew];
GO
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=d:\12345.xls;’,’SELECT * FROM [Sheet1$]’)
SELECT TOP 5 EMP_NO,EMP_FULL_NAME_AR FROM dbo.EMPLOYEES
GO
after run your script with my data i get the next error messge.
please help me
Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Hi, to all above that got the error :
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.
Ensure that you have a space between Excel and 8.0 in the OPENROWSET section ie
INSERT INTO OPENROWSET (‘Microsoft.Jet.OLEDB.4.0’,
‘Excel 8.0; Database=c:somesheet.xls;’
,’SELECT * FROM [Sheet1$]’)
to mahmoud
You generally see this type of error if you have not put the Column headers into Sheet1 of your spreadsheet .
ColumnHeaders must be the same as the Field Names.
Thanks this works great.
One problem I am having. Numeric fields that I am exporting to excel are entered into the excel sheet as text.
I have formatted the column in the excel sheet to number and I am still getting text. Any ideas how to fix this?
Thanks
Hi Every one,this good and simple. I tried this but it is not Working For me.after that working fine because the same syntax could not be worked for Excel 2007.Finally i got and if we use .xls format and also it will give error if an excel file is open and also for wrong name and also for columns not mentioned