Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Reading Binary Files with Leading Binary Zeros

  Asked By: Archie    Date: Feb 09    Category: MS Office    Views: 1684
  

I am using Excel 2000 under Windows 2000.

I have created a flat file from a mainframe. I must read the
flat file and plop it into an Excel cell. This reading of the
flat file is done using Excel VBA.

If I use the VBA statements

Open FullTRFFileName For Binary As #1
Line Input #1, FullTRFText

FullTRFText will contain the file and be ready for placement
in the Excel cell.

Problem:

If the file beings with binary zeros, the binary zeros are
truncated ( which is wrong ).

eg.

Flat File 1.

00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 32 30 ....

places 32 30 ... in variable FullTRFText

Flat File 2.

20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 32 30 ....

places 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 32 30 .... in
variable FullTRFText

It would be nice if the Line Input read both files the same way and
place all of the bytes in the variable.

Any suggestions?

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Marina Smith     Answered On: Feb 09

I think we've got a case of "mixed Metaphor's".
first of all, A "Flat File" is an ASCII file, not Binary.
so.. the question here is:
Is the file actually ASCII or Binary?

If it is Binary, is it possible that those are "nulls", and not "0"?

Also, check the syntax for the open statement with Binary.
I believe that in excel  2007, to open as binary, the statment reads:
Open FullTRFFileName For binary  Access Read As #1

 
Answer #2    Answered By: Verner Fischer     Answered On: Feb 09


I am viewing the file in Ascii using something called XVI32.

The information appears exactly as shown below where

Hex 20 ==> Ascii Space
Hex 32 ==> Ascii 2
Hex 30 ==> Ascii 0

What I referred to as Binary Zeros are:

Hex 00 ==> Ascii Null

Unfortunately, the company that I work for still uses Excel 2000 and
Windows 2000 so, I am trying to make it all work happily in that
environment.

The help file states that the format for the Read is

Open "TESTFILE" For Binary Access Read As #1

What I wrote is:

Open FullTRFFileName For Binary As #1

where my definition is

Public FullTRFFileName As String

I hope that this clarifies what I am doing.

 
Answer #3    Answered By: Luz Hayes     Answered On: Feb 09

Thanks for your thoughts. I am viewing the file in Ascii using something called
XVI32.
The information appears exactly as shown below where

Hex 20 ==> Ascii Space
Hex 32 ==> Ascii 2
Hex 30 ==> Ascii 0

What I referred to as Binary Zeros are:

Hex 00 ==> Ascii Null

Unfortunately, the company that I work for still uses Excel 2000 and Windows
2000
so, I am trying to make it all work happily in that environment.

The help file states that the format for the Read is

Open "TESTFILE" For Binary Access Read As #1

What I wrote is:

Open FullTRFFileName For Binary As #1

where my definition is

Public FullTRFFileName As String

so for the misleading words. I hope that this clarifies what I am doing.

 
Answer #4    Answered By: Vidos Fischer     Answered On: Feb 09

I kind-of suspected as much.
the Nulls are a going to present a problem.
Since Nulls are, well... basically nothing.
It's difficult to count how many discrete nothings precede "something".

What is it that you need to do with the data once you read it?

 
Answer #5    Answered By: Hoor Khan     Answered On: Feb 09

If FullTRFText is defined as a string and Line Input reads a string
terminated with an end-line of some sort, then Excel is probably quite
properly ignoring nulls.

I think that there's a "get" call that will read the file in smaller chunks.
If you need to read it byte by byte, then you probably need to use Get to
read into a character/byte variable or possibly a character/byte array.

(Sorry for the vague reply - a quick Google search wasn't very informative.)

 
Answer #6    Answered By: Hugo Williams     Answered On: Feb 09

Thank you everyone for the help on this. My biggest problem
was that I really did not understand why in some cases the
"read" would provide on thing, and then another time would provide
another thing.

In essence, I am reading  the information into a "copybook". Hence
there are several constant fields that I can check to determine what
I have read in.

 
Answer #7    Answered By: Amelia Schmidt     Answered On: Feb 09

The following method should also be valid for reading  a file.

Disclaimer: There may be a better way, but this works.

I am reading and writing serial port data with excel  and use string
variables to hold binary  data.
I use CHR$() to put binary data into the string variable and I use
ASC() to pull bytes out of the string, like this:
BinaryByte = Asc(Mid(data_msg, 6, 1))

Where data_msg is the string variable and this pulls one byte from
location 6 in the string.

Make it Hex(Asc(Mid(data_msg, 6, 1))) and you can see the bytes as hex
data the way you show below.

Where needed, I use a loop to (change the "6" here and) scan through
the bytes in the string

While it may sound strange, the data in a string variable is
actually no different that data in any other variable, namely bytes.
It is the way that data is interpreted by the software that makes it a
string. That is, a string variable is interpreted as a series of
ASCII codes and, therefore, it can be displayed as printable
characters, *IF* you request that they be printed. If I print my
particular strings, I see lots of garbage. My binary data is for
controlling a Ham radio that needs binary control codes and parameters.

I don't fully understand the example data below, nor how you are
actually seeing it. If I interpret correctly, the file data appears
to consist of byte values of &H00, &H20 and &H32 [interpreted as ASCII
NUL, Space and "2"]. Since a byte of 00 is a non printable ASCII
character code, it may very well be in the string (FullTRFText), just
not printing because of the way you are doing it. You should try to
scan through the string (FullTRFText) using MID() and see what the
actual bytes are.
If the nulls are still absent, then Excel is deleting them as mon
printable (as Dave S. says), but it seems to me that they should be there.
That said, one important issue will be the delimiter used in the
file. In my case, I read bytes from the port and have a binary
delimiter (&HFD) that I look for. If the file in question has a CR
(&H0D) delimiter, it can be read as strings and, I believe, all will
be well in the universe. If not, you can read individual bytes (one
at a time rather than by whole lines) and scan for the delimiter as I do.

Lastly, OPENing as Binary may be the cause. Excel tends to assume
that it knows what you want and can, at times, make conversions
between data types behind your back and confuse the heck out of you
(well... me anyway). If I'm reading this correctly, the file should
be opened as string or text rather than binary (I don't remember the
actual OPEN parameter name for a text file read). The half-life of
that knowledge chunk has been exceeded... (;-)

 
Didn't find what you were looking for? Find more on Reading Binary Files with Leading Binary Zeros Or get search suggestion and latest updates.




Tagged: