Getting Data In

How to remove the currency symbol etc. from a field before indexing?

sundareshr
Legend

This is what the data looks like in the source file (.csv). Notice the $156.03

09/26/13, 2013 , 09-Sep , Week-39 , Thu , -   , 4 ,, $156.03 ,, $156.03 ,100%, $39.01 ,,0:00, 13 , $12.00 ,

This is what I have in my \etc\system\local\props.conf

SEDCMD-makenumeric = s/\$(\d)/\1/g s/(\d)%/\1/g s/\$-//g s/(\d),(\d)/\1\2/g s/\s?,\s?/,/g

This is what the data looks like in raw mode in data preview (no currency symbol). SEDCMD appears to be working.

09/26/13,2013,09-Sep,Week-39,Thu,-  ,4,,156.03,,156.03,100,39.01,,0:00,13,12.00,

However, the $ symbol is back in the List and Table mode in Data Preview

09/26/13,2013,09-Sep,Week-39,Thu,-  ,4,,156.03,,156.03,100,39.01,,0:00,13,12.00,
Avg _ Cust = $39.01 Conv = 100% DOW = Thu Date = 09/26/13 Hrs Open = 13 Hrs Worked = 0:00 In_Store = $156.03 Month = 09-Sep No Sale = - Sale _ Hr = $12.00 Sales = 4 Sales Amt = $156.03 Wk No = Week-39 Year = 2013

If I go through with the data import, this search results in the field with $ symbol

index=* | table "Sales Amt"

Question: How do I get rid of the currency symbol (& other string characters) so a number can be stored as a numeric value when indexed?

0 Karma
1 Solution

jeffland
Champion

This is because your SEDCMD is applied to the rawdata, but (assuming you are using standard settings for csv data) your indexed fields are extracted from the rawdata prior to SEMDCMD, so they still contain the original data (with dollar sign). Read some more here where I came across the problem earlier.

You need to change your indexed field as well. If you don't know how to after reading up on the link I posted, feel free to ask further.

View solution in original post

jeffland
Champion

This is because your SEDCMD is applied to the rawdata, but (assuming you are using standard settings for csv data) your indexed fields are extracted from the rawdata prior to SEMDCMD, so they still contain the original data (with dollar sign). Read some more here where I came across the problem earlier.

You need to change your indexed field as well. If you don't know how to after reading up on the link I posted, feel free to ask further.

Get Updates on the Splunk Community!

Register to Attend BSides SPL 2022 - It's all Happening October 18!

Join like-minded individuals for technical sessions on everything Splunk!  This is a community-led and run ...

What's New in Splunk Cloud Platform 9.0.2208?!

Howdy!  We are happy to share the newest updates in Splunk Cloud Platform 9.0.2208! Analysts can benefit ...

Admin Console: A Single, Unified Interface for All Your Cloud Admin Needs

WATCH NOWJoin us to learn how the admin console can save you time and give you more control over the Splunk® ...