Getting Data In

How to parse the British Pound £ sign for currency fields?

splunkuser_uk
New Member

Hi,

I'm relatively new to Splunk and trying to ingest a cav of transactions in GBP in the format £123.45. I have these in a CSV and ideally want them formatted as currency so that I can analyze in Splunk. When the CSV is ingested the fields are displayed as a string not recognizing the £ sign - I've tried various convert options including (auto) and num but not having much luck.

0 Karma

aaraneta_splunk
Splunk Employee
Splunk Employee

@splunkuser_uk - Were you able to test out gokadroid's second possible solution? Did it work? If yes, please don't forget to resolve this post by clicking on "Accept". If you still need more help, please provide a comment with some feedback. Thanks!

0 Karma

gokadroid
Motivator

Assuming as per your comments if string £1,838.38 is coming in currency field as \xA31,838.38 then how about trying this:

  1. Ensure currency has no commas to start with (if any)
  2. Extract everything after \xA3 from currency field into a new field myCurrency
  3. Format back myCurrency to use the £ symbol as a string.

Try below search which shall give you the results needed as stated above.

your base query to return your currency field
 | rex field=currency mode=sed "s/,//g"
 | rex field=currency "A3(?<myCurrency>.*)"
 | fieldformat myCurrency ="£".tostring(myCurrency , "commas") 
 | stats sum(myCurrency )

Hope it helps. Below is the sample makeresults command i used to verify it was working.
alt text

gokadroid
Motivator

How about trying this and see if it works out, assuming string is coming in currency field as £ 42 or £ 42.0 or £42 :

your base query to return your currency field
| rex field=currency "£\s*(?<myCurrency>.*)"
| fieldformat myCurency="£".tostring(myCurrency, "commas")

See example here

splunkuser_uk
New Member

Hi - thanks for your reply. I tried your suggestion but not having much luck. The problem is the fields in the CSV which contain a currency amount i.e the figure £1,838.38 are being ingested as \xA31,838.38 - the £ symbol is ingested as \xA3 rather than a "£"

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...