title: Using Excel to create a CSV file with special characters and then Importing it into a db using SSIS
body: <p>Take this XLS file</p>
  <p><img src="https://i.stack.imgur.com/JT3Wf.jpg" alt="enter image description here" /></p>
  <p>I then save this XLS file as CSV and then open it up with a text editor. This is what I see:</p>
  <pre><code>Col1,Col2,Col3,Col4,Col5,Col6,Col7
  1,ABC,&quot;AB&quot;&quot;C&quot;,&quot;D,E&quot;,F,03,&quot;3,2&quot;
  </code></pre>
  <p>I see that the double quote character in column C was stored as <code>AB&quot;&quot;C</code>, the column value was enclosed with quotations and the double quote character in the data was replaced with 2 double quote characters to indicate that the quote is occurring within the data and not terminating the column value. I also see that the value for column G, <code>3,2</code>, is enclosed in quotes so that it is clear that the comma occurs within the data rather than indicating a new column. So far, so good.</p>
  <p>I am a little surprised that all of the column values are not enclosed by quotes but even this seems reasonable OK when I assume that EXCEL only specifies column delimieters when special characters like a commad or a dbl quote character exists in the data.</p>
  <p>Now I try to use SQL Server to import the csv file. Note that I specify a double quote character as the Text Qualifier character.</p>
  <p><img src="https://i.stack.imgur.com/dyYxr.jpg" alt="Insert figure 2" /></p>
  <p>And a command char as the Column delimiter character. However, note that SSIS imports column 3 incorrectly,eg, not translating the two consecutive double quote characters as a single occurence of a double quote character.</p>
  <p><img src="https://i.stack.imgur.com/anU00.jpg" alt="enter image description here" /></p>
  <p>What do I have to do to get Excel and SSIS to get along?</p>
  <p>Generally people avoid the issue by using column delimiter chactacters that are LESS LIKELY to occur in the data but this is not a real solution.</p>
  <p>I find that if I modify the file from this</p>
  <pre><code>Col1,Col2,Col3,Col4,Col5,Col6,Col7
  1,ABC,&quot;AB&quot;&quot;C&quot;,&quot;D,E&quot;,F,03,&quot;3,2&quot;
  </code></pre>
  <p>...to this:</p>
  <pre><code>Col1,Col2,Col3,Col4,Col5,Col6,Col7
  1,ABC,&quot;AB&quot;C&quot;,&quot;D,E&quot;,F,03,&quot;3,2&quot;
  </code></pre>
  <p>i.e, removing the two consecutive quotes in column C's value, that the data is loaded properly, however, this is a little confusing to me. First of all, how does SSIS determine that the double quote between the B and the C is not terminating that column value? Is it because the following characters are not a comma column delimiter or a row delimiter (CRLF)? And why does Excel export it this way?</p>
  <p>According to <a href="https://www.rfc-editor.org/rfc/rfc4180#page-2" rel="nofollow noreferrer">Wikipedia</a>, here are a couple of traits of a CSV file:</p>
  <ol start="6">
  <li><p>Fields containing line breaks (CRLF), double quotes, and commas
  should be enclosed in double-quotes.  For example:</p>
  <p>&quot;aaa&quot;,&quot;b CRLF
  bb&quot;,&quot;ccc&quot; CRLF
  zzz,yyy,xxx</p>
  </li>
  <li><p>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:</p>
  <p>&quot;aaa&quot;,&quot;b&quot;&quot;bb&quot;,&quot;ccc&quot;</p>
  </li>
  </ol>
  <p>However, it looks like SSIS doesn't like it that way when importing. What can be done to get Excel to create a CSV file that could contain ANY special characters used as column delimiters, text delimiters or row delimiters in the data?  There's no reason that it can't work using the approach specified in Wikipedia,. which is what I thought the old MS DTS packages used to do...</p>
  <p><strong>Update:</strong></p>
  <p>If I use Notepad change the input file to</p>
  <pre><code>Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8
  &quot;1&quot;,&quot;ABC&quot;,&quot;AB&quot;&quot;C&quot;,&quot;D,E&quot;,&quot;F&quot;,&quot;03&quot;,&quot;3,2&quot;,&quot;AB&quot;&quot;C&quot;
  </code></pre>
  <p>Excel reads it just fine</p>
  <p><img src="https://i.stack.imgur.com/Jw16G.jpg" alt="enter image description here" /></p>
  <p>but SSIS returns</p>
  <pre><code>The preview sample contains embedded text qualifiers (&quot;). The flat file parser does not support embedding text qualifiers in data. Parsing columns that contain data with text qualifiers will fail at run time.
  </code></pre>