topic Re: Import CSV data, multiple events on single line in Getting Data In

Import CSV data, multiple events on single line

jmeager_splunk — Sun, 08 Sep 2019 05:41:55 GMT

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
Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt,,Time,Volt

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

Re: Import CSV data, multiple events on single line

vgtk4431 — Wed, 30 Sep 2020 02:06:19 GMT

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

[new_sourcetype]
SHOULD_LINEMERGE=false
LINE_BREAKER=(,{2})|([\r\n]+)
NO_BINARY_CHECK=true
PREAMBLE_REGEX=Time.*
TIME_FORMAT=%b %d/%Y %H:%M

Re: Import CSV data, multiple events on single line

jmeager_splunk — Wed, 11 Sep 2019 01:48:43 GMT

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.

Re: Import CSV data, multiple events on single line

jmeager_splunk — Wed, 11 Sep 2019 02:01:35 GMT

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.

Re: Import CSV data, multiple events on single line

woodcock — Tue, 17 Sep 2019 19:53:19 GMT

Like this:

props.conf:

[<Your sourcetype here>]
SHOULD_LINEMERGE = false
LINE_BREAKER = (,,|[\r\n]+)
TIME_PREFIX = ^
TIME_FORMAT = %b %d/%Y %H:%M
PREAMBLE_REGEX=Time.*
REPORT-commalist_delims = commalist

transforms.conf:

[commalist]
DELIMS = ","
FIELDS = Time, Volt