question

gutofrs avatar image
0 Votes"
gutofrs asked MartinJaffer-MSFT commented

Azure Data Flow convert Hex to Integer

Good morning everyone.

We are using data factory and data flow to transform and after consume some data in power bi.

There is an column called "time" in csv we are transforming, it's type is hexadecimal, for instance "6022D4D8". This value it's an hex representation of timestamp in seconds.

0x6022D4D8 = 1612895448 = 2021-02-09 18:30:48 (in seconds)

Directly, with the value in plain text, it's possible to get the desire result:

 toTimestamp(seconds(toInteger(0x6022D4D8)))

But, using the column string "time" we can't do it.

We tried everything: toInteger('0x' + time), unhex(time).

Could you helps us?

Kind regards

azure-data-factory
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

toInteger takes a format if you look in the function help. Find the format that you want in
https://docs.oracle.com/javase/7/docs/api/java/text/DecimalFormat.html

0 Votes 0 ·

@KeshavKiran-MSFT hex is not included in DecimalFormat

0 Votes 0 ·

Hello @gutofrs and welcome to Microsoft Q&A.

I have also tried to find a way to convert hex to integer. It has been elusive so far, so I am writing an expression to do so. I am very close to having it complete. I am just running into a data type issue. Below is the code so far.

 reduce(
 mapIndex(
 map(
 slice(split(substring(time,3,length(time)-2),''),1,length(time)-2),
 case( #item == 'A', 11, #item == 'B', 12, #item == 'C', 13, #item == 'D', 14, #item == 'E', 15, toInteger(#item))
 ),
 toLong(#item) * power(16,length(time) - 2 - toInteger(#index) )
 ),
 0, toLong(#acc) + toLong(#item) , #result)

@Kiran-MSFT can you help me with this? I get error Higher order function cannot be evaluated with prior data types and Return types do not match

0 Votes 0 ·
Kiran-MSFT avatar image
1 Vote"
Kiran-MSFT answered

Dataflow functionality is continuously improved.
You can use toInteger(hexValue, ‘%x’) to convert from hex to base 10 integer. toLong also allows the same. Make sure to trim 0x from the string using substring or right functions.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

gutofrs avatar image
1 Vote"
gutofrs answered MartinJaffer-MSFT commented

After tried everything and also tried to work on @MartinJaffer-MSFT solution, we were not able to make the conversion on data factory natively, so we created an Azure Function to do the job and insert it on Data Factory pipeline just before the data flow, converting every hex data to integer in C#.
That's not the best solution, but we can remove that when Microsoft create a function inside Data Flow.
Thank you anyway and best regards

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for letting us know the results.

0 Votes 0 ·