topic Re: How to export to CSV when fields have commas in Splunk Search

How to export to CSV when fields have commas

cherrypick — Tue, 30 Jul 2024 07:15:50 GMT

My data has a tables{}.values{} containing a list of lists. Within each list there is data. Sample data below. When I try to extract this search to csv via job id, it's not containing tables{}.values{} data within a single cell and instead treating it as it's own field as it's comma delimited. How can I keep all the data within a singular field when exporting to CSV?

Sample data:

test@email.com
The following was found, on website: google.com, by test user, with id:testuser, extracted from test.txt, on date testdate,another test field.

Re: How to export to CSV when fields have commas

ITWhisperer — Tue, 30 Jul 2024 07:51:32 GMT

Have you tried using double quotes around the field values with commas in e.g.

field 1,"field2, with commas", field3

Re: How to export to CSV when fields have commas

cherrypick — Tue, 30 Jul 2024 08:02:58 GMT

How would you do this? I've seen solutions that replace the commas with null but I'm not sure how to encapsulate field values with commas in a double quote, especially with field values that have multiple commas inside.

Re: How to export to CSV when fields have commas

PickleRick — Tue, 30 Jul 2024 11:43:49 GMT

OK. Where are you exporting this from? Splunk should enclose the values in double quotes (and use double double quotes for the double quotes within a field value) when exporting search results.

So where are you exporting from/to?

Re: How to export to CSV when fields have commas

cherrypick — Wed, 31 Jul 2024 01:39:18 GMT

I'm using the splunk search API to convert search job to csv and downloading it. Unfortunately in this case splunk does not enclose values in double quotes.

/api/search/jobs/$alert_data$/results?isDownload=true&timeFormat=%FT%T.%Q%3Az&maxLines=0&count=0&filename=alert_data&outputMode=csv

Re: How to export to CSV when fields have commas

PickleRick — Wed, 31 Jul 2024 04:35:26 GMT

Something got converted here 😉 &->&

But to the point - if the csv export works badly, it's a material for support case.

Re: How to export to CSV when fields have commas

cherrypick — Wed, 31 Jul 2024 04:54:26 GMT

the &amp is HTML encoding to escape the & character. Exporting to CSV works via the API but when field values are multi-value and within each multi-value there is a comma as part of the data, exporting to CSV doesn't work.

Re: How to export to CSV when fields have commas

PickleRick — Wed, 31 Jul 2024 05:47:23 GMT

Yes. I know that & is a HTML entity. Hence the smiley.

Anyway. If exporting from WebUI works OK and the REST-initiated export does not there are two things you can do:

1) As I mentioned - raise a case with support. It seems like a bug. A proper CSV should be properly quoted/escaped/whatever.

2) You can use developer tools to check which REST endpoint the WebUI uses

Re: How to export to CSV when fields have commas

cherrypick — Wed, 31 Jul 2024 05:52:08 GMT

Oh, apologies hahaha 😅

Yeah, I tried using dev tools to see which REST endpoint WebUI uses. Not for exporting to CSV but for exporting to pdf (uses pdfgen endpoint). But can't find Splunk documentation on it anywhere and other forums don't seem to have a working solution.

Anyways thanks for recommendation. Will raise a case with support for now.