SQL Server Performance

how to convert Excel File to ZIP file in CMD/TSQL

Discussion in 'SQL Server 2005 General Developer Questions' started by Sandy, Apr 29, 2009.

  1. Sandy New Member

    Hi All,
    I am facing a issues with Email sending with an attachment by using Stored Procedure.
    I am having a excel report which need to send to user every day end.
    This is excel reports is generated by dynamically for error report by using Tsql command.
    Now before it was sending it manually now we have to make it automated by email system from sql server.
    I am able to send the Excel report by attachment but it is getting corrupted and deleted by company firewall but I have checked with Zip file its working fine.
    I am trying to convert it to ZIP file and send it from TSQL.I have used Compress and Makecab command but unable to make it...
    Can you please help me how to convert excel to zip file in CMD/TSQL.??
  2. satya Moderator

    I believe this is not a SQL problem or something you should continue from SQL Server side, due to the nature of the file extension and corporate policies the ZIP file will be restricted to send.
    Rather why not send the excel file itself as the attachment, do you see any great deal of size difference between Excel & ZIP files?
  3. Sandy New Member

    No its deleting from if its a normal Excel file. but Zip files is allowing in the firewall..Thats why I need to convert to Zip file..
    Satya, now I got a alternative way to make this....
    What Exactly I am doing now. In net I got a DLL and Exe named 7z which can make zip for me...
    Now it’s working temporary. But I need the CMD or tsql to make the zip rather than 3rd party tool.
  4. satya Moderator

    YOu could achieve such task with XP_CMDSHELL:
    Declare @cmdstr varchar(8000),@zippedfile varchar(50),@srcfile varchar(50)
    Set @cmdstr = '<ZIP PROG>.... '+ @zippedfile +' ' + @srcfile
    exec master..xp_cmdshell @cmdstr
    As you are using SQL 2005 why not take help of SSIS in this case, see http://www.cozyroc.com/ssis/zip-task tool that will help you to achieve ZIP task to plug into SSIS package.
  5. Sandy New Member

    Thanks Satya,
    Exactly...That’s what I am doing...
    But can we achieve by using this...sp_OACreate, sp_OADestroy, sp_OAMethod...?
  6. cbarnhart New Member

    Wouldn't the best approach be a CLR function? Your task is similar to what I want to do for Reading and Updating Excel 2007 reports (.xslx). A workbook can be renamed with .zip extension, unzipped and then individual worksheets (parts) can be read or updated with XML. Then, I guess you have to re-package, change the extension back to ".xslx" and then you're finished. I want to accomplish all this in a SQL stored procedure. Requires SQL 2005, of course.
    If you are using Excel 2007 documents, you wouldn't really need to compress the files, since it is done automatically...just change the file extension so that it doesn't get blocked.

Share This Page