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 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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply