SQL命令在到达大型csv文件结束之前终止 [英] SQL command terminates before reaching end of large csv file

问题描述

我有一个很大的csv文件，其中包含很多需要分析的数据(〜6M行).我想连接到文件并对它运行SQL命令，以仅返回我感兴趣的分析数据.我正在编写的VBA在Excel 2010中.

I have a large csv file with lots of data that I need to be able to analysis (~6M rows). I want to connect to the file and run SQL command against it to return only the data I'm interested in analysing. The VBA I'm writing is in Excel 2010.

当csv文件中的行数为<时，一切正常. 4432669.如果csv文件中的行比该行多，该命令似乎在文件中的该点处终止，并仅返回该点之前的内容.没有引发错误(CN.Errors)，我首先虽然可能是命令超时，但是当我增加它时，它没有任何区别.我还检查了不同的csv文件，以防万一该行包含损坏的数据，但没有运气. Recordset maxrecords设置为0(无限制).

Everything works fine when the number of rows in the csv file is < 4432669. When the csv file has more rows than this, the command seem to terminate at that point in the file and just returns what ever it has found up to that point. No Error is thrown (CN.Errors), I first though it might be that the command timedout but when I increase this it made no difference. I also checked with different csv files just incase that row contained corrupted data, but no luck. Recordset maxrecords is set to 0 (No limit).

我尝试使用Microsoft.Jet.OLEDB.4.0；和driver = {Microsoft Text Driver(* .txt; * .csv)};在连接字符串中，两者的行为与上述相同.

I've tried using Microsoft.Jet.OLEDB.4.0; and driver={Microsoft Text Driver (*.txt; *.csv)}; in the connectionstring, both behave the same as described above.

这是我正在使用的测试代码，

Here is test code I'm using,

Dim CN As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Err As ADODB.Error

providerstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\cygwin\home\MarkM\csvimport\filtertest4\;" & _
"Extended Properties=" & Chr(34) & "text;HDR=Yes;FMT=Delimited" & Chr(34) & ";"
CN.ConnectionString = providerstr
CN.Mode = adModeRead
CN.CommandTimeout = 900
CN.Open

RS.Open "SELECT exCode FROM 5M_MBP1R04.csv", CN, adOpenStatic, adLockReadOnly
RS.MoveLast
MsgBox "Number of rows = " & RS.RecordCount

    For Each Err In CN.Errors
        strError = "Error #" & Err.Number & vbCr & _
            "   " & Err.Description & vbCr & _
            "   (Source: " & Err.Source & ")" & vbCr & _
            "   (SQL State: " & Err.SqlState & ")" & vbCr & _
            "   (NativeError: " & Err.NativeError & ")" & vbCr
        If Err.HelpFile = "" Then
            strError = strError & "   No Help file available"
        Else
            strError = strError & _
               "   (HelpFile: " & Err.HelpFile & ")" & vbCr & _
               "   (HelpContext: " & Err.HelpContext & ")" & _
               vbCr & vbCr
        End If

        Debug.Print strError
    Next

真的很感谢您的帮助，因为我现在完全陷入困境.

Really appreciate any help as I'm completely stuck now.

BR的标记.

推荐答案

由于CursorType，您可能超出了内存限制.尝试将其更改为adOpenForwardOnly

Perhaps you are exceeding a memory constraint due to the CursorType. Try changing it to adOpenForwardOnly

这是描述光标类型的MSDN页面. https://msdn.microsoft. com/en-us/library/windows/desktop/ms681771(v = vs.85).aspx

Here is the MSDN page describing Cursor Types. https://msdn.microsoft.com/en-us/library/windows/desktop/ms681771(v=vs.85).aspx

这篇关于SQL命令在到达大型csv文件结束之前终止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！