CSV comma handling with additional commas

[himynamesdave]

Hello fellow Splunkers!

I am having some problems with the format of my data and indexing it correctly in Splunk.

LINK

The data is parsed into a CSV files, with default comma delimiters (ignore extension of above file).

As some of the fields contain plain paragraph text, often with additional commas for punctuation, I cannot tell Splunk to separate fields by comma. Here's an example:

Field1\, Field2\, Field 3, with Paragraphs\, Field 4

IFX has worked with inconsistent results.

Without writing a very complicated regex, is there a way Splunk can pick-up CSV inserted commas? I am certain once the data is read there is no difference between normal commas and CSV commas, but I'm hoping there may be some other neat trick like this to solve the problem.

Any ideas?

Thanks!

edit: I have no control over the format of the data and therefore cannot alter delimiter type in file.

[richgalloway]

If you have control over how the CSV files are created, change them to put quotation marks around the fields with embedded commas. It may be easier to quote all fields. Once you do that, modify your Splunk transforms to strip the commas during indexing.

[richgalloway]

Regex may be your only answer. Try something like this:

(?<Field1>[^,]*?),\s(?<Field2>[^,]*?),\s(?<Field3>.*),\s(?<Field4>[^,]*)

That should allow for commas only in Field3.

[himynamesdave]

edit: I have no control over the format of the data and therefore cannot alter delimiter type in file.

[kristian_kolb]

Do you have any control of the generation of the CSV file? In that case, perhaps you can choose a different delimiter, e.g. a pipe, semicolon, # etc, that cannot occurr in your data.

Then it would be very simple to extract the fields with a REPORT in props.conf and FIELDS/DELIMS in transforms.conf.

props.conf

[your_sourcetype]
REPORT-blah = hash_delim

transforms.conf

[hash_delim]
DELIMS = "#"
FIELDS = field1, field2, field3 etc

---

Or, if you can't change the format, or if there's just one known field where the extra commas could occurr, you could set up an EXTRACT in props.conf

props.conf

[your_sourcetype]
# commas could be in field 4
EXTRACT-second_round =  ^(?<a1>[^,]*),(?<a2>[^,]*),(?<a3>[^,]*),(?<a4>.*),(?<a5>[^,]*),(?<a6>[^,]*),(?<a7>[^,]*)

But the first option is probably better.

/K

[kristian_kolb]

So, try the second option. You can do it with rex as well;

...| rex "^(?<a1>[^,]*),(?<a2>[^,]*),(?<a3>[^,]*),(?<a4>.*),(?<a5>[^,]*),(?<a6>[^,]*),(?<a7>[^,]*)"

/k

[himynamesdave]

edit: I have no control over the format of the data and therefore cannot alter delimiter type in file.