topic Re: In a CSV file, how do you sort date and time separately? in Getting Data In

In a CSV file, how do you sort date and time separately?

mdmaala — Wed, 06 Mar 2019 00:23:54 GMT

hi!

I have a CSV file that indicates Date and Time in one column and is displayed like this 2019/03/05 17:05:04 I want to sort the date and time separately.

is there any way I can do this? thanks!

Re: In a CSV file, how do you sort date and time separately?

niketn — Wed, 06 Mar 2019 01:52:37 GMT

@mdmaala, try the following regular expression based extraction (with run anywhere example based on your data). If the field name is DateTime the regex extract two fields Date and Time separately.

| makeresults
| eval DateTime="2019/03/05 17:05:04"
| rex field=DateTime "^(?<Date>[^\s]+)\s(?<Time>.*)$"

Re: In a CSV file, how do you sort date and time separately?

vnravikumar — Wed, 06 Mar 2019 02:07:23 GMT

Hi @mdmaala

You can try this rex also, it will result in two new columns Date and Time

| makeresults 
| eval date_time="2019/03/05 17:05:04" 
| rex field=date_time "(?P<Date>\d{4}\/\d{2}\/\d{2})\s(?P<Time>\d{2}\:\d{2}\:\d{2})"

Re: In a CSV file, how do you sort date and time separately?

mdmaala — Wed, 06 Mar 2019 03:02:33 GMT

Thank you so much! it works perfectly. but how about if I want to separate the date and time for all the date and time included in the table? is that possible?

Re: In a CSV file, how do you sort date and time separately?

vnravikumar — Wed, 06 Mar 2019 03:07:12 GMT

Hi @mdmaala, specify your datetime field in the rex statement

your query...| rex field=date_time "(?P<Date>\d{4}\/\d{2}\/\d{2})\s(?P<Time>\d{2}\:\d{2}\:\d{2})"

here date_time is the field name that you want to split, It will give two fields Date and Time.
Please let me know if you need more info.

Re: In a CSV file, how do you sort date and time separately?

mdmaala — Tue, 29 Sep 2020 23:34:35 GMT

I see now I understand. In my table the field name is Date_Time so what I did was this:

source="C:\Users\NPIE-MDMAALA\Documents\PATLITE\*" host="DESKTOP-G546H69" sourcetype="csv" | rename "Green information" AS "Green" | rename "Amber information" AS "Amber" | rename "Red information" AS "Red" | rename "User name" AS Username | rename "MAC Address" AS "MAC_add" | rex field=Date_Time "(?P\d{4}\/\d{2}\/\d{2})\s(?P\d{2}:\d{2}:\d{2})" |

is this correct?

Re: In a CSV file, how do you sort date and time separately?

mdmaala — Wed, 06 Mar 2019 03:33:36 GMT

Oh! Never mind! I already figured out what to do based on your last comment. and It really worked well! thank you so much @vnravikumar !!