Post reply

Open / Save Excel

  • January 9, 2008 at 7:42 am

    #98779

    Hi,

    I wonder if anyone can help me out with a (hopefully) simple problem. I'm not a programmer but a relatively savvy end user, so please bear with me.

    I have a need to open a non standard Excel file and then Save it with another name, before starting another import routine. There is no need to make any changes, as the simple act of saving the excel file corrects that issue I am having with the import routine.

    It struck me that since SQL server was installed on the server, it was an ideal tool to do this with as Excel is not installed. I have looked at SSIS, and managed to get halfway there (4 columns are not imported for some reason).

    Is this the right way to go, or is there a much simpler solution ?

    Any pointers would be much appreciated.

    Thanks in advance

  • January 21, 2008 at 5:42 am

    #770101

    Try this:

    [font="Courier New"]-- Make a new file from the template

    DECLARE @CMD VARCHAR (1000), @Dir VARCHAR(50), @Today VARCHAR(10), @Spreadsheet VARCHAR(100), @return_status int

    IF OBJECT_ID('tempdb..#Output') IS NOT NULL

    DROP TABLE #Output

    CREATE TABLE #Output (CopyResult VARCHAR(500))

    SET @Today = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 102), '.', '')

    SET @Dir = '\\ixnuflawLRS1\D$\SLA Reporting\'

    SET @Spreadsheet = @Dir + 'SLA_Dashboard' + @Today + '.xls'

    SET @CMD = 'COPY "' + @Dir + 'Templates\SLA_DashboardTemplate.xls" "' + @Spreadsheet + '"'

    INSERT INTO #Output EXEC MASTER..XP_CMDSHELL @CMD

    IF (SELECT COUNT(*) FROM #Output WHERE CopyResult = ' 1 file(s) copied.') = 0

    BEGIN

    PRINT 'FILE NOT COPIED'

    RETURN

    END[/font]

    Cheers

    ChrisM

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

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