Getting Data In

Import CSV data, multiple events on single line

Splunk Employee
Splunk Employee

Hi guys,
I have a report in CSV format that disappointingly the product exports only monthly reports and puts each day's data into two columns for the number of days of data available. I am trying to import the data into an index.
The data format I get is 720 rows (an event each two minutes) x number of days in the report in columns. The day split is denoted by a double comma (because the original xls file contains a blank column between each day) but using CSV import doesn't allow me to detect this as a LINE_BREAKER event (probably as the commas are being eaten by the CSV field split. I cannot seem to find a way to get it to split each line into the necessary individual events.

Any advice would be greatly appreciated. I've tried building this through props as well as playing with it in the GUI to no avail. Essentially I can get it to detect the first _time value using %b %d/%Y %H:%M which works for the line, then I simply get all the events of the line in a multi-value fields labelled Time and Volt, rather than individual event.

Any help appreciated.

Header row

sample data line.
Aug 01/2019 00:01,12.65V,,Aug 02/2019 00:01,12.63V,,Aug 03/2019 00:01,12.64V,,Aug 04/2019 00:01,12.63V,,Aug 05/2019 00:01,12.57V,,Aug 06/2019 00:01,12.63V,,Aug 07/2019 00:01,12.61V,,Aug 08/2019 00:01,12.60V,,Aug 09/2019 00:01,12.62V,,Aug 10/2019 00:01,12.64V,,Aug 11/2019 00:01,12.59V,,Aug 12/2019 00:01,12.63V,,Aug 13/2019 00:01,12.64V,,Aug 14/2019 00:01,12.63V,,Aug 15/2019 00:01,12.64V,,Aug 16/2019 00:01,12.60V,,Aug 17/2019 00:01,12.61V,,Aug 18/2019 00:01,12.57V,,Aug 19/2019 00:01,12.58V,,Aug 20/2019 00:01,12.52V,,Aug 21/2019 00:01,12.45V,,Aug 22/2019 00:01,12.62V,,Aug 23/2019 00:01,12.44V,,Aug 24/2019 00:01,12.60V,,Aug 25/2019 00:01,13.50V,,Aug 26/2019 00:01,12.49V,,Aug 27/2019 00:01,12.36V,,Aug 28/2019 00:01,12.45V,,Aug 29/2019 00:01,12.36V,,Aug 30/2019 00:01,12.39V,,Aug 31/2019 00:01,12.42V

0 Karma

Esteemed Legend

Like this:


[<Your sourcetype here>]
LINE_BREAKER = (,,|[\r\n]+)
TIME_FORMAT = %b %d/%Y %H:%M
REPORT-commalist_delims = commalist


DELIMS = ","
FIELDS = Time, Volt
0 Karma

Path Finder

In fact you can do it with this props.conf
LINE_BREAKER to split by two commas OR by line break
PREAMBLE_REGEX to remove all headers

TIME_FORMAT=%b %d/%Y %H:%M
0 Karma

Splunk Employee
Splunk Employee

The issues I'm finding is that the field delimiter value as a comma (required) conflicts with the line breaker double comma. The above still doesn't break the events, but puts one line's worth of events into a single event. I'm still trying.

0 Karma

Splunk Employee
Splunk Employee

I have been informed it's very unlikely that Splunk can make this happen with the comma clash. Recommendations to pre-process the data file monthly or bring in as it comes in into a temp index and do a collect function to bring into a new index, recommended a Metric index.

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...