title: Importing a large .csv into Excel,
body: <p>I was trying to import a large .csv file into Excel. The file has close to 4 million rows and 329 columns so it far exceeded Excel's <strike>65536</strike> 1M row limit. I found a VB script online to import text files into Excel where once the row limit was exceeded the script would create a new worksheet till the total rows in all worksheets equal the total rows in the original text file.</p>
  
  <pre><code>Sub ImportLargeFile()
  'Imports text file into Excel workbook using ADO.
  'If the number of records exceeds 65536 then it splits it over more than one sheet.
  
  Dim strFilePath As String, strFilename As String, strFullPath As String
  Dim lngCounter As Long
  Dim oConn As Object, oRS As Object, oFSObj As Object
  
  'Get a text file name
  strFullPath = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
  
  If strFullPath = "False" Then Exit Sub  'User pressed Cancel on the open file dialogue
  
  'This gives us a full path name e.g. C:\temp\folder\file.txt
  'We need to split this into path and file name
  Set oFSObj = CreateObject("SCRIPTING.FILESYSTEMOBJECT")
  
  strFilePath = oFSObj.GetFile(strFullPath).ParentFolder.Path
  strFilename = oFSObj.GetFile(strFullPath).Name
  
  
  'Open an ADO connection to the folder specified
  Set oConn = CreateObject("ADODB.CONNECTION")
  oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
             "Data Source=" &amp; strFilePath &amp; ";" &amp; _
             "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
  
  Set oRS = CreateObject("ADODB.RECORDSET")
  
  'Now actually open the text file and import into Excel
  oRS.Open "SELECT * FROM " &amp; strFilename, oConn, 3, 1, 1
  While Not oRS.EOF
      Sheets.Add
      ActiveSheet.Range("A1").CopyFromRecordset oRS, 65536
  Wend
  
  oRS.Close
  oConn.Close
  
  End Sub
  </code></pre>
  
  <p>However, when I run it in Excel 2007 I get the following error:</p>
  
  <pre><code>Run-time error '-2147217900 (80040e14)':
  
  Syntax error in FROM clause.
  </code></pre>
  
  <p>On clicking debug it points to this line</p>
  
  <pre><code>    oRS.Open "SELECT * FROM " &amp; strFilename, oConn, 3, 1, 1
  </code></pre>
  
  <p>I have non-existent VB skills so any help here would be appreciated. </p>
  
  <p>Thanks!</p>
  
  <p>Edit: I found this <a href="http://support.microsoft.com/kb/193514" rel="nofollow noreferrer">http://support.microsoft.com/kb/193514</a> but this doesn't work in this case, in fact, I am not even sure where to place the square brackets. Any place I put the brackets in that line, Excel refuses to give me the run option.</p>
  
  <p>Edit 2: I tried running this on a different machine which just has a lot more juice in it to make memory not an issue. This computer also has Office 2013 on it (no Office 365 with it though). I ran the same macro on that and it gave the following error.</p>
  
  <pre><code>Run-time error '3706':
  Provider cannot be found. It may not be properly installed.
  </code></pre>
  
  <p>When I click Debug it points to this line</p>
  
  <pre><code>oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" &amp; _
         "Data Source=" &amp; strFilePath &amp; ";" &amp; _
  -&gt;     "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
  </code></pre>