Requirements to export data into an .xlsx file

Question

Requirements to export data into an .xlsx file

richlion2

SSCrazy

Points: 2484
More actions
January 11, 2017 at 4:16 am

#401356

Hello,
I've just been asked to prepare a query and generate it into an .xlsx file. There are numerous hints on how to do the SQL using T-SQL or Powershell.
Here are some few examples:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
http://microsoft-ssis.blogspot.co.uk/2014/02/connecting-to-excel-xlsx-in-ssis.html
What I cannot find is how to verify that my SQL-Server has the right components installed. I can only see from the "Programs and Features" that MS Office is installed (I uploaded a screenshot). Is there any way I can verify that I can generate an .xlsx file? Which version of Microsoft.ACE.OLEDB should I use against a 64-bit SQL Server 2012 version?
Bare in mind I haven't got a clue about things like "OLE DB Provider for Jet", what the differences are, etc.
If this is the code that has to be used:
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable
then what is the correct version to generate an .xsls format? Am I correct in thinking the code above is to generate .xls, not .xlsx?
Thanks
Richard

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99130 More actions · Answer 1

To export from SQL Server to Excel, you'll need to use the ACE 32bit drivers (The JET drivers are the older drivers and are no longer supported).

It's the Microsoft Access Database Engine 2010 Redistributable drivers that you need. AS above, ensure you download the 32bit (x86) file, NOT the 64bit.

This will also enable you to perform tasks using SSIS to export or import data to similar files.

hopefully this will get you on the right path 🙂

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

David Burrows SSC Guru Points: 65142 More actions · Answer 2

If you output your data as tab delimited to an .xls (not .xlsx) file

Excel will be able open the file if you accept the prompts (depending on version of Excel)

Far away is close at hand in the images of elsewhere.
Anon.

richlion2 SSCrazy Points: 2484 More actions · Answer 3

Thanks, the problem is this is an index file that goes to a 3rd party to load data into our web site, so it must be automated, hence without any prompting. I suspect the web program will not be reading the .XLSX as a spreadsheet, rather as a text file. I asked my colleagues about this, why they want to generate .xlsx instead of .csv.

richlion2 SSCrazy Points: 2484 More actions · Answer 4

Thom A (1/11/2017)
To export from SQL Server to Excel, you'll need to use the ACE 32bit drivers (The JET drivers are the older drivers and are no longer supported).
It's the Microsoft Access Database Engine 2010 Redistributable drivers that you need. AS above, ensure you download the 32bit (x86) file, NOT the 64bit.
This will also enable you to perform tasks using SSIS to export or import data to similar files.
hopefully this will get you on the right path 🙂

Thanks, but are you sure? MS Access Drivers onto a SQL Server 2012 installation? Pretty odd solution from MS.

Thom A SSC Guru Points: 99130 More actions · Answer 5

richlion2 (1/11/2017)
Thom A (1/11/2017)
To export from SQL Server to Excel, you'll need to use the ACE 32bit drivers (The JET drivers are the older drivers and are no longer supported).
It's the Microsoft Access Database Engine 2010 Redistributable drivers that you need. AS above, ensure you download the 32bit (x86) file, NOT the 64bit.
This will also enable you to perform tasks using SSIS to export or import data to similar files.
hopefully this will get you on the right path 🙂
Thanks, but are you sure? MS Access Drivers onto a SQL Server 2012 installation? Pretty odd solution from MS.

Very sure.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

frederico_fonseca SSCoach Points: 16154 More actions · Answer 6

why not the 2013 or even the 2016 version of the ACE driver?

2013 - https://www.microsoft.com/en-ie/download/details.aspx?id=39358

2016 - https://www.microsoft.com/en-ie/download/details.aspx?id=50040

You do not need to buy any special product in order to redistribute the Access 2013/6 Runtime. You can freely redistribute it or point users to this download.