Export Data to Excel

Question

Post reply

Export Data to Excel

durgapras

Old Hand

Points: 350
More actions
February 23, 2008 at 9:59 pm

#179329

Hi,
I am currently running the following query to export Data from a table to a ExcelSheet(test.xls)
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls;',
'SELECT * FROM [Sheet1$]') select * from SYSCUST_AUDIT
Everything is working fine.NP's so far.
The thing what I am really looking for is,Is there a way where I can write a query and it should dynamically create a Excel sheet and export the data from the table.
Now I have manually created test.xls.
What I am looking for is ,Write some query which dynamically creates
test_'Date'.xls and export the data from the table.So after 5 days,My c:folder should have following files
test_'date1'.xls
test_'date2'.xls
test_'date3'.xls
test_'date4'.xls
test_'date5'.xls

Viewing 15 posts - 1 through 15 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

The info in the following article should do it for ya...

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

faheemlatif Mr or Mrs. 500 Points: 516 More actions · Answer 2

Hi,

Please try this 4 more information please visit :

http://technet.microsoft.com/en-us/library/ms162802.aspx

--------------------------------------------------------------------------------------

EXEC master.dbo.xp_cmdshell 'bcp fahtest.dbo.test out "d:\test.txt" -T -c', no_output

--------------------------------------------------------------------------------------

Best Regards
Faheem latif
Senior Database Architect
Genie Technologies (Pvt.) Ltd.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Yeaup... I agree... BCP will work, as well.

Big problem with both methods (xp_CmdShell/BCP and sp_OA*) is that they both require some extraordinary privs (SA) to execute. That's not necessarily a bad thing for production batch jobs that are under tight control of the DBA or a "DBA system" like the SQL Job Scheduler.

You can always call BCP from a Cmd prompt. Better yet, why not make spreadsheet that loads the data using external data ranges?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 4

Hi,

I agree with above posts on using BCP to export the data. This gives more flexibility and faster way to export the data.

You may want to check out the below link on some of the valuable tips on export to excel.

http://www.mssqltips.com/tip.asp?tip=1202

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

durgapras Old Hand Points: 350 More actions · Answer 5

Thanks for the replies.

Faheem latif & VijayKrishna I am still not sure how BCP is going to dynamically generate excel files (On the Date basis).

Jeff,I am still trying to analyze the SP's you provided.I just started on the DB work.Excuse me,If I said anything out of Ignorance.

I appreciate all your Inputs.Thanks you

Anipaul SSC-Insane Points: 24681 More actions · Answer 6

Read these links also

http://www.mssqltips.com/tip.asp?tip=1202

http://support.microsoft.com/kb/319951

durgapras Old Hand Points: 350 More actions · Answer 7

After a little reading,I found out xp_commandshell would accomplish my task.Thanks for all your Inputs.But I am struck up at this point

Declare @filename varchar(40)

DECLARE @cmd VARCHAR(255)

set @filename = 'Date' + convert(varchar,getdate(),112)

SET @cmd = 'copy C:\SoundPub\Batch\durga.xls C:\SoundPub\Batch_Orig\'+@filename+'.xls'

After declaring those,I tried both of these and both of them are failing

xp_cmdshell 'copy C:\SoundPub\Batch\durga.xls C:\SoundPub\Batch_Orig\'+@filename+'.xls'

xp_cmdshell @cmd

Error:

Incorrect syntax near 'xp_cmdshell'.

Not sure what is wrong.It works great,If I run this one statement

xp_cmdshell 'copy C:\SoundPub\Batch\durga.xls C:\SoundPub\Batch_Orig\durga1.xls'

Any Input is greatly appreciated.

Thanks

durgapras Old Hand Points: 350 More actions · Answer 8

My Bad.

The mistake what did was,I did not give 'exec' before the SP.

Declare @filename1 varchar(40)

Declare @filename2 varchar(255)

Declare @filedate varchar(40)

Declare @copyfile varchar(255)

set @filename1 = 'C:\SoundPub\Batch\durga.xls'

set @filedate = 'Date' + convert(varchar,getdate(),112)

set @filename2 = 'C:\SoundPub\Batch_Orig\'+@filedate+'.xls'

set @copyfile='copy'+' '+@filename1+' '+@filename2

exec xp_cmdshell @copyfile

But while executing it as single line it did not complain.How come?

xp_cmdshell 'copy C:\SoundPub\Batch\durga.xls C:\SoundPub\Batch_Orig\Date20080226.xls'

It works great even without EXEC !!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

It just works that way... you can execute just about any stored procedure the same way. It's like doing a PRINT to do an immediate calculation.

But if you mix it with other commands in a script or proc, then it has to be "proper" with the Exec and all...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durgapras Old Hand Points: 350 More actions · Answer 10

Appreciate all yours Inputs so far.I could successfully create .xls files on Date basis now.But the problem I am having currently is,I have to convert these .xls files into .csv (Not Microsoft Excel Comma Seperated Values File).This .csv file should be a TRUE .csv file with the following format.

'abc',123,'beem'

'def',456,'ram'

Something like that....

Thanks in advance

Adam Haines SSC-Insane Points: 23217 More actions · Answer 11

All of your requriements can be met by creating an SSIS package. SSIS is a very robust solution. It gives you the option to create delmited text files, with text qualifiers. Also, SSIS allows you to dynamically change the file name according to the current date.

I would recommend you create the package in SSIS and make a scheduled job to run it.

My blog: http://jahaines.blogspot.com

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 12

In that case, you can either use BCP with a format file that uses \" as part of the delimiter ("," would be "\",\"" in the delimiter column, for example) or you can use OSQL in conjunction with QUOTENAME... BCP will run the fastest but it will take some time to setup the format file correctly. Once setup, though, it absolutely screams.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

durgapras Old Hand Points: 350 More actions · Answer 13

Thanks for the quick replies.I am struck at this point.

I am running this command.It works perfect on the windows command line

bcp "select CUSTOMER_ID,Email1 from R4W_001.dbo.SYSCUST_AUDIT where Email1='abc@sarasu.com'" queryout "C:\SoundPub\EmailList\durga2.csv" -T -c

But when I run it from the query analyzer

xp_cmdshell 'bcp "select CUSTOMER_ID,Email1 from R4W_001.dbo.SYSCUST_AUDIT where Email1='abc@sarasu.com'" queryout "C:\SoundPub\EmailList\durga2.csv" -T -c'

It gives following error

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'abc@sarasu.com'.

Thanks in advance

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 14

You need to double up on the embedded single quotes...

xp_cmdshell 'bcp "select CUSTOMER_ID,Email1 from R4W_001.dbo.SYSCUST_AUDIT where Email1=''abc@sarasu.com''" queryout "C:\SoundPub\EmailList\durga2.csv" -T -c'

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)