Inserting Hex values into Excel
Im new to Excel and am trying to Concatenate a number of cells together and sperate them by a single character represented by a Hex "4F". The combined cells will then be written to a file on my PCs C drive by way of a script. I will Transfer the file to a Mainframe PDS. Ive used HEX2DEC and its variations, but it always takes up two positions in the PC file and is also incorrect in the Mainframe file. How can I get the single hex character to appear? Any help would be appreciated.
Software/Hardware used:
Software/Hardware used:
ASKED:
October 4, 2010 9:51 PM
UPDATED:
November 10, 2010 5:27 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
All Answer Wiki Contributors:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
Have you tried using a verticle bar (|) ASCII 7c in your concatenation. That should translate to a verticle bar (|) Ebcdic 4f on upload. Of course this assumes that your mainframe is an EBCDIC machine.
BCP; I’ve tried the verticle bar character, a cell reference with CHAR(79), CODE(“I”), CHAR(124) which is the numerical value of the verticle bar & dec2hex(124). I either get a value that takes up 2 positions (ie dec2hex(124) = 7c, or a sigle value like CAHR(79 = O, which after the FTP look like 7c or O. If CODE(“|”) is 124 & CHAR(124) is veritcle bar, then when used as the delimiting character it looks fine when viewed in EXCEL, but after the FTP it is a hex 6A (a broken verticle line) and I need to manually change it to an X”4F”. Maybe I’m typing the formula incorrectly. Can you show me what you think it should be. Will try anthing at this point.
It looks like the ASCII to EBCDIC translation table you are using is not the standard one supplied by IBM which translates
; ASCII-to-EBCDIC table
;
; 00 01 02 03 04 05 06 07 08 09 0A 0B 0C 0D 0E 0F
;
00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F ; 00 ;
10 11 12 13 3C 3D 32 26 18 19 3F 27 22 1D 35 1F ; 10 ;
40 5A 7F 7B 5B 6C 50 7D 4D 5D 5C 4E 6B 60 4B 61 ; 20 ;
F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C 7E 6E 6F ; 30 ;
7C C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6 ; 40 ;
D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 AD E0 BD 5F 6D ; 50 ;
79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 ; 60 ;
97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 4F D0 A1 07 ; 70 ;
00 01 02 03 37 2D 2E 2F 16 05 25 0B 0C 0D 0E 0F ; 80 ;
10 11 12 13 3C 3D 32 26 18 19 3F 27 22 1D 35 1F ; 90 ;
40 5A 7F 7B 5B 6C 50 7D 4D 5D 5C 4E 6B 60 4B 61 ; A0 ; 3
F0 F1 F2 F3 F4 F5 F6 F7 F8 F9 7A 5E 4C 7E 6E 6F ; B0 ;
7C C1 C2 C3 C4 C5 C6 C7 C8 C9 D1 D2 D3 D4 D5 D6 ; C0 ;
D7 D8 D9 E2 E3 E4 E5 E6 E7 E8 E9 AD E0 BD 5F 6D ; D0 ;
79 81 82 83 84 85 86 87 88 89 91 92 93 94 95 96 ; E0 ;
97 98 99 A2 A3 A4 A5 A6 A7 A8 A9 C0 4F D0 A1 07 ; F0 ;
The Excel code I would use is =CONCATENATE(A1,”|”,B1,”|”,C1)
or if you prefer =CONCATENATE(A2,CHAR(124),B2,CHAR(124),C2)
The other possibility is that when you save the file in your macro you are saving it in DOS format rather than ANSI. That would convert the solid vertical bar to a broken vertical bar.
Hope this helps.
BCP; I tried using CHAR(124) but had the same issue. I’ve come to the same conclusion about the conversion table but am not sure what my other options are. Selecting the “EBCDIC” option from the Rumba transfer screen doesn’t work, but when I don’t select it I get the correct pipe character but everything else garbage. Do you know of an option I could use when performing the Rumba transfer that would use another conversion table? I couldn’t find one in any of my research.