查询管道定界文件不返回所有记录 [英] Query Pipe Delimited File Not Returning all Records

问题描述

我正在使用带有ADODB的VBA查询管道分隔的文本文件。我已经尝试过使用ACE和JET引擎，但是结果是相同的，而且是不正确的。我在目录中保存了一个 schema.ini文件，告诉引擎该文件是用管道分隔的。但是，查询完成后，当我可以在文本文件中看到数据时，我的数据会移动列和其他字段为空白。我使用 CopyFromRecordset 方法将结果传输到我的工作簿中。有人可以在我的代码中看到一些东西吗？还是有人碰到这个？

I am querying a pipe delimited text file using VBA with ADODB. I have tried using both the ACE and JET engines, but the results are the same, and not correct. I saved a "schema.ini" file in the directory telling the engine that the file is pipe delimited. When the query finishes though, I have data that shifted columns and other fields that are blank when I can see the data in the text file. I use the CopyFromRecordset method to transfer the results to my workbook. Can anyone see something off in my code? Or has anyone run into this?

我的连接字符串：

With Conn
   .Provider = "Microsoft.ACE.OLEDB.12.0"
   .ConnectionString = "Data Source=" & strPath & ";" & _
      "Extended Properties=""text;HDR=YES;FMT=Delimited"""
   .Open
End With

strPath的值：

Value of "strPath":

strPath = "H:\Folder\"

SQL字符串：

strSQL = "SELECT * FROM " & strFile & ";"

数据传输：

With ThisWorkbook
    With .Sheets("CashFlows")
        .Range("a2").CopyFromRecordset Rst
        With .Range("a1")
            For i = 0 To Rst.Fields.Count - 1
                .Offset(0, i) = Rst.Fields(i).Name
            Next i
        End With
    End With
End With

我从未遇到过ADO（Ace或Jet）的问题，我经常使用它们。但是，我以前从未将它们用于管道分隔文件。

I have never had issues with ADO (either Ace or Jet) and I use them quite frequently. I have not, however, used them for pipe delimited files before.

如果我的解释不明确或需要更多代码，请发表评论。

Please leave a comment if I am unclear in my explanation, or if you require more code.

编辑
请参见下面的管道删除文本文件中的两行。第一行正常解析。第二个删除 651111100和 654444475。最奇怪的是，当我直接在Excel中打开它们并使用文本作为列时，这些解析完全没问题。

EDIT See the two lines out of the pipe delinted text file below. The first line parses normally. The second drops the "651111100" and "654444475". The strangest thing is that these parse totally fine when I open them in Excel directly and use text to columns.

08/31/2015|000|000|Recital #5546|0000000012|88885463|123334563
08/31/2015|000|000|DII #7412|651111100|654444475|00000326541

我重新检查了一下，问题仅在于它正在删除列，而不是它在四处移动。抱歉，有任何混淆。

I re-checked, and the issue is only that it is dropping columns, not that it is shifting them around. Sorry for any confusion.

更新

我在以下位置进行了查找/替换文本文件，并放入 ^而不是 |。然后，我再次尝试运行此命令，但遇到了同样的问题！

I did a find/replace in the text file and put in "^" instead of "|". I then tried to run this again and I ran into the same issue!

推荐答案

虽然我仍然不确定问题可能是什么，以下是可用的解决方案：

While I am still not sure what the problem might, the following is a usable solution:

Option Explicit

Public Sub ImportTextFile()

Dim lngRowNumber As Long
Dim strImportFile As String
Dim intNextFreeFile As Integer
Dim strOneImportLine As String
Dim arySplitLine As Variant

With Application
    .ScreenUpdating = False
    .Calculation = xlManual
    .EnableEvents = False
End With

intNextFreeFile = FreeFile
strImportFile = "C:\path\to\the\TextFile.csv"
lngRowNumber = 1

Open strImportFile For Input Access Read As #intNextFreeFile

Do While Not EOF(intNextFreeFile)
    Line Input #intNextFreeFile, strOneImportLine
    arySplitLine = Split(strOneImportLine, "|")
    Sheets(1).Cells(lngRowNumber, 1).Value2 = arySplitLine
    lngRowNumber = lngRowNumber + 1
Loop

Close #intNextFreeFile

With Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
    .EnableEvents = True
End With

End Sub

在我的计算机上进行的测试在12秒内完成了80MB文件中的一百万行的导入。
虽然我确信有更好的方法可以胜过此解决方案，但您可能希望同时尝试一下。

The test-run on my computer completed the import for one million rows in a 80MB file within 12 seconds. While I am sure that there are better ways to do this which outperform this solution you might want to give it a try in the meantime.

这篇关于查询管道定界文件不返回所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！