title: Excel VBA process csv string into array
body: <p>I have csv string (utf-8) obtained via a http download.</p>
  
  <p>Depending on the situation the data in the string could contain a different number of columns, but each individual time a string is processed it will contain the same number of columns and be contiguous.  (the data will be even).</p>
  
  <p>The string could contain any number of rows.</p>
  
  <p>The first row will always be the headings.</p>
  
  <p><strong>String fields will be encased in double quotes and could contain commas, quotes and newlines</strong>.</p>
  
  <p>quotes and double quotes inside a string are escaped by doubling so "" and ''</p>
  
  <p>In other words this is a well formed csv format.  Excel through it's standard file open mechanism has no problem formatting this data.</p>
  
  <p>However I want to avoid saving to a file and then opening the csv as I will need to process the output in some cases, or even merge with existing data on a worksheet.</p>
  
  <p>(<strong>Added the following information via edit</strong>)
  <em>The Excel Application will be distributed to various destinations and I want to avoid if possible potential permissions issues, seems that writing nothing to disk is a good way to do that</em></p>
  
  <p>I am thinking something like the following pseudo:</p>
  
  <pre><code>rows = split(csvString, vbCrLf)  'wont work due to newlines inside string fields?
  
  FOREACH rows as row
      fields = split(row, ',')     'wont work due to commas in string fields?
  ENDFOR
  </code></pre>
  
  <p>Obviously that cant handle the fields containing special tokens.  </p>
  
  <p>What is a solid way of parsing this data?</p>
  
  <p>Thanks</p>
  
  <h2>EDIT 13/10/2012 Data Samples</h2>
  
  <p>csv as it would appear in notepad  (note not all line breaks will be \r\n some could be \n)</p>
  
  <pre><code>LanguageID,AssetID,String,TypeID,Gender
  3,50820,"A string of natural language",3,0
  3,50819,"Complex text, with comma, "", '' and new line
  all being valid",3,0
  3,50818,"Some more language",3,0
  </code></pre>
  
  <p>The same csv in Excel 2010 - opened from shell (double click - no extra options)
  <img src="https://i.stack.imgur.com/difaj.png" alt="enter image description here"></p>