从二进制.dat文件中搜索特定的数据字符串，仅提取文本 [英] Search for specific strings of data from a binary .dat file, only extract text

问题描述

Sub ReadEntireFileAndPlaceOnWorksheet()
    Dim X As Long, Ys As Long, FileNum As Long, TotalFile As String, FileName As String, Result() As String, Lines() As String, rng As Range, i As Long, used As Range, lc As Long
    
         FileName = "C:\Users\MEA\Documents\ELCM2\DUMMY_FILE.dat"
        FileNum = FreeFile
         Open FileName For Binary As #FileNum
        TotalFile = Space(LOF(FileNum))
        Get #FileNum, , TotalFile
        Close #FileNum
        Lines = Split(TotalFile, vbNewLine)
         Ys = 1
         lc = Sheet3.Cells(1, Columns.Count).End(xlToLeft).Column
        For X = 1 To UBound(Lines)
            Ys = Ys + 1
            ReDim Preserve Result(1 To Ys)
            Result(Ys) = "'" & Lines(X - 1)
            Set used = Sheet1.Cells(Sheet1.Rows.Count, lc + 1).End(xlUp).Rows
            Set rng = used.Offset(1, 0)
            rng.Value = Result(Ys)
         Next
         
End Sub

我正在尝试在.dat(二进制文件)中找到一些数据.数据应如下所示:

I am trying to find some data in a .dat (binary file). The data should look like this:

MiHo14.dat
MDF     3.00    TGT 15.0
Time: 06:40:29 PM
Recording Duration: 00:05:02
Database: DB
Experiment: Min Air take
Workspace: MINAIR
Devices: ETKC:1,ETKC:2
Program Description: 0delivupd2
Module_delivupd2
WP: _AWD_5
RP: _AWD
§@
Minimum intake - + revs - Downward gear ​

我目前的代码从.dat文件提取所有数据并将其放置在Excel文件中，如下所示:

The code I have currently extracts all data from .dat file and places in Excel file looks like this:

MiHo14.dat
MDF     3.00    TGT 15.0
Time: 06:40:29 PM
Recording Duration: 00:05:02
Database: DB
Experiment: Min Air take
Workspace: MINAIR
Devices: ETKC:1,ETKC:2
Program Description: 0delivupd2
Module_delivupd2
WP: _AWD_5
RP: _AWD
§@
Minimum intake - + revs - Downward gear 
Bã|ŽA…@@,s~?
B{À¿…@@@Ý‚Iá 
Á<
"@²n¢"N@ÇÿÈÿj
Ð="SØ•N@ÇÿÈÿj   
à¨. —N@ÇÿÈÿj
 8²œg˜N@ÇÿÈÿj
0NI,¯™N@ÈÿÈÿj
Ðä$öšN@ÈÿÈÿj
@Q›=œN@ÈÿÈÿj
Пe…N@ÇÿÈÿj
 GàÍžN@ÇÿÈÿj"
etc....​

我需要知道如何使用instr函数通过识别包含:"的行来提取信息，另一个挑战是数据中存在最后一行，即用户注释，该用户注释基本上可以是任何文本，我需要能够在不提取整个文件的情况下提取它，因为如您所见，它附带了很多符号(乱码).

I need to know how to use instr function to extract the information by identifying lines that include ":", the other challenge is there is a final line in the data that is a user comment this user comment can basically be any text, I need to be able to extract it without extracting the whole file because as you can see there is a lot of symbols (gibberish) that comes with it.

推荐答案

我不认为您要复制所有HD/PR/TX块以获得所需的输出.

I don't think you want to copy all the HD/PR/TX blocks to get the output you are looking for.

检查文件时，我可以看到有效数据和无效数据之间的一个区别(从您的角度来看)是无效数据不是以CR-LF组合结尾或包含空字符.如果该特征在整个文件中都是一致的，则可以利用它来发挥优势:

Examining at your file, one difference I can see between valid and invalid data (from your perspective) is that the invalid data either does not end with CR-LF combination, or contains a null character. If that characteristic is consistent throughout your files, you may be able to use it to advantage:

下面是我使用的代码以及结果.您可以为自己的例程修改变量，并查看其是否始终如一.

Below is the code I used, and the results. You can modify the variables for your own routine and see if it works consistently.

Option Explicit
Sub ProcessDAT()
    Const sFN As String = "D:\Users\Ron\Desktop\DUMMY_FILE.dat"
    Const sEND As String = vbCrLf
    Dim S As String, COL As Collection, V As Variant, I As Long
    Dim R As Range

Open sFN For Binary Access Read As #1
S = Space(LOF(1))
    Get #1, , S
Close #1

V = Split(S, sEND)
Set COL = New Collection
For I = 0 To UBound(V)
    If InStr(V(I), Chr(0)) = 0 Then COL.Add V(I)
Next I

ReDim V(1 To COL.Count, 1 To 1)
For I = 1 To UBound(V)
    V(I, 1) = COL(I)
Next I

Set R = Range("a1").Resize(UBound(V))
R = V  
End Sub

---

结果

Time: 11:47:42 AM
Recording Duration: 00:01:09
Database: Testproject
Experiment: Measurement_Dummy
Workspace: Workspace
Devices: ETKC:1
Program Description: LPOOPL14
WP: LPOOPL14d2_1
RP: LPOOPL14d2
§@
Dummy test data

这篇关于从二进制.dat文件中搜索特定的数据字符串，仅提取文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！