SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Export to Excel
 New Topic  Reply to Topic
 Printer Friendly
Previous Page | Next Page
Author Previous Topic Topic Next Topic
Page: of 30

cris_0007
Starting Member

3 Posts

Posted - 12/23/2011 :  12:53:21  Show Profile  Reply with Quote
HI all, this the first time that I see this code.... related proc_generate_excel_with_columns.

I want to know what I need to change to get the excel file with the extension XLSX .. I do not want to be open the file to resave for XLSX


I am very new in this...

regards
Cristina

Edited by - cris_0007 on 12/23/2011 16:28:02
Go to Top of Page

ROLASHISH
Starting Member

India
3 Posts

Posted - 01/12/2012 :  01:10:08  Show Profile  Reply with Quote
Hi Madhivanan,

I am trying to use the sqlserver to excel query

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=D:\testing.xls;',
'SELECT * FROM [SheetName$]') select * from SQLServerTable

but i am getting an error..
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.

Thanks
Ashish


Edited by - ROLASHISH on 01/12/2012 01:12:08
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 01/12/2012 :  01:13:20  Show Profile  Reply with Quote
What you need to do is describe clearly in the error message. Just follow it.


KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 01/12/2012 :  03:32:27  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cris_0007

HI all, this the first time that I see this code.... related proc_generate_excel_with_columns.

I want to know what I need to change to get the excel file with the extension XLSX .. I do not want to be open the file to resave for XLSX


I am very new in this...

regards
Cristina


You need to have get engine 12.0 installed

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

andyc209
Starting Member

5 Posts

Posted - 01/16/2012 :  05:37:22  Show Profile  Reply with Quote
I have my stored proc that inserts data into an existing excel file (which is a copy of a master file) however when i insert the data all the data values change to text and the numeric fields change to text. On the master IC_master.xls file that is copied i tried setting all the columns to date and numeric and this does not work - how can i get this to format things correctly.

What it is doing is looping through a table of companies and creating a file for each. The excel file already has headings in relating to the 'as x' in the openrowset query.

code is below

DECLARE @SOL AS NVARCHAR(500)
DECLARE PRAC_LIST SCROLL CURSOR FOR

select COMPANYNAME from TBL_COMPANY GROUP BY COMPANYNAME

OPEN PRAC_LIST
FETCH NEXT FROM PRAC_LIST
INTO
@SOL
WHILE @@FETCH_STATUS = 0
BEGIN

-- BEGIN CREATING THE EXCEL FILE --


DECLARE @FILEDAY AS VARCHAR(50)
DECLARE @FILEMONTH AS VARCHAR(50)
DECLARE @FILEYEAR AS VARCHAR(50)
SET @FILEDAY = DATEPART("DD",GETDATE())
SET @FILEMONTH = DATEPART("MM",GETDATE())
SET @FILEYEAR = DATEPART("YYYY",GETDATE())

DECLARE @SOL2 AS NVARCHAR(500)
SET @SOL2 = REPLACE(@SOL,' ','_')
DECLARE @SOLID AS INT
SELECT
@SOLID = COMPID
FROM TBL_COMPANY
WHERE
COMPANYNAME = @SOL

EXEC sp_configure 'show advanced option', '1';
RECONFIGURE;

EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

DECLARE @FILENAME AS VARCHAR(255)
SET @FILENAME = 'COPY /Y D:\IC_MASTER.xls /B D:\IC\IC_'+ @SOL2 +'.XLS'
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
EXEC master..xp_cmdshell @FILENAME

-- ====== WRITE ROWS INTO EXCEL FILE ====== ---


DECLARE @xlFileName nvarchar(255)
SET @xlFileName=N'D:\IC\IC_'+ @SOL2 +'.XLS'

DECLARE @SQL as nvarchar(2000)
SET @SQL=N'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel 8.0;HDR=YES;DATABASE='+@xlFileName+''',''Select * from [DATA$]'')SELECT dbo.TBL_CASESTATUS.SOLREFText AS reference, dbo.TBL_CASESTATUS.casekey AS icref, dbo.TBL_CASESTATUS.ClientFullNameCompanyName AS client, CAST(dbo.TBL_CASESTATUS.FileOpenedDate AS DATETIME) AS Daterecv, dbo.TBL_CASESTATUS.PI1DateDate AS Datebillstart, dbo.TBL_CASESTATUS.PI2DateDate AS datebilldraft, dbo.TBL_CASESTATUS.ICClientApproveDate AS clientapproval, dbo.TBL_CASESTATUS.PI3DateDate AS BilltoTP, dbo.TBL_CASESTATUS.ICTPApproveDate AS tpapproval, dbo.TBL_CASESTATUS.ICBillAgreedDate AS billagreed, dbo.TBL_CASESTATUS.ICWIPValueValue AS wip, dbo.TBL_CASESTATUS.ICTotalBillValue AS icbill, dbo.TBL_CASESTATUS.ICAgreedBillValueValue AS recovered, dbo.TBL_CASESTATUS.ClientSolicitorFullNameCompanyName as solname , dbo.TBL_COMPANY.COMPID as solid FROM dbo.TBL_CASESTATUS INNER JOIN dbo.TBL_COMPANY ON dbo.TBL_CASESTATUS.ClientSolicitorFullNameCompanyName = dbo.TBL_COMPANY.COMPANYNAME WHERE (dbo.TBL_COMPANY.COMPID = ' + CAST(@SOLID as varchar(11)) + ')'
EXEC(@SQL)



FETCH NEXT FROM PRAC_LIST
INTO
@SOL
END
CLOSE PRAC_LIST
DEALLOCATE PRAC_LIST

Edited by - andyc209 on 01/16/2012 05:39:52
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/17/2012 :  04:09:30  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
andyc209, you need to have EXCEL sheet with proper formatting before copying data to it

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

huum
Starting Member

Canada
9 Posts

Posted - 04/22/2012 :  21:44:09  Show Profile  Reply with Quote
Exprting query to Excel with headers through sql script. Any one?


[k Shaz---Torontonian]
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 04/22/2012 :  21:51:12  Show Profile  Reply with Quote
quote:
Originally posted by huum

Exprting query to Excel with headers through sql script. Any one?


[k Shaz---Torontonian]


please dont cross post

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 04/24/2012 :  05:21:02  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by huum

Exprting query to Excel with headers through sql script. Any one?


[k Shaz---Torontonian]


Goto the first page and see the point 5 of the original code

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

dmgerber
Starting Member

USA
1 Posts

Posted - 06/21/2012 :  16:04:18  Show Profile  Reply with Quote
SQL Server 2008 R2
Excel 2010

I want to be able to Update a specific column in an EXCEL spreadsheet using SQL Code when the EXCEL spreadsheet is opened.

I have created the spreadsheet and filled it with data using Microsoft Query.

The update needs to be keyed on the value of the First Column of the spreadsheet which is equal to a field in the SQL code below.

SELECT TB.[PartNumber]
,TB.[WhereUsed]
,TB.[SkidCount]
,TB.[SSQty]
,TB.[F5]
,P.[QTY_ON_HAND]
,TOT_LOCATIONS_QOH = SUM(PART_LOCATION.QTY)
,P.[QTY_ON_ORDER]
FROM [SSRSdgerber].[dbo].[TRACOM_BOXES] TB
LEFT JOIN bpc.dbo.PART_LOCATION PART_LOCATION
ON TB.[PartNumber] = PART_LOCATION.PART_ID
LEFT JOIN bpc.dbo.PART P
ON TB.[PartNumber]=P.ID
INNER JOIN bpc.dbo.PRODUCT CAT
ON P.PRODUCT_CODE=CAT.CODE
WHERE PART_LOCATION.QTY>0
AND PART_LOCATION.WAREHOUSE_ID='TRACM'
GROUP BY TB.[PartNumber], PART_LOCATION.WAREHOUSE_ID
,TB.[WhereUsed]
,TB.[SkidCount]
,TB.[SSQty]
,TB.[F5]
,P.[QTY_ON_HAND]
,P.[QTY_ON_ORDER]

Thanks in advance.
Darlene
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/24/2012 :  06:01:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I don't know how to do the update when the file is opened. However you can use UPDATE statement something like this

UPDATE excel
Set col=table.col from
(select * from OPENROWSET(....)) as excel inner join your_table as table
on excel.key_col=table.key_col

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 07/24/2012 06:01:38
Go to Top of Page

cbjones
Starting Member

USA
4 Posts

Posted - 07/24/2012 :  06:39:01  Show Profile  Reply with Quote
If anyone is still following this older thread, can you tell me how to export the results of a query to Excel with headers (column names as determined by the query)?

Thank you!
cbj
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/24/2012 :  07:04:20  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by cbjones

If anyone is still following this older thread, can you tell me how to export the results of a query to Excel with headers (column names as determined by the query)?

Thank you!
cbj


Go to the first page of the thread and see point 5. If it is one time job, right click on the result panel of query analyser and choose Copy with headers

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

niquefx
Starting Member

USA
1 Posts

Posted - 08/24/2012 :  09:55:44  Show Profile  Reply with Quote
Hi madhivanan, is there any way to dynamically change excel sheet names using openrowset?
Go to Top of Page

rentadeveloper
Starting Member

Germany
1 Posts

Posted - 08/27/2012 :  12:48:15  Show Profile  Reply with Quote
@Disclaimer: I work for the company who build DbTransfer.

We have a tool exactly build for the purpose of exporting from sql to excel and back:
http://www.rent-a-developer.de/Products/DbTransfer

And apart from all the other options it works very easy and fast.

Regards,
Dave
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 09/03/2012 :  10:22:51  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by niquefx

Hi madhivanan, is there any way to dynamically change excel sheet names using openrowset?

Yes like this

Create Procedure ExportToExcel(@fileName varchar(200))
as
Declare @sql varchar(1000)
Set @sql ='
Insert into dbo.ImportTemp([c1], [c2], [c3] , [c4])
Select * FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database='+@fileName+';HDR=YES'',
''SELECT [DESTINATION] , [COUNTRYCODE], [AREA CODES],[PRICE]FROM [test$]'')
'
Exec (@sql)


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/14/2013 :  03:11:47  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thanks madhivanan

-------------To export data to new EXCEL file with heading(column names), create the following procedure


but I get an error

EXEC proc_generate_excel_with_columns 'ats342', 'dbo.absher','c:\temp\my.xls'



kmkmmm
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/14/2013 :  07:01:01  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
IF @GO=1
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END

Msg 2714, Level 16, State 1, Procedure ParseXmlPersonInfo, Line 155
There is already an object named '# WS_DEPTS1' in the database.



kmkmmm
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/14/2013 :  07:05:46  Show Profile  Reply with Quote
quote:
Originally posted by pascal_jimi

IF @GO=1
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE1
END
ELSE
BEGIN
SELECT A INTO #WS_DEPTS1 FROM TABLE2
END

Msg 2714, Level 16, State 1, Procedure ParseXmlPersonInfo, Line 155
There is already an object named '# WS_DEPTS1' in the database.



kmkmmm


Instead of creating table in each step, create it once and then do insertion inside if statement

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

pascal_jimi
Posting Yak Master

Azerbaijan
150 Posts

Posted - 06/14/2013 :  07:16:40  Show Profile  Visit pascal_jimi's Homepage  Reply with Quote
thank you visakhm

but

can examples ?

kmkmmm
Go to Top of Page
Page: of 30 Previous Topic Topic Next Topic  
Previous Page | Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.59 seconds. Powered By: Snitz Forums 2000