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!

Get ready to show some Splunk Certification swagger at .conf24!

Dive into the deep end of data by earning a Splunk Certification at .conf24. We're enticing you again this ...

Built-in Service Level Objectives Management to Bridge the Gap Between Service & ...

Now On-Demand Join us to learn more about how you can leverage Service Level Objectives (SLOs) and the new ...

Database Performance Sidebar Panel Now on APM Database Query Performance & Service ...

We’ve streamlined the troubleshooting experience for database-related service issues by adding a database ...