topic Re: How to convert excel with multiple tabs to multiple csvs and then upload to splunk with a script? in Getting Data In

How to convert excel with multiple tabs to multiple csvs and then upload to splunk with a script?

Chandras11 — Sun, 13 May 2018 09:27:24 GMT

Hi Everyone,

I get a report ( 3tabs, 64 columns and 10k+ rows with all kind of data) every day multiple times. I need to upload the data as csv (3 csvs for each tab and a predefined source type for each csv) to Splunk in the same index.

Currently, I wrote an Excel-VBA script with replacing a few keywords and then creating the CSV for each excel tab. However, when this CSV gets generated , Excel appends double quote (") at the beginning and end of each row cell. Therefore, I need to open all CSVs in Notepad++ and remove the double quote (") manually. After that, I upload all the data in Splunk enterprise.
I believe, there are multiple workarounds for this issue.
I am just looking for certain pointers if:
1. Is there any already available app for one-click excel to CSV convert and upload to Splunk (with some customization). I just found https://splunkbase.splunk.com/app/1832/ but I am not sure about the customization
2. Could you please recommend any scripting tool, which can help me with this.

Thanks a lot for your time.

Re: How to convert excel with multiple tabs to multiple csvs and then upload to splunk with a script?

FrankVl — Sun, 13 May 2018 10:05:27 GMT

That app you found seems to be for exporting data as xls, not for importing xls.

Are you sure that trouble to remove quotes is actually necessary? Have you tried ingesting it into splunk without stripping those quotes first (possibly with the addition of setting FIELD_QUOTE=" in props.conf)?

I'd suggest googling for some powershell code to convert xlsx to csv and then turn that into a Splunk scripted input.

Re: How to convert excel with multiple tabs to multiple csvs and then upload to splunk with a script?

Chandras11 — Sun, 13 May 2018 12:06:01 GMT

Let me check with FIELD_QUOTE=" in Splunk. I tried to upload the CSV without removing the Double Quotes and it resulted in a single field. After that, I need to extract all the fields again separately in Splunk. As I wrote that the excel contains all kind of data (including some cells with Double Quotes as well comma and the German characters ä,ö,ü ß etc, I need to correct the data beforehand. In Excel VBA, there is a replace command to do it.

Re: How to convert excel with multiple tabs to multiple csvs and then upload to splunk with a script?

Chandras11 — Tue, 26 Jun 2018 09:13:24 GMT

You are correct. I found some power shell commands to resolve it. The one I found is also quite popular and known as jrepl . It can be used to remove the double quote with the following code:-
for /f "delims=" %%a in ('dir *.csv /b /a-d ') do (
echo processing "%%a"
call jrepl "\x22" "" /x /f "%%a" /O -
)

The above code will remove the double quotes in all CSVs in a folder where jrepl.bat is also saved. It should be saved inside another .bat file.