Getting Data In
Highlighted

Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

When we export Social Security Numbers to a csv file, the leading zeros of the SSN are being trimmed. We wonder how we can preserve these leading zeros.

Any ideas?

Tags (3)
0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Legend

Its probably because csv converts it to numbers. Try changing the format in splunk to string. Something like this
.... rex mode=sed field=ssn "s/(\d{3})(\d{2})(\d{4})/\1-\2-\3/g"

View solution in original post

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

Interesting -

I ran -

basesearch | eval rex mode=sed field=ces_ssn "s/(\d{5})(\d{2})(\d{4})/\1-\2-\3/g"

It says -

Error in 'eval' command: The expression is malformed. 
0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Legend

There should not be an eval before the rex

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

Gorgeous - thank you!!!

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Legend

@ddrillic Assuming this worked for you, I converted it to an answer so you can accept it.

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

Great. I really wondered about it... so, we can convert a comment to an answer, right?

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

I wonder also whether we can add quotes and "force" the number to be a string in the csv file. Any ideas?

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Legend

You could try a | eval ssn=tostring(ssn) see if CSV sees that as string. I might. Then you dont have to deal with formatting.

0 Karma
Highlighted

Re: Exporting SSNs to a CSV file trims leading zeros. How do we preserve them?

Ultra Champion

Interesting - the | eval ssn=tostring(ssn) doesn't have any effect on the data type. The UI still shows the ssn as numerical values.

When doing | eval ssn="\"" + tostring(ssn) + "\"", I get quotes around the ssn in the csv file.

0 Karma