How to Convert EBCDIC Character Overpunch Values to ASCII?

[SirRoshua]

I have tried everything I know to convert an EBCDIC Character Overpuch Currency Value into ASCII format with a floating point leading sign value.

For example, the following is the original state of the value (the comma I have added to seperate fields as a precursor of an import into SQL):

0000894A,0000638N,0000103},0000345F,0000111{

I need to convert this into the following:

89.41,-63.85,-10.30,34.56,11.10

There are approximately 10k records per file with about 15 currency values one after the other. I have no dificulties in inserting the decimal, or dropping of the leading zero's, nor converting the graphic sign into its consituent numerical value, but cannot seem be able to insert the negative sign when needed at the beginning of the value, when I do insert through a simple find and replace command I have only managed to do so at the need of the value string, at which point the value is not treated as numeric or negative, but rather as a text string by Excel (where I need to do further manipulation before importing into SQL) and SQL.

The full graphic sign table is as follows:
{ = 0
A = 1
B = 2
C = 3
D = 4
E = 5
F = 6
G = 7
H = 8
I = 9
} = -0
J = -1
K = -2
L = -3
M = -4
N = -5
O = -6
P = -7
Q = -8
R = -9

Any assistance would be greatly appreciated!

FYI: I am using UE v14, File is encoded DOS, from an original .DAT file sent to me from an outside source.

[SirRoshua]

I figured out the solution, had to do the following:

Had to leave it formatted as 000085.7Q in the text editor.
Dropped it into Excel and did the Find/Replace to leave it formulated as 000085.78- ensuring all the columns in question were formatted as TEXT.
Imported into an Access temporary table where the corresponding columns were formatted as CURRENCY. Access automatically reformatted to an accepted currency format, dropping off the leading zeros and moving the negative from back to front (well actually putting the value in parentheses, but same diff).
Then ran an Append query to move the records in Access temporary table to the SQL database without further any issues.
Also created a delete all records in the temp table so that the container would be empty for the next import.

[jorrasdk]

Hi SirRoshua

Using your exact example:
0000894A,0000638N,0000103},0000345F,0000111{
I have created the script below to convert it into:
89.41,-63.85,-10.30,34.56,11.10

Since we don't know the layout of your source file it might only work after you have inserted the commas.

I hope the script is self explanatory otherwise consult the UE help on scripting or ask again.

Code: Select all

RegExp.prototype.toUEregexp = function() { return this.toString().replace(/^\/|\/$/g, ""); }; /* remove starting and ending / as UE does not recognize these for regexp */

var findEbcdicNumericRegexp = /\b\d{7}[{ABCDEFGHI}JKLMNOPQR]/; // This will find 8 digit Ebcdic signed/zoned numerics
var numPoints = 2; // How many decimal points in the result
var decPoint = "."; // decimal point

var unZone =  [ // Return 2 dim. array with the unzoned numbers and corresponding sign
["{","0",""],  ["A","1",""],  ["B","2",""],  ["C","3",""],  ["D","4",""],  ["E","5",""],
["F","6",""],  ["G","7",""],  ["H","8",""],  ["I","9",""],  ["}","0","-"], ["J","1","-"],
["K","2","-"], ["L","3","-"], ["M","4","-"], ["N","5","-"], ["O","6","-"], ["P","7","-"],
["Q","8","-"], ["R","9","-"]
];

UltraEdit.activeDocument.top(); // Go to top of active document
UltraEdit.perlReOn(); // use Perl regexp engine

UltraEdit.activeDocument.findReplace.replaceAll=true; // replace all while we are at it for speed
UltraEdit.activeDocument.findReplace.regExp=true; // use regExp for searching

while (UltraEdit.activeDocument.findReplace.find(findEbcdicNumericRegexp.toUEregexp())) { // find all occurrences
   var foundEbcdicNum = UltraEdit.activeDocument.selection; // Retrieve found number into variable
   var asciiNum = convertEbcdicNum(foundEbcdicNum); // convert using local function convertEbcdicNum()

   UltraEdit.activeDocument.findReplace.regExp=false; // don't user regExp for replace
   UltraEdit.activeDocument.findReplace.replace(foundEbcdicNum,asciiNum); // replace all from here to end
   UltraEdit.activeDocument.findReplace.regExp=true; // use regExp for searching
}

function convertEbcdicNum(num) { // inline function to convert from Ebcdic zoned dec to ascii dec
   num = num.replace(/^0+/g,""); // remove leading zeroes

   zonedDigit = num.substr(num.length - 1,1); // get the zoned digit
   var sign = ''; // assume positive
   for (var i=0;i<unZone.length;i++) { // loop through table with zoned digits
      if(unZone[i][0]==zonedDigit) { // found
         zonedDigit = unZone[i][1]; // get ascii digit
         sign = unZone[i][2]; // get sign
         break; // break loop
      }
   }
   num = sign + num.substr(0, num.length - 1)+zonedDigit; // add sign and ascii digit.
   
   if (numPoints > 0) { // insert decimal point
      num = num.substr(0,num.length - numPoints) + decPoint + num.substr(num.length - numPoints);
   }
   
   return num; // return ascii value
}