Export SQL To Excel

Mar 13, 2008

I have written a query which dumps the output to a table, ideally I would like to select everything from this table in to an Excel file.

Looking @ http://blog.sqlauthority.com/2008/01/08/sql-server-2005-export-data-from-sql-server-2005-to-microsoft-excel-datasheet/

Made me think this would be quite easy... so

1. I created a spreadsheet named test.xls with the column headings from my temp table
2. Saved and closed this xls
3. Tried to run the following:

USE [PEI];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:DELL est.xls;',
'SELECT * FROM [Sheet1$]')
SELECT *
FROM tblCFPooled
GO

Where C:DELL est.xls is where I saved test.xls, tblCFPooled is the table I have populated in the firstplace and PEI is the database name.

When I run this the following error crops up:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

I then found something on a different site:
http://www.mssqltips.com/tip.asp?tip=1202

So based on this I tried:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:DELL est.xls;',
'SELECT * FROM [T$]') select * from tblCFPooled

And obtained the following error:

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "The Microsoft Jet database engine could not find the object 'T$'. Make sure the object exists and that you spell its name and the path name correctly.".
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

(I renamed the sheet1 to T if you are wondering where T$ comes in!)


Before running either of these 'export' queries I did as instructed in the first link:
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

Which produced results:
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

I assumed from that I had successfully enabled Ad Hoc Distributed Queries - Is that correct?

Both methods seem to throw up a similar error, an error which makes me think its some kind of SQL authentication issue.

Has anyone successfully exported from SQL to Excel - if so, any tips?!

(For the record I am using Management Studio, we are running SQL Server 2005, I am doing all this on a Vista Business machine, the SQL server is on a local server on our network here. )

View 1 Replies


ADVERTISEMENT

Export To Excel - Number Formatted Cells Export To Excel As 'General' ?

Feb 5, 2007

Anyone know why cells within a matrix that are formatted as numeric export to Excel with a cell format proprty of "General"? Cells within a table however export with an appropriate format.

Thanks

View 1 Replies View Related

Excel Report Export Causes Excel 2000 To Crash On Print

Dec 5, 2007

When I open the spreadsheet in Excel 2000, it works fine. When I try to print, it crashes Excel. In testing, I narrowed it down to the Header/Footer, because it also crashes when I go to Page Setup and click on the header/footer tab.

However, I can print the same spreasheet from Excel 2007.

Am I just dealing with a "you need to upgrade all your clients" situation, or is there a known issue with certian formatting that is passed out with reports that is not supported by older versions of Excel?

I am using Reporting Services 2005 SP2 to serve up the report that is exported to Excel.

Any assistance is appreciated.

View 3 Replies View Related

DTS Export To Excel (How To Format Results In Excel)

Nov 22, 2005

Hi All

I've been googling this for a while now and can't seem to find any elegant answers.

I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.

Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?

His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).

In an ideal world an individual would send an email to the Server with two formated parameters (@FromDate & @ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.

Thanks

GW

View 2 Replies View Related

Report Export To Excel - Report Header Is Not Appearing In Each Page Of Excel.

Feb 28, 2007

I've built a fairly straight forward report in RS that looks normal in preview mode and in PDF format with out any issues.But when I export it to Excel report header is not appearing in each page.Any ideas as to why this is occurring?thanks in advance,Ramesh KS

View 1 Replies View Related

Export To Excel

Feb 1, 2008

Hello All, I'm simply exporting data from SQL to Excel via the Export Data feature in SQL Management Studio.My problem is I have a column aliased as [ID #] and when it exports it changes the Pound Sign into a decimal   ->  ID .SPACE(25) As [ID#], It's not a major problem but does anyone have any suggestions? Thanks, Kyle 

View 3 Replies View Related

Export To Excel

Nov 16, 2005

Hi there I know its possible to export data from a gridview to excel. I'm looking to export data directly from a stored procedure at the click of a button.Somebody suggested using the following:insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C: esting.xls;', 'SELECT * FROM [SheetName$]') select * from table-nameWhen I tried executing the above lines of codes I got the following error message:"Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server." If anyone has any idea whats wrong ... plssssssssssssssssssss ... let me know .... Thanks in advance.

View 4 Replies View Related

DTS Export To Excel

Nov 27, 2001

I have a job which produces a daily report and exports it to excel. However, instead of overwriting the contents of the file the data is being appended to the file. Any suggestions welcome.

Thanks in advance
Vince.

View 5 Replies View Related

Export To Excel Using DTS

Jan 31, 2006

I'm trying to export to excel using dts, but using a stored procedure where i have a query using temporary tables (#D,#T,#R) and the result is a table with a variable number of columns (sometimes 3 columns and sometimes 10 columns)



what is the best way to do this? DTS, BCP

thanks
FOCEL

View 1 Replies View Related

DTS Export To Excel

Mar 28, 2006

I want to run Stored procedure that is returning recordsets and is using cursors/ temporary tables (MS Sql 2000 Server).
The output of this SP is to be used to prepare an excel Report.

In the Transform Data Task Properties:

EXEC sp_ProductivityReport_ByDay '01/01/2005','02/01/2005'

It shows me the data in the Preview, but asks me to define transformations. Further on the transformations, it does not shows up the source columns (although they were populated in the preview)

When I perform the same task using DTS Export utility, i get the following error:

Error source: MS ole db provider for sql server
Error Desc : Null Accessors are not supported by this provider
context: error calling CreateAccessor. Your provider does not support all the interface/methods required by DTS

Please Help

Thanks

View 3 Replies View Related

Export To Excel

Feb 18, 2007

Hello All

First time setting up A SSIS

Trying to import a excel that creates a new table called lockbox.

Does not import any records from Columns-Contract Id, Owner ID,
that have a null value.

Then in the Site Id -that is a number value- change to a text value
and update it to it text value.

for example: Site Id Column
2 = Ell
3 = CSI
12 = Ell2

Can someone get me started in the right direction?

Thanks for your time and help

JK

View 1 Replies View Related

Excel Export

Jul 11, 2007

Hi All,



While exporting my reports to Excel , I want them to be exported to a single excel book in multiple sheet.

i.e. single report in single sheet and all in a single excel workbook.

Does anyone have any idea how to do that?



Regards

Saurabh

View 2 Replies View Related

Export To Excel

Feb 27, 2007

Hi,

I have a report when I run that report if the result is span for multiple page the table header is displaying for each page.When exported to PDf it is displaying the table header for each page.but when we export to Excel the table headers are not displaying.Any work around for this.

Thanks

View 1 Replies View Related

Export To Excel

Sep 27, 2007

Hi ,
I have a simple report which i want to export to excel format. the are no groups in the report. I need each page of the report to appear as a seperate page in excel. currently when i export the report i get a single continuous page. is there any way in which i can do this ????

View 3 Replies View Related

Excel Export Bug

Jul 31, 2007

I have some reports that I used vbcrlf in. Whenever they are exported to Excel only the first line appears.

Ex: Name (&vbcrlf)
Address 1 (& vbcrlf)
Address 2 (& vbcrlf)
City, State, Zip

This exports to Excel and displays the name line only.
However, if I close the file that was exported and the extra blank screen, and hit export to excel again for a second time, all of the information is displayed.

View 1 Replies View Related

Can You Export Them To Excel?

Mar 17, 2008

Have you tried exporting your report with subreports to Excel? How does it work?
Thanks!

View 5 Replies View Related

Export To Excel

Nov 2, 2007



I am having a problem exporting data to excel using t-sql. I need to create a t-sql stored procedure that exports the resultset of a dynamic query to Excel where the columns are not know beforehand.

I have been able to do it using bcp, but the problem is that the bcp method just exports the data to an tab-delimited ascii file with a .xls extension. Excel is smart enough to pick it up and parse it out into the individual cells, but the problem arises if the field value contains any special chars that may have been picked up when the user input the data by using copy & paste. Then the columns/rows are skewed for the record that has the field with the special chars in its field value.

I have tried using the openrowset method and if you know the fields(cols) that you want to retrieve, it works great. All you have to do is pre-create an excel file with the first row containing the columnNames for each column. But for dynamic queries, this is not an option. I have read somewhere that you can do a range, but have not been able to figure out to do that yet. You would have to pre-determine the number of columns & rows and figure out the range. Columns A-Z may not be as big an issue, but then I would have to figure out the values for any columns if the column count exceeded 26.

Outside of using Ole-Automation, is there any way than I can achieve the results that I really need? It just seems like over-kill for something that should be a simple task. If it cannot be done outside of Ole-Automation, is there a good example that someone can point me to?



Thanks in advance for any help!
glh8564

View 11 Replies View Related

Export To Excel

Jan 18, 2008



Hi all,
I have a SSRS Report which I have built using only a table. There are many table header rows, detail rows and table footers rows. For the layout of the report, I have merged many Table columns as well.

So when I am exporting this report to excel, it renders the report as it is but I am not able to manipulate any data in the excel till I do a lot of cleaning up. I have to remove the table headers, footers and also unmerge the merged columns etc.

My users are asking that they need a way throught which when they export the data it presents them with just the data which can be easily sorted, added, manipulated.

Is there a way of doing this. I thought about two ways of doing it.


Make another report in which only the data is shown and thus when this data is exported, then only the data gets exported to excel. But this is a very tedious way. Because as the number of reports increases, thi sis going to be a tough task to follow.

Make a web service or a plug in which will be connected to my web form. Then when the Export to Excel is clicked, this plug in will clean up the SSRS Report and export to excel in a way that only the data is exported.
But I dont know much about web services or plug ins.

Could you please guide me that if it is achieveble. And what are the various ways in which this task can be done.

I really need this urgently. Any help or suggestion is welcome.

Thanks
Swati


View 3 Replies View Related

On Export To Excel

Jun 28, 2007

I have list within an list and we have section 1 thru 7. We display the list based the parameters seleted from report for some result section might start from 3 thru 6 . In such case when try to export to excel i get huge gaps as i am missing section 1 thru 2 in the report.





How do we avoid those spaces when exporting to excel,





View 1 Replies View Related

Export To Excel

Jan 18, 2007

Hi,

I'm trying to use the export to Excel (Excel Destination) however, I wish to supply a dynamic piece of SQL to generate the data.

When I set the properties I just get an option to enter "Table or View", "Table or View as Variable" or "SQL Command".

The issue is that the table does not exist until the SSIS job is run and the columns will not always be the same anyway.

How can I use a simple dynamic piece of SQL (or stored proc) to get a recordset that is simply dumped out to excel - rows... columns... nothing more ?.

Help very much appreciated - Paul.

View 3 Replies View Related

Export To Excel

Nov 9, 2007

Hi,

I have several similar reports all with a table with more or less columns depending on the report it self.

On one report I made the table rows with a bigger Height( because I have a small column with big data where I need to wrap)

It works OK on the Preview (Designer)
it works OK on the reports Web Page (ReportManager) .
it works OK when I export to PDF
When I export to Excel the rows are not with the height I specified and not all the data is visible (I have to adjust row height in excel).

Why is this appening?


Help is really apprecieted.


Tks,
DevDiver

View 5 Replies View Related

Export To Excel

Mar 30, 2007

Hi,

When our report exported to Excel, In the Excel file it is giving some extra columns between the data columns.How to avoid those extra columns.This extra columns causes problems when we want to sort one column it is throwing sort can't be applied on merged columns.

How to avoid the Extra columns.



Thanks in advance

View 7 Replies View Related

Export To Excel?

Mar 26, 2007



I am using SQL Server CE as a backend to a dekstop application. I would like the ability to export some of the SQL Server CE tables in my databse to MS Excel. Is this possible?

Thanks.

Mike



View 1 Replies View Related

Export To Excel

Apr 14, 2007

I have a big report which I want to export t oexcel. I get an error with timeout as it is a big fine. Does anyone have a suggestion as to how I can change the timeout? I tried to run the file on server early morning with File to Delivey option.



regards

Josh

View 1 Replies View Related

DTS Export To Excel

Aug 7, 2006

I'm looking for the best way to export the results of a parameterized stored procedure (SQL 2000) to excel. I can do this with DTS using global variables for the parameters, but each time I execute the package it appends the data below where the previous data was, leaving a bunch of blank rows. I need the data to always be appended to the 2nd row (replacing the old data) because I have a chart based on a dynamic named range in Excel. Is there an easy way to do this in DTS, or should I approach this another way (ADO, ActiveX Scripts, .NET, etc.)? Thanks,

Dave

View 3 Replies View Related

SQL 6.5 Export To Access Or Excel

Nov 15, 2002

In SQL7 you can use a wizard to export database to access or excel. Any known scripts or procedures? I need to give the data in one of these formats so it can be massaged into Oracle by our Oracle vendor. Any help or referals will be greatly appreciated.

View 3 Replies View Related

Export Data To Excel

Nov 17, 2005

Hi,
can any help me in how to export data from sqlserver 6.5 to Excel sheet.

View 4 Replies View Related

Export Result To The Excel

Nov 18, 2005

Hi,

I am a new in SQL Server.
I have a question.

I create the table called "tbl1"
I create a query called "qry1", this query retrieve the data from the "tbl1".

after running the query, and then convert the result to the Excel.

How can I do that?
Please let me know, thanks a lot. Thanks.

View 3 Replies View Related

Export To Excel File

Jun 4, 2006

I've created a dts package that exports data to an excel. The data exports okay, but I'd like to make a few changes on how the data is exported.

First, when the data exports to the excel file, it always creates a new excel sheet. How can I map the data to the existing sheet in the Excel file? I have an existing sheet named 'Sheet1'. I've deleted the other two sheets in the excel file, so there is only one sheet. When I created the destination table in the dts package for the data transformation task, I specified the name as 'Sheet1', but when it exports, it creates a new sheet named 'Sheet11'. I haven't been able to correct this.

Second, I'd like to either ammend data to the last row in the excel file, which I'd have to know the last record that was inserted from the SQL table. Or probably much easier, simply drop the existing sheet in the excel file and simply re-create and insert.

How can I do this?

Thanks,
-D-

View 1 Replies View Related

DTS Export To Excel Too Many Recs?

Jan 15, 2004

Hi All,

I'm trying to export around 115,000 rows from ms sql 2000 into Excel 2000, using the manual process. I just need this one time dump.

I am able to successfully export around +- 65,000 rows, but the operation fails after that.

I need to be able to get all the rows out, so using TOP obviously doesn't work.

Is there some "version" or modified way to use TOP to get...say, rows 65,000 to 90,000 then 90,001 to 115,000 ?

This would'nt be an issue if the db I'm working with was MySQL...I'd just use the LIMIT function and pull out 3 different chucks. Is there anything similar to LIMIT...or something converse to TOP in MS SQL? Or perhaps another way to dump the table then export it all (or in portions) into Excel?

Thanks!

View 7 Replies View Related

Export Data From Excel To MDF

Feb 28, 2004

Hi,

I have some newer software which utilizes the SQL Desktop Engine (MSDE) and some older 16-bit application. I want to export the database from the old app to the newer software. If I can export the data from the old app to an excel spreadsheet, can I import that into the MDF file? Is this at all possible? What kind of other software would I need? This is my first experience in this area.

Thanks

View 4 Replies View Related

Export Data To Excel Through SP

Apr 3, 2008

Hi All

Thanks in Advance...

I want to export data from SQL Server 2005 to Excel. Now I made a sp for that, since the SP will return multiple result sets and all data (all resultsets) need to export through SSIS package.

Can anyone please help me out how to do this.

Michael Raj. A



MichaelRaj Arokiyasamy

View 2 Replies View Related

Export SQL Data To Excel

Apr 18, 2008

Hello People,

I am Seema a first user in this forum. I am new to SQl and also .net. I am having a timesheet database where in I need to export those datas to Excel using stored procedure.

If I use a DBMail it works fine, but the issue is I am not able to pass parameters in query. For example the following code works fine,
DECLARE @tableHTML NVARCHAR(MAX) ;

SET @tableHTML =
N'<H1>GSR Time Entry</H1>' +
N'<table border="1">' +
N'<tr><th>Employee ID</th><th>Employee Name</th>' +
N'<th>Age</th>' +

CAST ( ( SELECT td = E.Employeeid, '',
td = E.Name, '',
td = E.Age, ''
FROM Employee as E
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='trial@yahoo.com',
@profile_name = 'seema',
@subject = 'Time Sheet',
@body = @tableHTML,
@body_format = 'HTML'

This code works fine, but in the same query when I try to send parameters say 'dept' it doesnot recognise the database at all.

I need a stored procedure which will export SQL data to an excel and I should be able to pass parameters and get data, i.e., I should be able to use the following query and the result should be exported in Excel.

ALTER procedure [dbo].[sp_email]
(
@dept varchar (50),
@exp varchar (50)
)
as
begin
select EmployeeID,Name,Age
from employee where DepartmentName= @dept and
Experience = @exp

Please help and any help and reply will be very much appreciated

Regards,

Seema



seema

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved