Reading HEX values from a database

[#1]

Hi,

I am trying to read the following from a progress database.

Field	Value
A	332400
B	Test product
C	322C303231332C31392F30382F31302C30333A32303A33342C2C2C2C302C2C2C302C30
D	12/12-2010

The information reads in exactly as above, filed C is a RAW data (HEX) representation of

2,0213,19/08/10,03:20:34,,,,0,,,0,0

I would like to be able to read each one of the comma separated values into its own QlikView field. I am at a total lost so any help would be appreciated.

Gavin.

[#2 · Accepted solution]

Hi,

you could use the script below to convert the Hex-String into a Ascii String.

org:
LOAD * INLINE [
A, B, C, D
332400, Test product, 322C303231332C31392F30382F31302C30333A32303A33342C2C2C2C302C2C2C302C30, 12/12-2010
];

tmpHexTable:
LOAD
recno()+31 AS Nr
, CHR(recno()+31) AS Ascii
, NUM(recno()+31,'(HEX)00') AS Hexa
AUTOGENERATE 95;

HexTable:
MAPPING
LOAD
Hexa, Ascii
RESIDENT tmpHexTable;
DROP TABLE tmpHexTable;

new:
LOAD
*
, MAPSUBSTRING('HexTable', C) AS C2
RESIDENT org;

DROP TABLE org;

Then, you could use the subfield function to get each of the Contents of the Comma seperated fields of C2.

Hope that helps.

Best regards

Michael

[#3]

Michael,

Thank you so much for your help, worked exactly how I was hoping. I can now read in all the required information and not have to worry about batch files dumping the data out over night. I added al little bit of code to strip out the UNICODE-8 characters that are on the end of a line

BuildHexTable:

LOAD

recno() AS Nr

,CHR(recno()-1) AS Ascii

,NUM(recno()+9,'(HEX)00') AS Hexa

AUTOGENERATE 1;

BuildHexTable:

LOAD

recno()+1 AS Nr

,CHR(recno()-1) AS Ascii

,NUM(recno()+12,'(HEX)00') AS Hexa

AUTOGENERATE 1;

Everything else worked brilliantly, would have struggled for a long time before thinking of using a mapping solution.

Thank you again for your time & help.

Gavin.

[#4]

Very good solution ... thanks!