title: Why does this VBA code for SQL queries on CSV files work intermittently?
body: <p>A very simple query function that takes in a path for a source CSV file and a SQL statement as a string (I'm also transposing the data from the VBA function),</p>
  
  <pre><code>Public Function RunQuery(FilePath As String, SQLStatement As String)
  
      Dim Conn As New ADODB.Connection
      Dim RecSet As New ADODB.Recordset
  
      With Conn
          .Provider = "Microsoft.Jet.OLEDB.4.0"
          .ConnectionString = "Data Source=" &amp; FilePath &amp; ";" &amp; _
          "Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1"""
      End With
  
      Conn.Open
      RecSet.Open SQLStatement, Conn
      RecSet.MoveFirst
      RunQuery = RecSet.GetRows()
  
      Conn.Close
      Set RecSet = Nothing
      Set Conn = Nothing
  
  End Function
  </code></pre>
  
  <p>This code works intermittently against a CSV files, some data is retrieved correctly and some is not.</p>
  
  <p>An example are these two CSV files - <a href="http://pastebin.com/1HdYZnn9" rel="nofollow">Abbreviated</a> and <a href="http://pastebin.com/dLHyuFNb" rel="nofollow">Full</a>. The following SQL query works perfectly on the Abbreviated file, but returns #VALUE on the Full file.</p>
  
  <pre><code>SELECT birthYear FROM [File]
  </code></pre>
  
  <p>It's definitely not a data limit/size issue as the Full file only contains 1800 rows. I'm completely befuddled and would appreciate any thoughts/pointers.</p>
  
  <p>Incidentally if I wrap up the logic into a Sub rather than a UDF then it works perfectly without any errors,</p>
  
  <pre><code>Public Sub RunQuerySub()
  
  Dim Conn As New ADODB.Connection
  Dim RecSet As New ADODB.Recordset
  Dim FilePath As String
  FilePath = ActiveSheet.Range("Path")
  
  With Conn
      .Provider = "Microsoft.Jet.OLEDB.4.0"
      .ConnectionString = "Data Source=" &amp; FilePath &amp; ";" &amp; _
      "Extended Properties=""text;HDR=Yes;FMT=Delimited;IMEX=1"""
  End With
  Dim SQLStatement As String
  SQLStatement = ActiveSheet.Range("SQL")
  
  Conn.Open
  RecSet.Open SQLStatement, Conn
  ActiveSheet.Cells(1, 8).CopyFromRecordset RecSet
  
  Conn.Close
  Set RecSet = Nothing
  Set Conn = Nothing
  
  End Sub
  </code></pre>
  
  <p>I am very confused, and would appreciate any pointers.</p>