Getting Data In

How to remove Commas In a Field Value?

cdson
Explorer

Hello!

I have recently just downloaded Splunk on my MAC for experimenting/practicing searching and dashboarding. I just picked a random csv file that has planetary information.

One of the fields in my .csv file has a mix of numbers without commas, and three numbers that have a comma.

EX: 59,800

I think this is causing those values to not show up in my visualization.

Is there a way to remove said comma from the field value? I tried using this below in the source code under the visualization but it says it's an unknown option name.

<option name="useThousandSeparators">false</option>

 

Labels (1)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

So, how are you getting your data from that CSV into Splunk - have you ingested the data or have you made a Splunk lookup and are using 

| inputlookup your_csv.csv

If you are dealing with a CSV lookup, then any commas in the field must be quoted, otherwise Splunk will think they are separate fields in the lookup, so it will break the parsing.

However, if you have got those fields and they just contain commas, then it's simple to replace commas. 3 possible ways, but these are the most common 2

| rex field=your_field mode=sed "s/,//g"
| eval your_field=replace(your_field, ",", "")

The rex command uses sed syntax to replace all commas with empty string. The eval statement does the same.

If the field has a comma, Splunk will not treat is as a number.

View solution in original post

0 Karma

bowesmana
SplunkTrust
SplunkTrust

So, how are you getting your data from that CSV into Splunk - have you ingested the data or have you made a Splunk lookup and are using 

| inputlookup your_csv.csv

If you are dealing with a CSV lookup, then any commas in the field must be quoted, otherwise Splunk will think they are separate fields in the lookup, so it will break the parsing.

However, if you have got those fields and they just contain commas, then it's simple to replace commas. 3 possible ways, but these are the most common 2

| rex field=your_field mode=sed "s/,//g"
| eval your_field=replace(your_field, ",", "")

The rex command uses sed syntax to replace all commas with empty string. The eval statement does the same.

If the field has a comma, Splunk will not treat is as a number.

0 Karma

cdson
Explorer

Thank you! This worked!

0 Karma

cdson
Explorer

To add, since there's a .tostring and fieldformat option to format values is there a way in the raw search to remove commas?

0 Karma
Get Updates on the Splunk Community!

Take Your Breath Away with Splunk Risk-Based Alerting (RBA)

WATCH NOW!The Splunk Guide to Risk-Based Alerting is here to empower your SOC like never before. Join Haylee ...

Industry Solutions for Supply Chain and OT, Amazon Use Cases, Plus More New Articles ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Enterprise Security Content Update (ESCU) | New Releases

In November, the Splunk Threat Research Team had one release of new security content via the Enterprise ...