CODEWITHSUNDEEP
spotfire
Albert
Albert

Reputation: 135

How can I create integer columns from hex strings in Spotfire?

I am importing a csv-file containing a data column with semicolon separated bytes in hexadecimal format like this:

06;03;58;1C;05;F5;D2;70;05;F5;DF;...

(Yes, this is all one column in the comma separated file..)

I would like to parse this column into a number of columns with 32-bit values and convert them to decimal:

06;03;58;1C -> 0x0603581C -> 100882460
05;F5;D2;70 -> 0x05F5D270 -> 99996272 ...

Here is one of my first (futile) attempts to create the first column:

Integer(Concatenate("0x",
    Mid([data], 1, 2), 
    Mid([data], 4, 2), 
    Mid([data], 7, 2), 
    Mid([data], 10, 2)))

Any suggestions on how to accomplish this? I am trying to avoid the extra step of pre-processing the csv-file in Excel using this very similar calculation:

HEX2DEC(CONCATENATE(
    MID($M2,1,2),
    MID($M2,4,2),
    MID($M2,7,2),
    MID($M2,10,2)))

Upvotes: 1

Views: 1475

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30815

The easiest way is probably to use either IronPython or the R interface. However, here's a version using just calculated columns (quite ugly, but gets the job done):

  • extract the one-character substring for pos 1, 2, ...
  • replace each character with its numeric value, i.e. 'A' -> '10', 'B' -> '11', ...
  • convert it to an integer Int1, Int2, ...
  • compute the resulting value as ((Int1*16) + Int2)*16 + ...

Here are the column expressions for the calculated columns (I only did the first two characters):

Int1

Integer(
  Substitute(
    Substitute(
     Substitute(
       Substitute(
         Substitute(
           Substitute(
             Mid([Input],1,1),
             "A","10"),
           "B","11"),
         "C","12"),
       "D","13"),
   "E","14"),
 "F","15"))

Int2

Integer(
  Substitute(
    Substitute(
     Substitute(
       Substitute(
         Substitute(
           Substitute(
             Mid([Input],2,1),
             "A","10"),
           "B","11"),
         "C","12"),
       "D","13"),
   "E","14"),
 "F","15"))

Result

Integer(([Int1]*16) + [Int2])

Upvotes: 0

Related Questions