As I have mentioned in previous posts there are several processes I have written and responsible for that takes data from a CSV (Comma Separated Values) files and updates files on the IBM i (AS400). One vendor has trouble sticking to the agreed standard of not having double quotes ( " ) in the description fields to denote inches.
The double quotes are interpreted as the start of an alphanumeric field which does not end until another double quote in encountered. This results in the extra fields being created at the end of the record or a loss of several records which are combined into one big field. I prevented this from happening by using the %XLATE built in function in RPGLE/RPG IV to replace all occurrences of the double quote with a space.
But this week they sent a right double quotation mark ( ” ), which were translated to some strange hexadecimal characters by the ASCII to EBCDIC translation table.
How can I replace these hexadecimal characters?
I tried cut-n-pasting the displayed characters into a %XLATE statement, but that did not work.
Then it struck me… if I could find what those hexadecimal values are I could try to use them in a %XLATE. There are many third part tools, for example DBU, that allow you to view the hexadecimal values, but I endeavor in this blog to use just the IBM commands and features so everyone can do it. Therefore, I used the DSPF command, see below:
The first screen displays the file in character mode. I pressed F10 to display hex.
The interesting part of the screen is displayed below:
- - - - + - - - - * - - - - + - - - - * ----+----*----+----* 40404040 40404040 40404040 405C5C5C 5C5C5C5C ******* F3BA2014 40E740F3 BA201440 E740F1F2 BA201440 3[ X 3[ X 12[ 40404040 40404040 40404040 405C5C5C 5C5C5C5C *******
Comparing the character representation, on the right, I am able to find the hexadecimal values on the left, highlighted in red.
Now I know that the hexadecimal values I need to replace are:
Now I could write the lines I needed to replace these in my RPGLE program:
01 FLD01 = %xlate(x'BA':' ':FLD01) ; 02 FLD01 = %xlate(x'20':' ':FLD01) ; 03 FLD01 = %xlate(x'14':' ':FLD01) ;
The x before the hexadecimal value denotes that it is a hex value.
The result is shown below:
- - - - + - - - - * - - - - + - - - - * ----+----*----+----* 40404040 40404040 40404040 405C5C5C 5C5C5C5C ******* F3404040 40E740F3 40404040 E740F1F2 40404040 3 X 3 X 12 40404040 40404040 40404040 405C5C5C 5C5C5C5C *******
Those hexadecimal codes have been translated to blank, 40.
You can learn more about these on the IBM website:
This article was written for IBM i 7.1, and it should work with earlier releases too.