none
Using SSIS to auto-convert Excel to tab-delimited text file

    Question

  • Hi!

    I've had some issues trying to load from a macro/encrypted Excel spreadsheet but have had success when i manually save the file as a tab-delimited text file.  Is there a way to automatically save the Excel file as a tab-delimited text file using SSIS?

    Thanks!

    Elizabeth


    Elizabeth Priddy
    Thursday, September 01, 2011 6:58 PM

Answers

  • Hi!

    I've had some issues trying to load from a macro/encrypted Excel spreadsheet but have had success when i manually save the file as a tab-delimited text file.  Is there a way to automatically save the Excel file as a tab-delimited text file using SSIS?

    Thanks!

    Elizabeth


    Elizabeth Priddy

    Hi Elizabeth,

    If you can use third-party solutions, check CozyRoc:

    Excel Source Plus

    Excel Task

    You can open Excel-encrypted workbooks without problems. The library doesn't require installation of Microsoft Office or other drivers and components.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by Eileen Zhao Monday, September 05, 2011 8:59 AM
    Thursday, September 01, 2011 8:29 PM

All replies

  • Hello Elizabeth,

    There is no automated way.

    Excel is a desktop class of an application, whereas SSIS is server based, although everywhere I go I see MS Office installed on servers...

    I would use a Script Task with some MS Office interop code plugged in to convert. Eve though I have not tried this myself (I try to convince that we never ever process Excel sheets on servers because these packages break often [users easily mess spreadsheets up]).

    Found a relatively close example here: http://www.developerfusion.com/code/4405/converting-an-excel-file-to-tab-delimited-format/

    Perhaps you will need to deal with the encryption.


    Arthur My Blog
    By: TwitterButtons.com
    Thursday, September 01, 2011 7:25 PM
    Moderator
  • Alas, dealing with the encryption is also a scipting thing... Thank you for your response!  Right now we're debating whether or not it's worth the development hours/cost when it's a single cell of data needing to be entered into SQL once a month.
    Elizabeth Priddy
    Thursday, September 01, 2011 7:32 PM
  • Hi!

    I've had some issues trying to load from a macro/encrypted Excel spreadsheet but have had success when i manually save the file as a tab-delimited text file.  Is there a way to automatically save the Excel file as a tab-delimited text file using SSIS?

    Thanks!

    Elizabeth


    Elizabeth Priddy

    Hi Elizabeth,

    If you can use third-party solutions, check CozyRoc:

    Excel Source Plus

    Excel Task

    You can open Excel-encrypted workbooks without problems. The library doesn't require installation of Microsoft Office or other drivers and components.


    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    • Marked as answer by Eileen Zhao Monday, September 05, 2011 8:59 AM
    Thursday, September 01, 2011 8:29 PM