July 17, 2008 at 4:39 pm
Hi all. I am quite new in SQL and I need help.
What I am trying to do is to export the Data from tables to Excel fiel
I creted the script:
exec xp_cmdshell 'copy C:\reports\2007.xls C:\reports\out\2007.xls'
GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\reports\out\2007.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView
This works perfectly.
Then I created separately this script (to copy the file with dynamic current date:
declare
@currentdate varchar(10), @cmd varchar(4000)
select @currentdate = convert(varchar(8),getdate(),112)
set @cmd = 'copy "C:\reports\2007.xls" "C:\reports\out\Export-' + @currentdate + '.xls"'
exec master..xp_cmdshell @cmd
And now I need to add to this current date named file the export procedure and I am not able to do it. Can anyone help me to write the INSERT INTO OPENROWSET code to be able to export the data into file with current date.
Thanks a lot inn advance.
July 20, 2008 at 2:27 am
Hi Friend,
You could use the EXPORT wizard in sql server management studio to export data to excel!!:) Its a very user friendly method. I just had a chance to export data to excel today! and it worked perfect
July 20, 2008 at 11:17 am
From BOL
The arguments of OPENROWSET and OPENDATASOURCE do not support variables. The arguments must be specified as string-literals. If variables must be passed in as arguments, a query string that contains the variables can be constructed dynamically and executed by using the EXECUTE statement.
Help URL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/6a506b36-1ebb-4b52-aee0-549e12ae2d67.htm
July 20, 2008 at 12:18 pm
levani (7/17/2008)
Hi all. I am quite new in SQL and I need help.What I am trying to do is to export the Data from tables to Excel fiel
I creted the script:
exec xp_cmdshell 'copy C:\reports\2007.xls C:\reports\out\2007.xls'GO
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\reports\out\2007.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView
This works perfectly.
Then I created separately this script (to copy the file with dynamic current date:
declare@currentdate varchar(10), @cmd varchar(4000)
select @currentdate = convert(varchar(8),getdate(),112)
set @cmd = 'copy "C:\reports\2007.xls" "C:\reports\out\Export-' + @currentdate + '.xls"'
exec master..xp_cmdshell @cmd
And now I need to add to this current date named file the export procedure and I am not able to do it. Can anyone help me to write the INSERT INTO OPENROWSET code to be able to export the data into file with current date.
Thanks a lot inn advance.
Why don't you change it around - create the Excel file as a default file and then rename the file with the current date?
Another option - as someone else posted - would be to use SSIS to create the excel spreadsheet. Using SSIS allows you to dynamically create the output file using variables. You can find more information at http://www.sqlis.com.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 20, 2008 at 7:08 pm
Using dynamic SQL as:
DECLARE @cmd VARCHAR(4000)
DECLARE @currentdate VARCHAR(10)
select @currentdate = convert(varchar(8),getdate(),112)
SET @cmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\reports\out\Export-' + @currentdate + '.xls' + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
results in @cmd being
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\reports\out\Export-20080720.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Just tack on your select statement
SET @Cmd = @Cmd + ' select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView'
then use
EXECUTE @cmd
And I think you will have what you want.
July 20, 2008 at 11:43 pm
Thanks to everyone for reply. I will try all sugested options and let you know the result.
July 20, 2008 at 11:51 pm
bitbucket (7/20/2008)
Using dynamic SQL as:DECLARE @cmd VARCHAR(4000)
DECLARE @currentdate VARCHAR(10)
select @currentdate = convert(varchar(8),getdate(),112)
SET @cmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\reports\out\Export-' + @currentdate + '.xls' + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
results in @cmd being
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\reports\out\Export-20080720.xls;HDR=YES',
'SELECT * FROM [Sheet1$]')
Just tack on your select statement
SET @Cmd = @Cmd + ' select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView'
then use
EXECUTE @cmd
And I think you will have what you want.
The only question here is about export to file name Export-20080720.xls. Is there any way to indicate dynamicly the file name?
I am working on some application and I have to insert the code that dynamically will generate the file name.
Thank you
July 21, 2008 at 8:34 am
bitbucket has already created the filename dynamically. The type in bold is what the @currentdate becomes when run.
-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:
July 21, 2008 at 10:01 am
Thanks for reply.
When I run this:
DECLARE @cmd VARCHAR(4000)
DECLARE @currentdate VARCHAR(10)
select @currentdate = convert(varchar(8),getdate(),112)
SET @cmd = 'INSERT INTO OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''Excel 8.0;Database=C:\reports\out\Export-' + @currentdate + '.xls' + ';HDR=YES'',
''SELECT * FROM [Sheet1$]'')'
SET @Cmd = @Cmd + ' select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView'
EXECUTE @cmd
I get the following error
Msg 203, Level 16, State 2, Line 10
The name 'INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\reports\out\Export-20080721.xls;HDR=YES',
'SELECT * FROM [Sheet1$]') select ItemGroupID, SalesDescription
from dbo.ItemAccountEntityView' is not a valid identifier.
What I am doing wrong?
Thank you for help. I am really new in this and I want to understand it.
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply