Reading from text file

[PeterWieland]

Hi,

I need to read data from a text file produced by another application. It is in a fixed length, non-delimited format.

Reading the file with a Hex viewer, it has the following format:

1st 4 bytes - ID Number
2nd 21 Bytes - Forename
3rd 21 Bytes - Surname

etc up to 991 bytes, which is the length of the record.

Using the open and get commands, I can read the data into a string.

Dim varString As String * 991
Open "myfile.txt" For Random Access Read As #1 Len = 991
Get #1, 1, varString
Close #1

Now, my problem. I need to get the first 4 bytes into a numeric variable, then the names into string variables and ignore the rest.
If I use the Left function I get a mismatch error.

Any ideas?

Peter

 

[FoFa]

val(left(varstring,4)) will convert it to numeric for you

 

[PeterWieland]

Thanks for the reply,

I have tried the Val function, but I always get zero. I think the problem is that the first 4 bytes are being interpreted as ASCII characters, but because they start from 1, most of them are control characters. Also, the byte order is reversed, i.e. Record 1 is 01 00 00 00, Record 2 is 02 00 00 00 etc, the numbers counting up in Hex. I think I need to read the first four bytes from the text file seperately from the rest, but using Open/Get I can't work out how to do it.

Peter

 

[PeterWieland]

Hi Pat,

Tried that as well! No matter what data type the first field is defined as, Access still tries to interpret the first byte as an ASCII character. As soon as the 4th record is read, it is interpreted as ASCII 04, which is end of transmission, and the import stops, and the first 3 records have errors.

I have attached a small portion of the offending file. I would really appreciate it if you could take a look. I only need the first few fields of the record, ie Number, Title, Forename, Surname. The rest is not required.

Thanks in advance

Peter

 

[FoFa]

Old DOS Basic had function to convert this actual numeric values to ascii numeric values. Newer versions of VB and VBA do not have these functions anymore. I wrote one once (don't have it anymore) but basically it is:
first byte, take as binary value, each byte after is a 256 value increase. So second byte is the amount of 256's (or is it 255's I can't remember). Basically what happens is when the first byte overflows (256) it adds 1 to the second byte, reset the first byte to all zeros, when the second one fills ups, it adds one to the third byte, zeros the second, etc. So basically the formala is value of first byte + value of second byte * 256 + value of third byte * (whatever that is) + value of 4th byte * (whatever that is) gives you your numeric value. It is a reversed binary counter is all.