Is possible to use regex to read binary file?

jindon · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Pattern I found is

Code

Sub test()
    Dim txt As String, m As Object
    txt = "FF7700000253206445018934551F81474554768FFFFF0015000A4800015A00024200016000013300013600013700015B00016600016500017700017800017E00" & _
    "16900006 A00007900009300012200002100010900010A00012600010200010400010500010600011000010800012B00002C00012D00012E0001550001560007" & _
    "2 A00002F0000300000310000FF7900800932C90688888000A000800935C90600008000000080093CC906888880008000800943C90688888000800005900F0102" & _
    "000000308147526905FFFFFF00910F01020000013A81475269559FFFFF009310010C0000009F8147526905FFFFFF0101960F010E000000EB81475269596FFFFF" & _
    "970  F01010006F69981475269563FFFFF00940E0001000001000100FFFF0000010195060003790001EA05820037060100000100650000000200000200180000" & _
    "000300000300170000000400000400010000000500000500150000000A00FFFF006500000007802EC918009181475269539FFFFFFF009181475269539FFFFF00" & _
    "103 CA030808FECB0A00000000000000000000CC0101811BC90B009181475269567FFFFFFFCA06000000000000CB0103CC0101"
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(^.*532064[^F]*.814[^F]*|5900.*(?=950))"
        If .test(txt) Then
            For Each m In .Execute(txt)
                MsgBox m
            Next
        End If
    End With
End Sub

Display More

Ophiuchus · Sep 28th 2013

Re: Is possible to use regex to read binary file?

The goal is print in different rows (one for each block) the values got with regex1 and regex2(if exists).

Maybe somebody could help in how to apply the regexes for variable that stores each block.

Thanks in advance

jindon · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Good luck.

Ophiuchus · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Quote from jindon;685790

Pattern I found is

Hello jindon,

I'll test your code when I get a PC. But is possible to use back-reference?
Because the need to process the pattern2 that has variable length, so I'd like to store the content of pattern1 and patterne in different variables.

Thanks for the help.

jindon · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Simple

Code

Sub test()
    Dim txt As String, m As Object, var1 As String, var2 As String
    txt = "FF7700000253206445018934551F81474554768FFFFF0015000A4800015A00024200016000013300013600013700015B00016600016500017700017800017E00" & _
    "16900006 A00007900009300012200002100010900010A00012600010200010400010500010600011000010800012B00002C00012D00012E0001550001560007" & _
    "2 A00002F0000300000310000FF7900800932C90688888000A000800935C90600008000000080093CC906888880008000800943C90688888000800005900F0102" & _
    "000000308147526905FFFFFF00910F01020000013A81475269559FFFFF009310010C0000009F8147526905FFFFFF0101960F010E000000EB81475269596FFFFF" & _
    "970  F01010006F69981475269563FFFFF00940E0001000001000100FFFF0000010195060003790001EA05820037060100000100650000000200000200180000" & _
    "000300000300170000000400000400010000000500000500150000000A00FFFF006500000007802EC918009181475269539FFFFFFF009181475269539FFFFF00" & _
    "103 CA030808FECB0A00000000000000000000CC0101811BC90B009181475269567FFFFFFFCA06000000000000CB0103CC0101"
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "(^(.*532064[^F]*.814[^F]*)|(5900.*)(?=950))"
        If .test(txt) Then
            For Each m In .Execute(txt)
                If m.submatches(1) <> "" Then
                    var1 = m.submatches(1)
                Else
                    var2 = m.submatches(2)
                End If
            Next
        End If
    End With
    MsgBox "var1 = " & var1 & vbLf & "var2 = " & var2
End Sub

Display More

Ophiuchus · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Hello jindon,

Thank you for your help!

I was able to modify your code to get 5 substrings and works fine:

Code

Sub test()
    Dim txt As String, m As Object, var1 As String, var2 As String
    txt = "FF7700000253206445018934551F81474554768FFFFF0015000A4800015A00024200016000013300013600013700015B00016600016500017700017800017E00" & _
    "16900006A00007900009300012200002100010900010A00012600010200010400010500010600011000010800012B00002C00012D00012E0001550001560007" & _
    "2A00002F0000300000310000FF7900800932C90688888000A000800935C90600008000000080093CC906888880008000800943C90688888000800005900F0102" & _
    "000000308147526905FFFFFF00910F01020000013A81475269559FFFFF009310010C0000009F8147526905FFFFFF0101960F010E000000EB81475269596FFFFF" & _
    "970F01010006F69981475269563FFFFF00940E0001000001000100FFFF0000010195060003790001EA05820037060100000100650000000200000200180000" & _
    "000300000300170000000400000400010000000500000500150000000A00FFFF006500000007802EC918009181475269539FFFFFFF009181475269539FFFFF00" & _
    "103CA030808FECB0A00000000000000000000CC0101811BC90B009181475269567FFFFFFFCA06000000000000CB0103CC0101"
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "((.{6})(532064[^F]*).(814[^F]*)|05(9.{32,34}.*?)940E(.{28}))"
        If .test(txt) Then
            For Each m In .Execute(txt)
                If m.submatches(1) <> "" Then
                    var1 = m.submatches(1)
                    var2 = m.submatches(2)
                    var3 = m.submatches(3)
                Else
                    var4 = m.submatches(4)
                    var5 = m.submatches(5)
                End If
            Next
        End If
    End With
   
    MsgBox "var1 = " & var1 & vbLf & "var2 = " & var2 & vbLf & "var3 = " & var3 & vbLf & "var4 = " & var4 & vbLf & "var5 = " & var5
 End Sub

Display More

Now I need to extract some substrings from var4 (var4 is below), the substrings are highlighted in color:
var4 content:
900F0102000000308147526905FFFFFF00910F01020000013A81475269559FFFFF009310010C0000009F8147526905FFFFFF0101960F
010E000000EB81475269596FFFFF970F01010006F69981475269563FFFFF00

As you can see, there are 5 subtrings within this var4. The regex that works to match each of those subtrings is:

Code

(9[\d])(.{4})(.{2})(.{8})([^F]*)F*?(0[0-1])(0[0-1])?

And I use 7 groups () because I need to process each submatch(x) from this pattern.

The issue is that even there are 5 subtrings in var4, it seems the code I'm using below is only matching one, because
For Each loop only happens once.

Code

With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "((9[\d])(.{4})(.{2})(.{8})([^F]*)F*?(0[0-1])(0[0-1])?)"
        If .test(var4) Then
            For Each v In .Execute(var4)
                If v.submatches(1) <> "" Then
                    var6 = v.submatches(1)
                    var7 = v.submatches(2)
                    var8 = v.submatches(3)
                    .
                    .
                Else
                    .
                    .
                End If
            Next
        End If
    End With

Display More

I don't know how the regex could match more than one substring in var4.

Thanks again for any help.

jindon · Sep 28th 2013

Re: Is possible to use regex to read binary file?

See if this helps

Code

Sub test()
    Dim txt As String, m As Object, var1 As String, var2 As String, var3, var4, var5
    txt = "FF7700000253206445018934551F81474554768FFFFF0015000A4800015A00024200016000013300013600013700015B00016600016500017700017800017E00" & _
    "16900006A00007900009300012200002100010900010A00012600010200010400010500010600011000010800012B00002C00012D00012E0001550001560007" & _
    "2A00002F0000300000310000FF7900800932C90688888000A000800935C90600008000000080093CC906888880008000800943C90688888000800005900F0102" & _
    "000000308147526905FFFFFF00910F01020000013A81475269559FFFFF009310010C0000009F8147526905FFFFFF0101960F010E000000EB81475269596FFFFF" & _
    "970F01010006F69981475269563FFFFF00940E0001000001000100FFFF0000010195060003790001EA05820037060100000100650000000200000200180000" & _
    "000300000300170000000400000400010000000500000500150000000A00FFFF006500000007802EC918009181475269539FFFFFFF009181475269539FFFFF00" & _
    "103CA030808FECB0A00000000000000000000CC0101811BC90B009181475269567FFFFFFFCA06000000000000CB0103CC0101"
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "((.{6})(532064[^F]*).(814[^F]*)|05(90.+)(91.+)(93.+)940E(90.*)(96.*)(97.+))"
        .Pattern = "((.{6})(532064[^F]*).(814[^F]*)|05(9.{32,34}.*)940E(.{28}))"
        If .test(txt) Then
            For Each m In .Execute(txt)
                If m.submatches(1) <> "" Then
                    var1 = m.submatches(1)
                    var2 = m.submatches(2)
                    var3 = m.submatches(3)
                Else
                    var4 = m.submatches(4)
                    var5 = m.submatches(5)
                End If
            Next
            .Pattern = "9.{26,27}F{4,}(0{2}|0101)?"
            For Each m In .Execute(var4)
                MsgBox m
            Next
        End If
    End With
    MsgBox "var1 = " & var1 & vbLf & "var2 = " & var2 & vbLf & "var3 = " & var3 & vbLf & "var4 = " & var4 & vbLf & "var5 = " & var5
End Sub

Display More

snb · Sep 28th 2013

Re: Is possible to use regex to read binary file?

Code

Sub M_snb()
    Open "binary.txt" For Binary As #1
        c00 = Input(LOF(1), 1)
    Close
     
    For j = 1 To Len(c00)
        c01 = c01 & Right("0" & Hex(Asc(Mid(c00, j, 1))), 2)
    Next
     
    sn = Split(Join(Split(Left(c01, Len(Trim(Replace(c01, "00", "  ")))), "FF77"), "|FF77"), "|")
    
    With CreateObject("VBScript.RegExp")
        .Global = True
        For j = 1 To UBound(sn)
            .Pattern = "((.{6})(532064[^F]*).(814[^F]*)|05(9.{32,34}.*?)940E(.{28}))"
           For Each it In .Execute(sn(j))
             .Pattern = "(9[\d])(.{4})(.{2})(.{8})([^F]*)F*?(0[0-1])(0[0-1])"
             For Each it2 In .Execute(it)
                c02 = c02 & vbLf & it2
             Next
           Next
        Next
    End With
    MsgBox c02
End Sub

Display More

Ophiuchus · Sep 29th 2013

Re: Is possible to use regex to read binary file?

Hello jindon and snb,

Thank you for your help. I've been trying and trying and mixing and adapting your 2 codes and it seems to work nice.

The only issue it seems to be the performance if I try a relative small file of 20MB. Excel got stuck.

Do you know if when open the file for read can be done only for a few KB (4KB for example)? This in order to avoid buffer problems.

Thanks again for the help.

snb · Sep 29th 2013

Re: Is possible to use regex to read binary file?

You know how to read a freefile line by line ...

Ophiuchus · Sep 30th 2013

Re: Is possible to use regex to read binary file?

Quote from snb;685869

You know how to read a freefile line by line ...

Hello snb,

Thanks for your help. I've tried filefree, but is the same behaviour, it seems a buffer issue or that a relative small
size of 20 MB cannot be handle by VBA :(.

snb · Sep 30th 2013

Re: Is possible to use regex to read binary file?

I can't tell: I never use flat ascii files of that size.

**pike** · Sep 30th 2013

Re: Is possible to use regex to read binary file?

Ophiuchus
It would help if you can tell what type of file it is???
where it did it come from and how it was created ???
and what is the pattern .. is it data base ..??
the file was nott set up to be read as a binary file

Ophiuchus · Sep 30th 2013

Re: Is possible to use regex to read binary file?

Hello pike,

Is a dump file containing information for products and each block is a record for a product and each block contains characteristics of that product.

The file is dumped into binary format to have a compressed file with the information and yes, it is created to be read as binary. Is the same format with the same patterns as the file I've uploaded, only is bigger. A file of 2GB can be read without issues by a scripting language like ruby block by block separating each line by ff77, but it seems the problem could be that VBA is reading or storing in buffer the complete file and not storing only each block at a time since got stuck when I use a small file of 20MB.

Thanks again for the help

apo · Sep 30th 2013

Re: Is possible to use regex to read binary file?

HI..

One question..

So why persist with VBA when you can use Ruby or perhaps Pyphon?.. or even VB.NET and nice SQL SP?

Ophiuchus · Sep 30th 2013

Re: Is possible to use regex to read binary file?

Hello apo,

Is because the ruby solution was not done by me, I don't know ruby or python or VB.net. Only have some experience with Excel VBA and having a solution in excel would be much more portable for the users.

Best regards

**pike** · Oct 2nd 2013

Re: Is possible to use regex to read binary file?

Hi
binary files are made in easliy read and retrive blocks of information
looks like it is just dumped in with no structure/order
what is the code use to make the file?

Ophiuchus · Oct 2nd 2013

Re: Is possible to use regex to read binary file?

Hello pike,

I dont have the code which generates the dump. It is an application that generates it. What I've seen is doing with regex is the easiest way to get the information for each block, since the lenght of each block is variable and the values of second patterns are not in the same position within each block.

The issue I see is VBA gets stuck, the algorithm it seems to be fine.

Best regards.

Ophiuchus · Oct 2nd 2013

Re: Is possible to use regex to read binary file?

Hello pike,

I dont have the code which generates the dump. It is an application that generates it. What I've seen is doing with regex is the easiest way to get the information for each block, since the lenght of each block is variable and the values of second patterns are not in the same position within each block.

The issue I see is VBA gets stuck, the algorithm it seems to be fine.

Best regards.

Is possible to use regex to read binary file?

Participate now!

Share