Splunk csv Export and Scientific Notation

[OldManEd]

Here is my problem. I have a search that creates a table. The table contains a column called Product_ID, a large numeric value of 15 digits. When I export the table as a csv file in Splunk, and read it into Excel, the column is turned into a scientific notation value. I ~need~ it to be a string. I’ve tried using "| eval field_name = tostring(field_name)", in the search, but that did not work.

I did see that the exported csv file created has all the strings encapsulated in double quotes, but all the numbers are not.

Any ideas on how to get the export csv file to force everything to strings?

[emiller42]

You don't need to do anything special in Splunk for this. Just change the formatting in Excel from 'General' to 'Number'. You may also need to remove decimal places, but that's trivial. (See below) It's worth noting that the change to scientific notation (and all formatting changes) in Excel are all in display only. The underlying data is not modified, and no precision is lost.

If you ~really~ need it to be a string, you can do something like the following:

index=_internal | head 10 | eval foo=1234567890123 | table foo | eval foo2="\""+tostring(foo)+"\"" 

[OldManEd]

emiller,

Thanks. Adding (foo2="\""+tostring(foo)+"\"") to the search worked. I understood your suggestions about setting up Excel to accept and display the data correctly, but my user community wanted to simply 'click' on the email attachment and examine the data in excel without any extra work.

Thank you again.

[OldManEd]

I did exactly that in wordpad and saw the strings were encapsulated in double quotes but numbers were not. (i.e., “string”, 1122334455667788). What I need to do is force the numbers to strings in Splunk. I tried to use the “tostring()” function in Splunk, but that did not encase the numbers in double quotes.

[somesoni2]

I guess Splunk exports values in correct format only (you can verify it by opening the csv file in textpad/notepad). Its is Excel who converts this text which holds a numeric value into number automatically.