使用二进制访问读取从.dat文件中提取所有数据(应用程序定义的错误1004) [英] Extracting all data from a .dat file using Binary Access Read (Application Defined error 1004)

问题描述

Blockquote

Blockquote

有一些.dat格式的文件，这些文件包含一些有价值的信息，但是它们可能很大，尝试在记事本中打开每个文件并提取我需要的信息根本没有效率，因为这需要很长的记事本是时候打开每个文件了.我遇到了这个二进制访问读取"功能，该功能显然可以打开大文件，并允许您非常快速地读取它们.

have some files in the .dat format,these files contain some valuable information however they can be quite big, trying to open each file in notepad and extracting the information I need is not efficient at all, as it takes notepad a long time to open each file. I have come across this Binary Access Read function which apparently opens large files and allows you to read them very quickly.

Sub ReadEntireFileAndPlaceOnWorksheet()
  Dim X As Long, FileNum As Long, TotalFile As String, FileName As String, Result As Variant, Lines() As String, rng As Range, i As Long, used As Range, MyFolder As String
  
  With Application.FileDialog(msoFileDialogFolderPicker)
    .Show
    MyFolder = .SelectedItems(1)
End With
FileName = Dir(MyFolder & "\*.*")
Do While FileName <> ""
  FileName = Dir()
  FileNum = FreeFile
  Open FileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
  Close #FileNum
  Lines = Split(TotalFile, vbNewLine)
  ReDim Result(1 To UBound(Lines) + 1, 1 To 1)
    For X = 1 To UBound(Result)
    Result(X, 1) = "'" & Lines(X - 1)
  Next
  Set used = Sheet1.Cells(1, Sheet1.Columns.Count).End(xlToLeft).Columns
  Set rng = used.Offset(0, 1)
  rng.Resize(UBound(Result)) = Result
  Loop
  
End Sub

.问题本质上是我想拥有的所有东西，所以它可以就此结束，但是如果要继续这样做的话，这不是很实际，那么在解决方案上有什么想法吗?

. The problem is essentially I have all I want so it can end there but if this is going to keep happening its not very practical, any ideas on a solution?

脚本的结果如下:

MDF     3.00    TGT 15.0
Time: 04:47:24 PM
Pre-trigger Time: 20[s]
Recording Duration: 00:01:39
Database: dpdtoolp
Experiment: __140910_RB
Workspace: 13
Devices: ETKC:1,THMM(25362),THMM(25361),ADMM(448),CalcDev
Program Description: Module_ivupd2
WP: _AWD_1
RP: _AWD
§@
98 okt
Data: 14E410_299
PU Off

=
E1æ?b¡ClYDZ0C
Eä>
?C­ÛêB
?C
"CÝåÆB
×#<س½C`C"¯„D-+@‰<ÕCs•D.ÄB)—>"
​

推荐答案

好的，在运行了一些测试之后，我可以确定问题是文件中有一行被解释为公式，并且该公式无效(语法错误或其他错误).

OK, after running some tests, I'm pretty certain that the problem is that there is a line in your file that is being interpreted as a formula, and that formula is invalid (bad syntax or other error).

我将修改您的代码，如下所示:

I would modify your code as below:

For X = 1 To UBound(Result)
    Result(X, 1) = "'" & Lines(X - 1)
  Next

请注意单引号.

单引号将强制Excel将行视为文本，而不是公式.

The single quote will force Excel to see the line as text, and not as a formula.

另一种可能更快的方法是在写入数组之前将目标范围格式化为文本. .numberformat = "@"

An alternative, probably faster, would be to format the destination range as text before writing the array. .numberformat = "@"

您可能还会遇到一个单独的问题，即单元格中的字符过多，但这很容易纠正.

You may also have a separate problem with too many characters in the cell, but that is easily corrected.

这篇关于使用二进制访问读取从.dat文件中提取所有数据(应用程序定义的错误1004)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！