1

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)))
Albert
  • 135
  • 2
  • 7

1 Answers1

0

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])
Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
  • Ok, thanks! I am looking into IronPython, I need to do something like this, right? http://stackoverflow.com/questions/25533046/how-to-create-a-data-table-on-the-fly-in-spotfire-via-python – Albert Dec 10 '15 at 09:20
  • It depends on whether you want to create a new data table or just want to add columns to an existing data table. The question you linked is about adding a completely new data table - adding columns to an existing data table is a little bit different. However, using IronPython has the slight disadvantage that it's quite difficult (if not outright impossible) to automatically update the added columns - you always need something to trigger the IronPython script (usually an action control, e.g. a button) whereas a calculated column is automatically updated whenever the source data changes. – Frank Schmitt Dec 10 '15 at 11:31
  • I see. I did not mention this in the question, but I need to create 100 columns so it seems easier to handle it in IronPython where I do not have to do them all one by one. Regarding the tables, it does not really matter, I can just as well add the columns to the original table. – Albert Dec 10 '15 at 14:21