Post reply

update excel sheet with results of stored procedures

  • November 16, 2004 at 11:59 am

    #60440

    Hi,

    I have a stored procedure which returns certain results. I want to update the excel sheet with the results of this stored procedure. I was looking for a way to include a script inside stored procedure itself so that it will update the excel sheet. Else i would like to write a seperate activex script to do this.

    Can anyone point me how to do this? I do not know how to write active x scripts.

    Thanks,

    Sridhar!!

  • November 16, 2004 at 6:49 pm

    #530389

    You can output the results to an Excel file easily enough. If you're not too worried about the layout of the information, export the results to a tab-delimited text file that has an .xls extension and Excel will open it like a normal Excel file.

    If you need the formatting, it would be best to create a template file that hass all the formatting. Then before you export the data create a copy of the template and export the data to the copy.

    --------------------
    Colt 45 - the original point and click interface

  • November 17, 2004 at 7:02 am

    #530472

    Thanks Phillcart, I will try using the templates.

  • November 17, 2004 at 12:41 pm

    #530540

    Hi PhillCart,

    I have tried using templates. It is working fine. But now I have another problem. My excel template has an image at the header, and below the image there is a text saying how many pages there are to print in the sheet. below that I need to display the data. The first column is of datetime. But I'm not to display it as datetime. Below is the structure

    _______________________

    Image

    _________________________

    4 Pages to Print

    Period Item Store

    I'm not able the display the period values as datetime. they are displayed as varchar. Is there a way to get around this?

    Thanks,

    Sridhar!!

  • November 17, 2004 at 3:52 pm

    #530562

    So is the display of the period data a formatting requirement, or do you get an error when you extract the data?

    In Excel, if you need to display any non-text data as text, just prefix it with a single quote.

    --------------------
    Colt 45 - the original point and click interface

  • November 17, 2004 at 10:14 pm

    #530575

    Attach the Excel Sheet and then you can use it like a table.  In order to attach the Excel Sheet use the following :

    CREATE PROCEDURE attach_excel

        @file_path nvarchar(4000),

        @alias_name nvarchar(128)

    as

    exec sp_addlinkedserver

     @server = @alias_name

        ,  @srvproduct = 'microsoft excel workbook'

        ,  @provider = 'microsoft.jet.oledb.4.0'

        ,  @datasrc = @file_path

        ,  @provstr = 'excel 8.0'

    exec sp_addlinkedsrvlogin @alias_name, 'false'

    exec attach_excel  'c:\myxl.xls', 'xltable'

    go

    set ansi_nulls on

    go

    set ansi_warnings on

    go

    select * from xltable...table1

    update xltable...table1 set f2 = 3 where a = a and f3 = 23

    In myxl.xls  you should qualify the area as table1. 

     

     

  • November 18, 2004 at 7:43 am

    #530666

    Hi Srinikrish and PhillCart,

    Thanks for the reply. In the excel sheet, how should I qualify the area as table1? Where should I mention the datatypes of the columns? In the excel sheet? And my excel sheet will have my company image at the top. so can i mention like from where(record number) it should start loading the record?

    Thanks,

    Sridhar!!

  • November 22, 2004 at 9:45 pm

    #531163

    Hi Sridhar

    In the name box drop down after selecting the row/col specify the name as table1.  If you have 4 columns in your table then choose 4 cells e.g. a4 to d5 and in the name box enter the name as table1.  Make a4 to D4 as column headers similar to the table column names.  Then you can use this as database table to update/insert/delete in excel.

    Regards

    S.Krishnan

  • November 23, 2004 at 7:54 am

    #531217

    Hi S.Krishnan,

    It worked. Thank you so much. I appreciate your help.

    Sridhar!!

  • December 6, 2004 at 4:59 pm

    #532768

    Hi,

    Now I got a new problem. I am able to update the excel sheets using the name ranges in excel sheet. Now I have to do like this. I have to format some columns so that they will be right aligned, and some columns to be center aligned and some columns to be left aligned in the excel sheet. I tried setting the format of each cell accordingly. but it is ignoring that and it is taking the previous record's(Column Names which are center aligned) format and updating the column values according to that format. And I want to differentiate between Column header and column data using some color for the row of Column Names. But when i execute the DTS package, it is giving the column header color to all the data field cells also. Is there a way to set different formatting for column headers and data fields either in excel or DTS? I have got a temporary solution. The solution is I am inserting the dummy record which is according to my format. It works fine but everytime I need to delete the dummy record after update. IS there a way for this? Or atleast Can I delete a row from excel permanently(means Delete Entire Row Option) using DTS?

    Thanks,

    Sridhar!!

Viewing 10 posts - 1 through 9 (of 9 total)

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