Getting Data In

How to add BOM to alert attached CSV file?

kwchang_splunk
Splunk Employee
Splunk Employee

Hi experts,

One of my customer wants to add UTF-8 BOM info to CSV files which are attached to email alerts.
In case of CSV files which can be downloaded from Splunk Web UI, https://answers.splunk.com/answers/128517/how-to-export-csv-with-bom.html is the right answer. But this doesn't applied to csv files which are attached to email.

Thank you in advance.

0 Karma

scelikok
SplunkTrust
SplunkTrust

Hi,

For anybody interested, I managed to add UTF8_BOM info into CSV files on email.  

Adding to lines into $SPLUNK_HOME/etc/apps/search/bin/sendemail.py  --> generateCSVResults function.

def generateCSVResults(results, escapeCSVNewline):
    if len(results) == 0:
        return ''

    header = []
    s = BytesIO()

    # BOM_UTF8 info fix for Excel compatibility
    import codecs
    s.write(codecs.BOM_UTF8)
    # BOM_UTF8 info fix end

    if sys.version_info >= (3, 0):
        t = TextIOWrapper(s, write_through = True, encoding='utf-8')
        w = csv.writer(t)
    else:
        w = csv.writer(s)

    if "_time" in results[0] : header.append("_time")
    if "_raw"  in results[0] : header.append("_raw")

    # for backwards compatibility remove all internal fields except _raw and _time
    for k in results[0].keys():
       if k.startswith("_") :
          continue
       header.append(k)

    w.writerow(header)
    # output each result's values
    for result in results:
        row = []
        for col in header:
            val = result.get(col,"")
            if isinstance(val, list):
                val = ' '.join(map(str,val))
            if (escapeCSVNewline):
                row.append(esc(val))
            else:
                row.append(val)

        w.writerow(row)
    return s.getvalue()

 

If this reply helps you an upvote is appreciated.
0 Karma

keio_splunk
Splunk Employee
Splunk Employee

CSV files attached to email are UTF-8 encoded in Splunk and there is no option to change the encoding.
UTF-8 CSV files work well with spreadsheet editors such as Google sheet and Apple Numbers. Microsoft Excel added support for UTF-8 CSV files from Excel version 2019 onward; refer to What's new in Excel 2019 for Windows | General improvements | CSV (UTF-8) support.

Here is the workaround for the issue for Microsoft Excel (without CSV (UTF-8) support):
1. Install Sublime Text.
2. Open the CSV file using Sublime Text.
3. Save the content using File > Save with Encoding > "UTF - 8 with BOM" to a new CSV file.
4. Open the new CSV file using Excel.

0 Karma

sloshburch
Ultra Champion

I'm having a ton of trouble understanding the question. You might get more answers if you could clarify it more. Break it down like I'm a little kid (not far from lol).

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!