Reading Complex Binary Files into a worksheet

Hello anthony1312002,

Were you hoping Excel could "translate" the binary file for you?

What type of result do want after importing the binary file?

Hello Anthony,

This is quite a complex operation due to the rules needed to make the output readable. Here is a User Defined Function that will pull out only the following characters: space, 0 to 9, minus sign, A to Z, underscore, and a to z. The result of this macro on the string you provided is 0311stat2017 jX0jX0jX VISA 71050 ICL REGZ 209925 b n. It is much better but not perfect.

Please Login or Register  to view this content.

Hello Anthony,

Are all the binary files in the same directory?

Can the directory's path be added into the code?

If so what is the directory's path?

What extension do these files have?

Will each file's contents be placed into a single cell on the worksheet?

Is there a break like a blank row between each file?

If you know the structure (data types of the fields) of the file you might be able to read the binary file directly and output to worksheet.

Are you able to post example binary file with expected output? A few records should be enough.

Unfortunately I'm unable to attach a file due to security policies. But here is a fuller representation of the file I'm dealing with.

Please Login or Register  to view this content.

The output would be something like you listed earlier

0311 stat2017 VISA 71050 ICL REGZ 209925

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

When you post it like that it become just text.

Where does the original file come from?
Ideally you would need to know what type of information is contained in each record of the file. Something like, 1st field if text, 2nd field is a Integer etc.

If you don't know that then the only thing you can do is remove characters not in the normal 32 to 127 range, which is what Leith's code is doing.

Use creating binary files will not be of much use.

First I think you need help from the supplier of the files in the form of them providing details or alternatives.
Where do the files come from? Have you asked whether they can be provided in a different format?

As I may not have any time next week until Thursday, according to your last "attachment",
as the initial "explanation" does not match with this attachment,
as the very poor explanation on Excel side, this is a demo extracting raw data to two columns
like any text file (can't waste time to find any binary logic without required crystal clear informations)
in order you get a code base you can mod to suit your real need as we can't guess,
update the initial path & name of source data file (if you want to navigate to a directory to choose a file,
a must read in VBA inner help : GetOpenFilename method !) :

PHP Code: 

Sub Demo1()
    Dim V, T$(), R&, S$()
        V = ThisWorkbook.Path & "\Data .dat"
        If Dir(V) = "" Then Beep: Exit Sub
        Open V For Binary As #9
        V = Split(Input(LOF(9), #9), "Z " & Chr(2))
        Close #9
        [A1].Value = Split(V(0))(0)
        ReDim T(1 To UBound(V), 1)
    For R = 1 To UBound(V)
            S = Split(V(R), " " & Chr(14))
        If UBound(S) < 2 Then
            S(1) = Right(S(0), 7)
            S(0) = Left(S(0), InStrRev(S(0), " ") - 1)
        End If
            T(R, 0) = S(0)
            T(R, 1) = S(1)
    Next
        [C1:D1].Resize(UBound(T)).Value = T
End Sub 


Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !