topic Re: CSV comma handling with additional commas in Getting Data In

CSV comma handling with additional commas

himynamesdave — Fri, 20 Dec 2013 11:40:37 GMT

Hello fellow Splunkers!

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

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.

Re: CSV comma handling with additional commas

kristian_kolb — Fri, 20 Dec 2013 12:50:32 GMT

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.

Re: CSV comma handling with additional commas

richgalloway — Fri, 20 Dec 2013 12:55:58 GMT

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.

Re: CSV comma handling with additional commas

himynamesdave — Fri, 20 Dec 2013 14:03:02 GMT

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

Re: CSV comma handling with additional commas

himynamesdave — Fri, 20 Dec 2013 14:03:08 GMT

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

Re: CSV comma handling with additional commas

kristian_kolb — Fri, 20 Dec 2013 14:19:35 GMT

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

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

Re: CSV comma handling with additional commas

richgalloway — Fri, 20 Dec 2013 14:23:50 GMT

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.