Read Binary Database File

[eselk2003]

Can anyone provide me with sample/psuedo code for reading a binary
file using VBA. I know about using open and input functions for
reading files in general, but mostly having trouble getting the data
in to a format I can work with in VBA. The data is written in a C
program with the records one after another in the following format
(struct is like type or class in VBA):

typedef struct
{
char del;
char company[41];
char street1[46];
char street2[46];
char city[25];
char state[3];
char zip[17];
char phone[21];
char email[91];
long namenum;
char buf[53];
char lock;
} PhoneDataStruct;

The structure is byte-packed in C, which means the fields don't start
on any special byte boundries, each file just comes right after the
next in the file. So for example:

The first byte of each record is one byte, a 0 for not-deleted, and
non-zero for deleted. The 2nd byte is the start of the Company
(company name) field, and the next 41 bytes make up the company name.
Somewhere within those 41 bytes is a zero byte which marks the end of
the null-terminated string.

Sorry if this is obvious to you, but for those who have never used C,
the idea of a struct and fixed-length null-terminated data may be
foreign.

All I want to do is read the entire file, one record at a time, and
dump the field in to cells in a spreadsheet... I know how to do
everything except put the data in to VBA strings so I can work with
them. The i/o function input() just returns a variant with the 1024
bytes of data that make up each record (I didn't include the full
struct above)... but then how do I get those 1024 bytes of data in to
individual strings for each field?

I could just write something in C, sure, but I'm trying to provide a
script in VBA only that I can give to a client so they can read my
data file and work with it in Excel. I could give them a COM object
or something else they could call... but I don't wanna =]

 

[Joel]

Here are two methods that should work. A C Language string is terminated
with a binary 0. I haven't tested this code and haven't worked with binary
characters in VBA in a while so not sure if I got it exactly right. Let me
know if you need more assistance.


'// API declaration must be in the modules

Declare Sub MemCopy Lib "kernel32" Alias "RtlMoveMemory" (Dest As Any, Src
As Any, ByVal cb&)


Sub readstructure2()



FName = "c:\temp\abc.bin"
Open FName For Binary Access Read As #1

Dim PhoneDataStruct(349) As Byte
Dim ch As Byte
Dim del As String
Dim company As String
Dim street1 As String
Dim street2 As String
Dim city As String
Dim state As String
Dim zip As String
Dim phone As String
Dim email As String
Dim namenum As Long
Dim buf As String
Dim lockchar As String

For CharCount = 0 To 348
Input #1, ch
PhoneDataStruct(CharCount) = ch
Next CharCount

MemCopy PhoneDataStruct(0), ByVal del, 1
MemCopy PhoneDataStruct(1), ByVal company, 41
MemCopy PhoneDataStruct(42), ByVal street1, 46
MemCopy PhoneDataStruct(88), ByVal street2, 46
MemCopy PhoneDataStruct(134), ByVal city, 25
MemCopy PhoneDataStruct(159), ByVal state, 3
MemCopy PhoneDataStruct(162), ByVal zip, 17
MemCopy PhoneDataStruct(179), ByVal phone, 21
MemCopy PhoneDataStruct(200), ByVal email, 91
MemCopy PhoneDataStruct(291), ByVal namenum, 4
MemCopy PhoneDataStruct(295), ByVal buf, 53
MemCopy PhoneDataStruct(348), ByVal lockchar, 1


Close #1

End Sub


Sub readstructure()

FName = "c:\temp\abc.bin"

Dim ch As Byte
Dim del As String
Dim company As String
Dim street1 As String
Dim street2 As String
Dim city As String
Dim state As String
Dim zip As String
Dim phone As String
Dim email As String
Dim namenum As Long
Dim buf As String
Dim lockchar As String


Open FName For Binary Access Read As #1


endcompany = False
endstreet1 = False
endstreet2 = False
endcity = False
endstate = False
endzip = False
endphone = False
endemail = False
endbuf = False


For CharCount = 0 To 348
Input #1, ch

Select Case CharCount
Case 0:
'char del;
del = Chr(ch)
Case 1 To 41:
'char company[41];
If endcompany = False Then
If ch = 0 Then
endcompany = True
Else
company = company & Chr(ch)
End If
End If
Case 42 To 87:
'char street1[46];
If endstreet1 = False Then
If ch = 0 Then
endstreet1 = True
Else
street1 = street1 & Chr(ch)
End If
End If
Case 88 To 133:
'char street2[46];
If endstreet2 = False Then
If ch = 0 Then
endstreet2 = True
Else
street2 = street2 & Chr(ch)
End If
End If
Case 134 To 158:
'char city[25];
If endcity = False Then
If ch = 0 Then
endcity = True
Else
city = city & Chr(ch)
End If
End If
Case 159 To 161:
'char state[3];
If endstate = False Then
If ch = 0 Then
endstate = True
Else
state = state & Chr(ch)
End If
End If
Case 162 To 178:
'char zip[17];
If endzip = False Then
If ch = 0 Then
endzip = True
Else
zip = zip & Chr(ch)
End If
End If
Case 179 To 199:
'char phone[21];
If endphone = False Then
If ch = 0 Then
endphone = True
Else
phone = phone & Chr(ch)
End If
End If
Case 200 To 290:
'char email[91];
If endemail = False Then
If ch = 0 Then
endemail = True
Else
email = email & Chr(ch)
End If
End If
Case 291 To 294:
'long namenum;
namenum = (256 * namenum) + ch
Case 295 To 347:
'char buf[53];
If endbuf = False Then
If ch = 0 Then
endbuf = True
Else
buf = buf & Chr(ch)
End If
End If
Case 348:
'char lock;
lockchar = Chr(ch)


End Select
Next CharCount

Close #1

End Sub

Can anyone provide me with sample/psuedo code for reading a binary
file using VBA. I know about using open and input functions for
reading files in general, but mostly having trouble getting the data
in to a format I can work with in VBA. The data is written in a C
program with the records one after another in the following format
(struct is like type or class in VBA):

typedef struct
{
char del;
char company[41];
char street1[46];
char street2[46];
char city[25];
char state[3];
char zip[17];
char phone[21];
char email[91];
long namenum;
char buf[53];
char lock;
} PhoneDataStruct;

The structure is byte-packed in C, which means the fields don't start
on any special byte boundries, each file just comes right after the
next in the file. So for example:

The first byte of each record is one byte, a 0 for not-deleted, and
non-zero for deleted. The 2nd byte is the start of the Company
(company name) field, and the next 41 bytes make up the company name.
Somewhere within those 41 bytes is a zero byte which marks the end of
the null-terminated string.

Sorry if this is obvious to you, but for those who have never used C,
the idea of a struct and fixed-length null-terminated data may be
foreign.

All I want to do is read the entire file, one record at a time, and
dump the field in to cells in a spreadsheet... I know how to do
everything except put the data in to VBA strings so I can work with
them. The i/o function input() just returns a variant with the 1024
bytes of data that make up each record (I didn't include the full
struct above)... but then how do I get those 1024 bytes of data in to
individual strings for each field?

I could just write something in C, sure, but I'm trying to provide a
script in VBA only that I can give to a client so they can read my
data file and work with it in Excel. I could give them a COM object
or something else they could call... but I don't wanna =]

 

[eselk2003]

Here are two methods that should work.  A C Language string is terminated
with a binary 0.  I haven't tested this code and haven't worked with binary
characters in VBA in a while so not sure if I got it exactly right.  Let me
know if you need more assistance.

Thanks, I should be able to do something with this. I haven't tried
them yet, but I'm guessing the 2nd method (without the memmove) will
work, but maybe be slow. The 1st method will probably be faster, but
I don't think you can just move the memory to VBA strings like that
because by default they probably don't have any size, but possibly you
could initialize them to some long string first to give them some
size.

I'll try the 2nd method first, since I don't need anything super fast
anyway, and I'm sure I can modify/improve if I do need it to be
faster.

Thanks again!

 

[Joel]

the memcopy will work. I got the code straight from a microsoft webpage and
used it before. the number of bytes to move is the last parameter of the
memcopy function.

there is a problem with the other method when it comes to the moving of the
number. first numbers are stored on a PC backwards. with the nibble order
from low to high ( 0 to 4) 11 00 33 22 and the number is two's compliment.
Your numbers will not come out straight with the code I provided. I thought
about this after I posted the solution. The memcopy will work properly as
long as you define the number as a Long which VBA will treat as 32 bits.

You may have problems with sign extension with negative numbers. Not
completely sure. The best thing is to try -1 and see if you get the correct
answer

VBA is not correct is the way it is handling length of number on my PC. I
though an Integer was 8 bits, Long 16 bits, and double 32 bit. I'm seeing
Integer and Long as being the same size?

-1 as 16 bit 2's compliment is = &HFFFF
-1 as 32 bit 2's compliment is = &HFFFFFFFF
-1 as 64 bit 2's compliment is = &HFFFFFFFFFFFFFFFF

So to convert from 16 bit 2's compliment to 32 bit 2's compliment you do
something like this

Dim MyNumber As Long

MyNumber = &HFFFF
If (MyNumber Imp &H8000) = &H8000 Then
MyNumber = MyNumber + &HFFFF0000
End If

Let me know if you have problems with the number and I will find a solution.
Strings should not really be a problem.

 

[eselk2003]

the memcopy will work.  I got the code straight from a microsoft webpage and
used it before.  the number of bytes to move is the last parameter of the
memcopy function.

I tried the code, and Excel crashes when it gets to this line:

MemCopy PhoneDataStruct(0), ByVal del, 1

The RtlMoveMemory function that this maps to wants the destination as
the first param, and source as 2nd, so I assumed you just typed them
in reverse order here, but it still crashes even when I swap them.
The reason I assume this was reversed is because you filled in
PhoneDataStruct already, from disk, so must be wanting to move the
memory to 'del'.

You have the decleration as this:

Declare Sub MemCopy Lib "kernel32" Alias "RtlMoveMemory" (Dest As Any,
Src As Any, ByVal cb&)

Should that last part really be "ByVal cb&" or should it be "ByVal cb
as Long"? I tried both, but Excel still crashes on the same line.

All I did, to start with, was start a new XLS file, insert a new
module, and copy/paste your code.

I have experience working with byte order (endian) issues, so that
shouldn't be a problem if I need to use the other method you posted,
but the MemCopy code sure looks nicer if it will work.

 

[Joel]

It looks like I found an old help file on the microsoft website. The
original code was for a win16 operating system and we need a win32 dll. I
changed memcopy to Copymemory. Still got the error. I then noticed a very
small problem. A Basic declaration statement assumes BYREF when not declared
(not ByVal). The original code has ByVal on the actual move statement. I
remove ByVal and everything is now working.


'// API declaration must be in the modules

Private Declare Sub CopyMemory Lib "kernel32" Alias _
"RtlMoveMemory" (Dest As Any, Src As Any, _
ByVal cb As Long)


Sub readstructure2()



FName = "c:\temp\abc.bin"
Open FName For Binary Access Read As #1

Dim PhoneDataStruct(349) As Byte
Dim ch As Byte
Dim del As String
Dim company As String
Dim street1 As String
Dim street2 As String
Dim city As String
Dim state As String
Dim zip As String
Dim phone As String
Dim email As String
Dim namenum As Long
Dim buf As String
Dim lockchar As String

For CharCount = 0 To 348
Input #1, ch
PhoneDataStruct(CharCount) = ch
Next CharCount



CopyMemory PhoneDataStruct(0), del, 1
CopyMemory PhoneDataStruct(1), company, 41
CopyMemory PhoneDataStruct(42), street1, 46
CopyMemory PhoneDataStruct(88), street2, 46
CopyMemory PhoneDataStruct(134), city, 25
CopyMemory PhoneDataStruct(159), state, 3
CopyMemory PhoneDataStruct(162), zip, 17
CopyMemory PhoneDataStruct(179), phone, 21
CopyMemory PhoneDataStruct(200), email, 91
CopyMemory PhoneDataStruct(291), namenum, 4
CopyMemory PhoneDataStruct(295), buf, 53
CopyMemory PhoneDataStruct(348), lockchar, 1


Close #1

End Sub

 

[eselk2003]

You're right, no crashing now. However, this line:

Input #1, ch

Doesn't appear to actually do anything. If you check LOC(1) after
that line, the file pointer hasn't moved, and "ch" is always zero (and
yes, I made sure my file wasn't just full of zeros/NULLs).

However, I suspect even if the Input line is fixed, the "strings" are
still going to be weird strings in VBA unless I loop through them and
remove everything after the NULL terminator. I've tried something
similar before, and if you look at the strings in the VBA debugger
you'll see something like this:

listname<box><box><box><box>

Where <box> are non-ascii characters, because the input function, and
I suspect CopyMemory, just assume fixed length strings since VBA has
no concept of null-terminated strings. So I suspect even once I get
your example working, I'll still need to loop through all of the
strings to fix them (remove the null terminator and everything after
it). If I'm going to loop through the strings to fix them anyway,
then I'd probably be better off without the CopyMemory and stuffing
the bytes in to an array, and instead just do something like:

listname = input(30,#1)
company = input(40,#1)
FixString(listname)
FixString(company)

The FixString functions would just remove everything after the first
NULL.

One weird thing, in my mind, is that I can't do:

ch = input(1,#1)

If "ch" is a byte, because I get a "type mismatch". Wied that VBA
will not treat a single character as a byte. I suspect this is
somehow related to why your sample does nothing on the "input #1, ch"
line -- probably because it treats "ch" as nothing, since it
apparently doesn't know how to read a "byte" type variable.

I did run across a sample in the VBA help file, under the "seek"
function, where they read a structure from a file... looks
interesting, so I'll try it.

 

[eselk2003]

Can anyone provide me with sample/psuedo code for reading a binary
file using VBA.

OK, here is what I ended up with. Even though I couldn't get Joel's
method to help, I'd still like to say thanks because I probably would
have just given up if I hadn't have seen and tried his examples....
and a big part of what I was missing was how to open the file in
binary mode which I ended up using for the code here as well. Plus, I
found this example (the usage of Get for reading and user-defined
types) while looking through VBA help files while reviewing Joel's
code.

' This is the format used for reading/writing to binary disk file
Type RawRecord
DeleteByte As Byte
ListName As String * 30
Company As String * 41
End Type

' This is the VBA friendly format, with nice strings that don't
contain the
' null-terminate or random junk after it.
Type Record
DeleteByte As Byte
ListName As String
Company As String
End Type

' This is the function that converts a null-terminated string to a
nice VBA
' string without any padding/spaces/nulls/etc.
Function NullTerminatedStringToVBAString(s As String) As String
Dim i As Integer
i = InStr(s, Chr$(0))
If (i > 0) Then
NullTerminatedStringToVBAString = Left(s, i - 1)
Else
NullTerminatedStringToVBAString = s
End If
End Function

Sub readstructure2()

FName = "c:\filename.ext"
Open FName For Binary Access Read Shared As #1

' Skip the file header
Seek #1, 513

' Read the first record (eventually I'd make this a loop until EOF)
Dim data As RawRecord
Get #1, , data

' Convert RawRecord to record that has strings that are easy to work
with in VBA
' for displaying to the user, etc.
' Eventually this would be moved to a seperate function, and once I
get in to doing
' anything with numbers I made need to mess with endian issues.
Dim rec As Record
rec.DeleteByte = data.DeleteByte
rec.ListName = NullTerminatedStringToVBAString(data.ListName)
rec.Company = NullTerminatedStringToVBAString(data.Company)

Close #1

' Work with "rec" here, like output to cells on an Excel spreadsheet,
etc.

End Sub