title: Importing Tab Delimited Text File with Multiline Cells (to Excel or Access)
body: <p>The tab delimited text file that i am trying to import looks like this (4 fields, Field 3 is multiline)... and about 100,000 rows of data:</p>
  
  <p>Below is just a sample... but please give a general solution where the multiline text could be in multiple fields... like say fld3, fld6 and fld7 of a total 10 fields.</p>
  
  <pre><code>Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1
  Field 3.2
  Field 3.3&lt;tab&gt;Field 4
  Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1
  Field 3.2
  Field 3.3&lt;tab&gt;Field 4
  Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1
  Field 3.2
  Field 3.3&lt;tab&gt;Field 4
  </code></pre>
  
  <p>But, actually when it was was exported to the above text file from the database... it had 3 lines of actual... that looked like this... 4 Fields. <strong>Field 3 is multiline.</strong></p>
  
  <pre><code>Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1(CR)Field 3.2(CR)Field 3.3&lt;tab&gt;Field 4
  Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1(CR)Field 3.2(CR)Field 3.3&lt;tab&gt;Field 4
  Field 1 &lt;tab&gt; Field 2 &lt;tab&gt; Field 3.1(CR)Field 3.2(CR)Field 3.3&lt;tab&gt;Field 4
  </code></pre>
  
  <p>where  is Tab, and (CR) is carriage return. When importing the tab delimited text file into Excel, i want all 3 lines in Field 3 (Field 3.1, Field 3.2 and Field 3.3) be in one cell, but multiline. So basically i want excel to ignore carriage return with in Field 3. How do i do that???</p>
  
  <p>For your info... <strong>The text fields are NOT in double quotes</strong>... Please tell me what is the best way to convert the plain text tab delimited text file to Excel 4 columns like this:</p>
  
  <p><img src="https://i.stack.imgur.com/rbziQ.jpg" alt="Excel File"></p>
  
  <p>does anyone know of a solution. it would be greatly appreciated.</p>
  
  <p><strong>Additional Comment</strong></p>
  
  <p>Please note that what I have is only the resulting Text File... as shown on the very top of this message.... with text "Field 3.2" in Line 2, "Field 3.3" and "Field 4" in Line 3 with  separating them.</p>
  
  <p>Also... not necessarily Field 3 will be always multiline... it may be multiline with 2, 3 or more lines... or even nothing or single line. Field 3 will never have a  with in itself.</p>
  
  <p><strong>Additional Comments with Sample File for someone to Test - Update 25Jun2013 09:05 UTC</strong></p>
  
  <p><em>pls note copy paste will not work.. as tabs are lost.. you will have to add it yourselves.</em></p>
  
  <p>File with Quotes around the multiline field:</p>
  
  <pre><code>f1hdr   f2hdr   f3hdr
  f11 "f12
  part of f12"    f13
  f21 "f22part of f22
  part f22
  part f22
  part f22"   f23
  f31 "f32aaa"    f33
  f41 "f42bbb"    f43
  </code></pre>
  
  <p>File without Quotes around the multiline field:</p>
  
  <pre><code>f1hdr   f2hdr   f3hdr
  f11 f12
  part of f12 f13
  f21 f22part of f22
  part f22
  part f22
  part f22    f23
  f31 f32aaa  f33
  f41 f42bbb  f43
  </code></pre>
  
  <p>Open the file with quotes in Excel... Open File Dialog... Hold Shift... and Click Open.. show the file nicely with multiline.</p>
  
  <p><img src="https://i.stack.imgur.com/vjScY.jpg" alt="capture 2 with quotes"></p>
  
  <p>But opening the file without quotes.. doesn't work.. it breaks.</p>
  
  <p>I have no idea why holding shift works. I knew of this from here: <a href="https://stackoverflow.com/questions/443204/import-multiline-csv-files-into-excel-internationally?rq=1">Import multiline csv files into Excel internationally</a></p>
  
  <p>Now.. it still remains to be answered...</p>
  
  <p>1) How to add the quotes around the text file in easier fashion before importing to Excel... Why Shift Open works? What if I want to control each delimited columns using text import wizard?</p>
  
  <p>2) How to add the quotes around the fields by default in SQL Server 2015. This is in addition to above Q1. We still need a solution where we can't export the file again. But, for any new export.. someone could user the answer from Q2.</p>
  
  <p>3) Any other methods which may be even simpler?</p>