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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...