title: How to fix the embedded text qualifier issue while exporting data to CSV flat file?
body: <p>###RFC 4180:</p>
  <p><strong><a href="https://www.rfc-editor.org/rfc/rfc4180" rel="nofollow noreferrer">RFC 4180</a></strong> defines <em><code>Common Format and MIME Type for Comma-Separated Values (CSV) Files</code></em>. One of the requirements of the <em><code>RFC 4180</code></em> is stated as below. This is the point <em><code>#7</code></em> in the RFC link.</p>
  <pre><code>If double-quotes are used to enclose fields, then a double-quote
  appearing inside a field must be escaped by preceding it with
  another double quote.  For example:
  
  &quot;aaa&quot;,&quot;b&quot;&quot;bb&quot;,&quot;ccc&quot;
  </code></pre>
  <p>###SQL Server 2000:</p>
  <p><strong><code>DTS Export/Import Wizard</code></strong> in <em><code>SQL Server 2000</code></em> seems to conform to the above mentioned standards even though the RFC 4180 itself seem to have been published only on <em>October 2005</em>. I am using the below stated <em>SQL Server 2000</em> version.</p>
  <pre><code>Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
  May  3 2005 23:18:38 
  Copyright (c) 1988-2003 Microsoft Corporation
  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
  </code></pre>
  <p>###SQL Server 2012:</p>
  <p><strong><code>SQL Server Import and Export Wizard</code></strong> in <em><code>SQL Server 2012</code></em> does not export the data from table to CSV file according to the standard defined in RFC 4180. I am using the below stated <em>SQL Server 2012</em> version.</p>
  <pre><code>Microsoft SQL Server 2012 - 11.0.2316.0 (X64) 
  Apr  6 2012 03:20:55 
  Copyright (c) Microsoft Corporation
  Enterprise Edition (64-bit) on Windows NT 6.1 &lt;X64&gt; (Build 7601: Service Pack 1) (Hypervisor)
  </code></pre>
  <p>###Issue Simulation:</p>
  <p>Here is a sample that I ran in both <em>SQL Server 2000</em> and <em>SQL Server 2012</em>. I ran the below query to create a table and insert few records. The <code>ItemDesc</code> column has data with double-quotes in it. My intention is to export the data from both these SQL Server versions using their in-built export data wizard and compare the generated CSV files.</p>
  <pre><code>CREATE TABLE dbo.ItemInformation(
      ItemId nvarchar(20) NOT NULL,
      ItemDesc nvarchar(100) NOT NULL
  ) 
  GO
  
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100338754', 'Crown Bolt 3/8&quot;-16 x 1&quot; Stainless-Steel Hex Bolt');
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('202255836', 'Simpson Strong-Tie 5/8&quot; SSTB Anchot Bolt');
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100171631', 'Grip-Rite #11 x 1-1/2&quot; Electro-Galvanized Steel Roofing Nails');
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('202210289', 'Crown Bolt 1/2&quot; x 3&quot; &quot;Zinc-Plated&quot; Universal Clevis Pin');
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('100136988', 'Tapcon 3/16&quot; x 1-3/4&quot; Climaseal Steel &quot;Flat-Head&quot; Phillips Concrete Anchors (75-Pack)');
  INSERT INTO dbo.ItemInformation (ItemId, ItemDesc) VALUES ('203722101', 'KwikTap 3/16&quot; x 2-1/4&quot; &quot;Flat-Head&quot; Concrete Screws (100-Pack)');
  GO
  </code></pre>
  <p>On the <em><code>DTS Export/Import Wizard</code></em> in <em><code>SQL Server 2000</code></em>, I used the below settings to export the data to CSV file. I saved the file under the name <code>SQLServer2000_ItemInformation.csv</code>.</p>
  <p><img src="https://i.stack.imgur.com/fdFSY.png" alt="DTS Export/Import Wizard" /></p>
  <p>On the <em><code>SQL Server Import and Export Wizard</code></em> in <em><code>SQL Server 2012</code></em>, I used the below settings to export the data to CSV file. I saved the file under the name <code>SQLServer2012_ItemInformation.csv</code>.</p>
  <p><img src="https://i.stack.imgur.com/toryR.png" alt="SQL Server Import and Export Wizard - Choose a destination" /></p>
  <p><img src="https://i.stack.imgur.com/MvbUx.png" alt="SQL Server Import and Export Wizard - Configure Flat File Destination" /></p>
  <p>Here is the comparison between the two files using Beyond Compare. The left side contains the file generated by <code>SQL Server 2000</code> and the right side contains the file generated by <code>SQL Server 2012</code>. You can notice that the left side file from <code>SQL Server 2000</code> contains additional double-quotes to compensate the embedded quotes in the data column. This conforms to the standard specified in <code>RFC 4180</code> but it is clearly missing from the file generated by <code>SQL Server 2012</code></p>
  <p><img src="https://i.stack.imgur.com/ofh2w.png" alt="File Comparison" /></p>
  <p>###Searches on the web:</p>
  <p>I searched for this bug on the web and found the following links. Following are the bug reports on Microsoft Connect. All these issues seem to be related to importing a file but nothing about exporting data. All these bugs have been closed as <code>Fixed</code>.</p>
  <ul>
  <li><a href="https://connect.microsoft.com/SQLServer/feedback/details/282396/ssis-flat-file-parser-does-not-read-column-delimiters-embedded-in-text-data" rel="nofollow noreferrer"><em>SSIS flat file parser does not read Column delimiters embedded in text data</em></a></li>
  <li><a href="http://connect.microsoft.com/SQLServer/feedback/details/560592/flat-file-connection-manager-not-handling-text-delimiters-in-csv-files" rel="nofollow noreferrer"><em>Flat File Connection Manager not handling Text Delimiters in CSV Files</em></a></li>
  <li><a href="https://connect.microsoft.com/SQLServer/feedback/details/267529/embedded-quotes-in-flat-file-import-fails" rel="nofollow noreferrer"><em>Embedded quotes in Flat File Import fails</em></a></li>
  <li><a href="https://connect.microsoft.com/SQLServer/feedback/details/540056/bug-flat-file-connection-manager-multiple-character-text-qualifier-does-not-load-all-data" rel="nofollow noreferrer"><em>BUG: Flat File Connection Manager: multiple-character text qualifier does not load all data</em></a></li>
  </ul>
  <p>Below post on MSDN blog states that changes have been made in SQL Server 2012 with respect to <code>Flat file source supports embedded qualifiers and a variable number of columns per row</code></p>
  <ul>
  <li><a href="http://blogs.msdn.com/b/mattm/archive/2011/07/12/ssis-what-s-new-in-sql-server-denali.aspx?PageIndex=2#comments" rel="nofollow noreferrer"><em>SSIS - What’s New in SQL Server Denali</em></a></li>
  </ul>
  <p>Another post on MSDN blog states the same under the section <code>Embedded Qualifiers</code>.</p>
  <ul>
  <li><a href="http://blogs.msdn.com/b/mattm/archive/2011/07/17/flat-file-source-changes-in-denali.aspx" rel="nofollow noreferrer"><em>Flat File Source Changes in Denali</em></a></li>
  </ul>
  <p>###Workaround that I know of:</p>
  <p>I know a workaround to fix the issue by writing a query that would replace all double-quotes (<strong><code>&quot;</code></strong>) in my column data with two double-quotes (<strong><code>&quot;&quot;</code></strong>) so that the exported file will end up with correct embedded qualifier data. This would avoid pulling the data directly from the table as it is.</p>
  <p>###My questions:</p>
  <ul>
  <li><p>I don't know if this issue has been truly fixed in <code>SQL Server 2012</code>. Has this issue been fixed only for <em><code>importing</code></em> files that have embedded text qualifiers and <strong><code>not</code></strong> for <em><code>exporting</code></em> data to CSV?</p>
  </li>
  <li><p>Probably, I am clearly doing something wrong and missing the obvious. Could someone please explain to me what I am doing wrong here?</p>
  </li>
  </ul>
  <p>###Microsoft Connect:</p>
  <p>I have submitted a bug report on Microsoft Connect website to get their feedback. Here is the link to the bug report. If you agree that this is a bug, please visit the below link to vote up on <code>Microsoft Connect</code> website.</p>
  <p><a href="https://connect.microsoft.com/SQLServer/feedback/details/778980/embedded-text-qualifier-during-export-to-csv-does-not-conform-to-rfc-4180#details" rel="nofollow noreferrer"><em>Embedded text qualifier during export to CSV does not conform to RFC 4180</em></a></p>