Avatar of SirRoshua
SirRoshua
 asked on

How do I Convert EBCDIC Character Overpunch Values to ASCII?

This is a difficult question worth 500 points to whom ever can provide a solution ASAP, as I need to have a working solution in place by 4/14/2008.

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 UltraEdit v14, File is encoded DOS, from an original .DAT file sent to me from an outside source.
SoftwareDatabases

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SirRoshua

ASKER
Thanks cactus_data, that was indeed the solution.

To get this to work I 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 table as suggested 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 table to the SQL database without further any issues.

Thanks for the assist!
SirRoshua

ASKER
Thank cactus_data, you saved my arse on this one, had a deadline looming that would have thrown a lot of dev timeline work out of wack.

Cheers - Roshua!
Gustav Brock

Great!
Thanks for the feedback.

/gustav
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Georgie101

The actual answer to this seems to have disappared. I see "If you have access to Microsoft Access, it reads/imports this format as a numeric value right away. " However 'it' seems to be missing. Where would I find 'it'?
Gustav Brock

Nothing has vanished; Microsoft Access is "it".

It is for download somewhere at Microsoft's site as a 30 or 90 days (or is it 30 launches?) evaluation version for free.

/gustav