Need to place 8-bit byte arrays in Excel file, then export

[rlbrinkman]

Thank you in advance for your help with my issue.


I have an Excel spreadsheet that computes rows containing a 1-7 digit number, and 64 decimal cells that can contain values that range only from 0 to 255. For consumption in the end product the 64 cells need to be generated into a single cell that contains data that is compressed into 64 contiguous 8-bit bytes. What I would LIKE to have is a function like DEC2BYTE to do the job, similar to the way DEC2HEX (for instance) reformats data. After reformatting the 64 8-bit bytes could be concatenated together. But I don't see any function that is promising to get the job done. I'm therefore guessing that VBA will be required, but I'm not sure how to generate it.


Here's a sample of the Excel spreadsheet input:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]...[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]...[/TD]
[TD]BP[/TD]
[/TR]
[TR]
[TD]Time (ms)[/TD]
[TD][/TD]
[TD]Data0[/TD]
[TD]Data1[/TD]
[TD]Data2[/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]Data5[/TD]
[TD]Data6[/TD]
[TD]Data7[/TD]
[TD]...[/TD]
[TD]Data63[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD][/TD]
[TD]201[/TD]
[TD]43[/TD]
[TD]178[/TD]
[TD]0[/TD]
[TD]56[/TD]
[TD]244[/TD]
[TD]98[/TD]
[TD]137[/TD]
[TD]...[/TD]
[TD]82[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]190[/TD]
[TD]73[/TD]
[TD]234[/TD]
[TD]123[/TD]
[TD]31[/TD]
[TD]123[/TD]
[TD]45[/TD]
[TD]...[/TD]
[TD]67[/TD]
[/TR]
[TR]
[TD]1883[/TD]
[TD][/TD]
[TD]47[/TD]
[TD]89[/TD]
[TD]111[/TD]
[TD]187[/TD]
[TD]3[/TD]
[TD]77[/TD]
[TD]32[/TD]
[TD]222[/TD]
[TD]...[/TD]
[TD]145[/TD]
[/TR]
</tbody>[/TABLE]


The data records will not all be available at the same time and they will need to be sorted; therefore, I will create a tab and keep appending records until completed. The desired result in the created tab:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1234567[/TD]
[TD]XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX[/TD]
[/TR]
</tbody>[/TABLE]
where, X = an 8-bit byte containing unsigned values ranging from 0..255

The information in column B may contain what would normally be interpreted control characters, etc.; it need not be readable or look pretty - it just needs to contain the information.

The final step, when the created tab is complete, is to export to a CSV or other delimited file. This file would look like:

1234567,XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
9,XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
143,XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX​

again, X = an 8-bit byte containing unsigned values ranging from 0..255; 64 8-bit bytes total.

Your help is appreciated.

 

[thisoldman]

What about DEC2BIN?
=DEC2BIN(B1,8)

 

[rlbrinkman]

Unfortunately that just gives me 8 bytes in ASCII format. If cell B1 contains the value 23 then the DEC2BIN function yields the binary result of 00010111.

 

[thisoldman]

If you had the function DEC2BYTE, what would the results of these be

=DEC2BYTE(0)
=DEC2BYTE(1)
=DEC2BYTE(13)
=DEC2BYTE(15)

 

[rlbrinkman]

=DEC2BYTE(0) ==> NUL - null character (\n)
=DEC2BYTE(1) ==> SOH - Start of Heading
=DEC2BYTE(13) ==> CR - Carriage Return
=DEC2BYTE(15) ==> SI - Shift In

These, of course, are all unreadable control characters. It would the kind of stuff you see when you open a binary file (such as a .JPG image) with the Notepad editor - a bunch of strange symbols that mean something to a binary application, but are not meant to be human readable.

 

[thisoldman]

Will =CHAR(B2) work? You'll get the ascii characters, including the control characters.

How are you going to get them out of Excel? If you're using Excel 2016, saving as Unicode text seems the cleanest. There will be a carriage return, ^M, at the end of every line of your Excel export, but there won't be added quotation marks.

Excel has its own standards for text files. It's a legacy of Windows code pages for different languages and insisting that UTF-16LE equals Unicode. Hope this works for you.<hint></hint>

 

[rlbrinkman]

CHAR(B2) comes pretty close - in fact, I should have thought of that. All values from 0..255 work with the CHAR() function except that CHAR(0) kicks out a #VALUE ! error. Bummer.

Export to Unicode text works except it kicks out two bytes for each value, but again that's a step in the right direction.

I think I'm moving to a revised approach, thinking that there is no way to avoid using a VBA script. I'm looking at having the created tab contain the 64 values as decimal values in 64 cells. Then, a VBA script can output the 64 values as 8-bit bytes.

Your responses are helping me think through a solution, so they are very much appreciated!

 

[thisoldman]

I believe you'll need to explore VBA's Put statement: https://msdn.microsoft.com/en-us/VBA/Language-Reference-VBA/articles/put-statement

The learning curve doesn't look too steep, even considering that VBA would not be my choice of scripting language for writing binary files.