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!

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...

Major Splunk Upgrade – Prepare your Environment for Splunk 10 Now!

Attention App Developers: Test Your Apps with the Splunk 10.0 Beta and Ensure Compatibility Before the ...

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

What are Community Office Hours?Community Office Hours is an interactive 60-minute Zoom series where ...