CSV multipul time events in header

[kphillipson]

I have a CSV file where the header contains the time of each subset of data. I need Splunk to split the columns into different event times, to be referenced as _time.

user_ID	6/24/2019	6/17/2019	6/10/2019
3	40.34	40.5	44.53
4	36.99	38.64	42.86
5	0	0	0

For instance user_ID 3 has logged in for 40.34 hours for week 6/24/2019,  40.5 hours for week 6/17/2019 etc...

The only thing that comes to mind is creating separate csv files for each week, but I believe there is a better way.  I have search but nothing has lined up with what I'm running into. The closest was this one but didn't help. https://community.splunk.com/t5/All-Apps-and-Add-ons/How-can-I-use-the-time-column-name-of-CSV-as-th...

Thank you for your time helping me.

[to4kawa · Accepted solution]

|inputlookup yourcsv |untable user_id week hours | eval _time=strptime(week,"%m/%d/%Y") |table _time user_id hours | collect index=yours

[kphillipson]

Thank you for your experience.  A kind friend was able to generate a python script to reorder the csv for me. Loading the csv as a lookup and having Splunk generate the desired output works too!  Hope this helps someone with the same issue having time referenced in the row.

[Nisha18789]

Hi @kphillipson , is it possible to update the csv to contain data like below?

week	user_id	hours
6/24/2019	3	40.34
6/24/2019	4	36.99
6/17/2019	3	40.5
6/17/2019	4	38.64

[kphillipson]

Hello@Nisha18789 ,

Unfortunately I can't export it that way. That would be a lot of entries to hand jam but I see where you are going with this.  I think I'll have to try my hand at a python script to change it.  I can easily flip the column A with row 1 using paste special > transpose.  From there maybe python can group the users to the hours, as you have in your example.