Post reply

Export to SQL

  • October 20, 2008 at 2:28 am

    #383146

    Hi mates,

    I was given a task to export data to an Excel sheet.

    There is a pre defined template which has some columns hidden, some have formulas and there is a chart for the sheet.

    I have created a SSIS package to export data. Its exporting data to excel but at the end of current data.

    What my aim is ...

    1. Create new excel file having the same template(must visible all formulas and chart)

    2. Newly created excel will have the name generated dynamically(from the parameter to this SSIS package)

    3. Before exporting, old data should be cleared.

    4. If the new excel file with the name already exists, should overwrite.

    How can I achieve this!

    Thanks in advance

  • October 20, 2008 at 4:24 am

    #887038

    Why do you export data from sql to Excel.

    you should create an excel file with the template you required:

    and by using the option "Import External Data" from the Tools menu, select the data source SQL and supply the query, you will get the resulted dataset at the position in excel sheet where you want without creating the same sheet again and again.

    next time you only have to copy and paste the excel workbook and change the sql query

    kshitij kumar
    kshitij@krayknot.com
    www.krayknot.com

  • October 20, 2008 at 4:38 am

    #887045

    Hi,

    Thanks for the reply. Your suggestion could be fair if I am doing it manually. But, my task is to be done as a job in sql server agent on a schedule basis.

    thanks

  • October 21, 2008 at 2:58 am

    #887482

    a2zwd

    Are you forced to use SSIS?

    krayknot has a point. The procedure can be automated.

    Maybe I am out on weak ice then I say I don't like SSIS for

    tasks involving Excel. I prefere to use VBA inside Excel.

    You can achive exactly what you describe with that approach.

    //Gosta

  • October 21, 2008 at 3:03 am

    #887483

    What about this approach...

    - create and (re-)fill a table with your data using the scheduled job

    - link the Excel-sheet with the correct template to the table

    - (automatically) refresh the data every time you open Excel

    You'll put new data in the table every time the scheduled job has run. Excel loads the data from the table thus always gets the most recent data.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 21, 2008 at 3:17 am

    #887488

    Try the following way:

    INSERT

    INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',

    'Excel 8.0;Database=C:\DOWNLOADS\testing.xls;',

    'SELECT Name, ID FROM [Sheet1$]'

    )

    SELECT top 10 [Name], Id FROM dbo.sysobjects with (nolock)

    GO

    For More Information navigate to URL: http://www.mssqltips.com/tip.asp?tip=1202

    create one sp with the above template and put into schedule.

    🙂

  • October 21, 2008 at 5:10 am

    #887516

    Also refer

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

    Madhivanan

    Failing to plan is Planning to fail

Viewing 7 posts - 1 through 6 (of 6 total)

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