从vba中的syslog文件中提取数据

Question

在下面的代码中，我试图从具有字符串original value的syslog文件中提取一行，但我将获得整个文件数据作为输出。

Sub test()
Dim InputData

' Open file for input.
Open "D:\temp\DraftTest3_pmi_jt_import_All_Annotations.syslog" For Input As #1 

Do While Not EOF(1) ' Check for end of file.
    Line Input #1, InputData    ' Read line of data.
    Debug.Print InputData    ' Print to the Immediate window.
    If InStr(1, InputData, "original value") Then
        Cells(2, 3).Value = InputData ''' Print a line in cell
        InputData = ""
    End If
Loop

Close #1    ' Close file.
End Sub

Syslog内容：

Nodes differ : original value 189.1596739640256
               new value      191.1200796864099

Difference: 

<object type="NXOpen.ValidationAnnotation3DCollectionValidator" value="None" xml_line_number="14" />
 <context line_number="132" variable_name="validationAnnotation3DCollectionValidator1" xml_line_number="15" />
  <object type="NXOpen.ValidationWeldValidator" value="None" xml_line_number="2675" />
   <context line_number="0" variable_name="" xml_line_number="2676" />
    <output name="WeldReferenceLineLength" xml_line_number="2677" />
      <object tolerance="1e-06" tolerance_type="absolute" type="double" value="189.1596739640256" />

      *** changed to ***

      <object tolerance="1e-06" tolerance_type="absolute" type="double" value="191.1200796864099" xml_line_number="2678" />
_________________________________________________________________________________________

Nodes differ : original value 102.5546050485778
               new value      102.8898888970786

Difference: 

<object type="NXOpen.ValidationAnnotation3DCollectionValidator" value="None" xml_line_number="14" />
 <context line_number="132" variable_name="validationAnnotation3DCollectionValidator1" xml_line_number="15" />
  <object type="NXOpen.ValidationWeldValidator" value="None" xml_line_number="4422" />
   <context line_number="0" variable_name="" xml_line_number="4423" />
    <output name="WeldReferenceLineLength" xml_line_number="4424" />
      <object tolerance="1e-06" tolerance_type="absolute" type="double" value="102.5546050485778" />

期望产出：

​

​

Answer 1

你想要提取值189.1596739640256吗？-7分钟前西德哈斯溃败了，是的。我想要那个价值。-6分钟前的专家

获得这个值的最快方法是

1. 一次打开并读取文本文件。也就是说，没有循环
2. 在original value上拆分
3. 然后在new value上分裂

代码：

Option Explicit

Sub Sample()
    Dim MyData As String, ValueNeeded As String

    '~~> Change this to the relevant file
    '~~> Read the entire file in a variable in ONE GO
    Open "C:\Users\routs\Desktop\Sample.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1

    ValueNeeded = Trim(Split(MyData, "original value")(1))
    ValueNeeded = Trim(Split(ValueNeeded, "new value")(0))

    Debug.Print ValueNeeded
End Sub

截图

​

​

编辑

要合并多个实例，如您最近的编辑所示，您必须寻找一个唯一的单词。在您的例子中，它将是Nodes differ :。为什么要这样？因为这意味着一个新的部分。因此，逻辑是在Nodes differ :上拆分，然后在original value、new value和Difference:上拆分

参见此示例

Option Explicit

Sub Sample()
    Dim MyData As String, ValueString As String
    Dim orgVal As String, newVal As String
    Dim strData() As String
    Dim i As Long

    '~~> Change this to the relevant file
    '~~> Read the entire file in a variable in ONE GO
    Open "C:\Users\routs\Desktop\test.Txt" For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1

    '~~> Split on "Nodes differ :" to identify separate sections
    '~~> Store them in an array
    strData() = Split(MyData, "Nodes differ :")

    '~~> Loop though each section and split on relevant identifiers
    '~~> to get the necessary value
    For i = LBound(strData) + 1 To UBound(strData)
        ValueString = Trim(Split(strData(i), "original value")(1))
        orgVal = Trim(Split(ValueString, "new value")(0))
        newVal = Trim(Split(ValueString, "new value")(1))
        newVal = Trim(Split(newVal, "Difference:")(0))

        Debug.Print "Original Value: " & Trim(orgVal)
        Debug.Print "New Value: " & Trim(newVal)
    Next i
End Sub

​

​

注意：我没有做任何错误处理。我相信你会处理好的.

Answer 2

InStr返回一个变体，而不是布尔值。尝试将您的比较更改为If InStr(...) > 0。医生在https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function。

我还注意到，您正在用每个值覆盖单个单元格。如果要保存所有值，则需要更改索引Cells的方式。

编辑 Line Input不能处理Unix类型的文本文件(LF只行尾).参见，例如，https://chandoo.org/forum/threads/line-input-reads-entire-text-file-as-a-single-record.23089/。在Loading linux text file into excel using VBA上试试答案。简而言之，您必须读取整个文件(您已经做了:)，然后在vbLf上拆分它以获得单独的行。