Post reply

Export Data to Excel

  • 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

  • February 23, 2008 at 11:09 pm

    #781411

    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)

  • February 25, 2008 at 5:29 am

    #781507

    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.

  • February 25, 2008 at 6:17 am

    #781514

    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)

  • February 25, 2008 at 7:50 pm

    #781838

    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


  • February 25, 2008 at 10:17 pm

    #781852

    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

  • February 25, 2008 at 11:44 pm

    #781863

    Read these links also

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

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

  • February 26, 2008 at 1:29 pm

    #782226

    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

  • February 26, 2008 at 5:40 pm

    #782311

    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 !!

  • February 26, 2008 at 6:25 pm

    #782319

    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)

  • March 1, 2008 at 9:02 pm

    #784198

    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

  • March 1, 2008 at 10:05 pm

    #784203

    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

  • March 1, 2008 at 10:07 pm

    #784204

    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)

  • March 2, 2008 at 12:37 am

    #784212

    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

  • March 2, 2008 at 9:17 am

    #784229

    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)

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

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