title: How to import into CSV file into SQL Server with different number of column?
body: <p>We would like to explore again to import using SQL SSIS 2005 where the CSV file have a different number of column separated for each rows. ie.</p>
  
  <p>Let say first 3 rows:</p>
  
  <pre><code>"1", "FirstName1", "LastName1"
  "2", "FirstName2", "LastName2"
  "3", "FirstName3", "LastName3"
  </code></pre>
  
  <p>4-5 rows:</p>
  
  <pre><code>"210", "Crows st", "Melbourne", "VIC"
  "212", "Cr st", "Melbourne", "VIC"
  </code></pre>
  
  <p>others:</p>
  
  <pre><code>"100", "Activities 1"
  "101", "Activities 2"
  </code></pre>
  
  <p>The only approaches that we did is that if the data has less than 1 million line, we can import into Excel and then export back as CSV or tab delimited and import to SQL Server.
  Alternatively, if it's more than 1 million records we can import into Ms Access 2007/2010 and import back into SQL (Assume that the data is no longer than 2GB). These type of solutions work nicely!!! </p>
  
  <p>Is there any solution directly to SQL? We are still using Ms SQL Server 2005 BTW. The reason that we asked due to there is a limitation on the size of Ms Access which is 2GB and we have data is more than 2GB !!! Surely, we can split this file based on number of lines but we found very messy.</p>
  
  <p>Looking forward for the input.</p>
  
  <p>Thanks </p>