none
(SOLVED) SSIS export to CSV - delimiter/qualifier issue

    Question

  • I have an SSIS package which exports HTML descriptions along with other content from my DB to a CSV file which is qualified by quotation marks (").  The content itself has commas & quotation marks, so I am finding that when it reaches a row with both, it cannot delimit them accurately & Excel opens them in incorrect rows.

    Is there a way to make it work without stripping the content from the db?  IE is there a standard escape character before a comma?  Or better yet, what is the most common/standard workaround?


    • Edited by Soig Tuesday, November 27, 2012 7:58 PM Thread solved
    Tuesday, November 27, 2012 4:29 PM

Answers

  • Just use a different delimiter for the file, say a tilde ~ or pipe |.

    Excel is equally capable of opening however character delimited files.

    If producing a CSV is of utter importance then consider cleansing the file after the export, how high quality it would be perhaps questionable, but I needed it that one day myself and posted a blog entry about this using either a Script Transform or Power Shell:

    http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspx or

    http://geekswithblogs.net/Compudicted/archive/2011/09/22/how-to-remove-quotes-and-replace-the-delimiter-in-a.aspx


    Arthur My Blog

    • Marked as answer by Soig Tuesday, November 27, 2012 7:43 PM
    Tuesday, November 27, 2012 6:15 PM
    Moderator

All replies

  • Just use a different delimiter for the file, say a tilde ~ or pipe |.

    Excel is equally capable of opening however character delimited files.

    If producing a CSV is of utter importance then consider cleansing the file after the export, how high quality it would be perhaps questionable, but I needed it that one day myself and posted a blog entry about this using either a Script Transform or Power Shell:

    http://geekswithblogs.net/Compudicted/archive/2011/09/19/ssis-how-to-remove-occasional-quotes-and-replace-the-column.aspx or

    http://geekswithblogs.net/Compudicted/archive/2011/09/22/how-to-remove-quotes-and-replace-the-delimiter-in-a.aspx


    Arthur My Blog

    • Marked as answer by Soig Tuesday, November 27, 2012 7:43 PM
    Tuesday, November 27, 2012 6:15 PM
    Moderator
  • Thanks!  I can change the delimiter to a pipe without an issue.  I just wanted to do things as close to normal or industry standard as possible so as to accommodate the people who might receive the file.
    Tuesday, November 27, 2012 7:56 PM