Complete

Endpoint list export to .csv

  • 4 July 2014
  • 8 replies
  • 1841 views

Userlevel 3
Just noticed something that would be really useful, don't know if it would be possible. As part of a migration I've been checking when endpoints have been checking in, and only migrating those that have been seen recently.
I need to run a report for a client, so exported a list to csv. Ideally I would like to sort this by data last seen. The date format is, for example, Jan 20 2014 13:29. Is there any way, when exporting to csv, these dates can be converted e.g 20/01/2014 13:29?
 
I understand there may be formatting issues with UK vs US date formats, but this would actually be quite useful.

8 replies

Userlevel 7
Hello AmandaJayne, welcome to the Webroot Community!
 
If you load the .csv into Excel, you can then select the column with the dates.  Right click and select Format Cells.  In there you can select the date display format to have Excel convert the date format, then simply re-export the .csv file with the date format changes.
 
I hope that this helps you!
 
 
 
 
Userlevel 3
 
I've tried this already, but the format doesn't change. I've tried on several sheets, but not been successful
Userlevel 7
I am not sure why.... I did it in Excel here before replying above and it worked perfectly.  Make sure you have the fields set for Date data type not numbers, text, etc.
 
 
David: It's also not working for me. Excel doesn't detect the datetime values. In my case the datetime is formated in german layout and I've read that Excel only can handle US datetime formats. Could this be true?
 
Amanda: When exporting the clients to CSV you can choose to sort them by last seen. Would this work for you?
Userlevel 3
Hi regnor,
 
I did try this, but still wasn't a really suitable format. I have trawlled the internet trying to find a solution, but gave up in the end. I worked around it (bodged) by replacing the month with its correspoding number, splitting the column, then concatenating them in the order I wanted. A royal pain, but it allowed me to do what I needed for now.
Userlevel 7
Badge +56
Glad to hear that you got a workaround to do the trick.  Hopefully we can get a better process for you in the future.
Userlevel 7
Badge +35
Thank you for your suggestion, this has been closed due to lack of kudos from other community members and an alternate workaround provided.
Badge
I wrote a vba function to easily convert the date format to something Excel can understand as a date:

'==============================================================================
Function WebrootDateConv(cellRef As Range)

Dim TextStrng As String
Dim Result() As String
Dim DisplayText As String
Result = Split(cellRef)
Result(0) = Month(DateValue(Result(0) & " 01" & " 2012"))
For i = LBound(Result()) To UBound(Result())

DisplayText = DisplayText & Trim(Result(i)) & " "
Next i
WebrootDateConv = CDate(Mid(DisplayText, 1, Len(DisplayText) - 1))

End Function
'===============================================================================

Save the lines between the '== as a text file, open excel developer mode and click Macros. Then import this file you saved as a module.
Then return to your worksheet and insert a new column after the poorly formatted Webroot column.
Label your new column something useful (eg. 'LastSeenFormatted')
In the first cell under the label, start typing '=Web'. Excel should autopopulate the formula to '=WebrootDateConv('. After the Parenthesis, just select the first cell of bad webroot formatted dates. Then hit enter. click the cell you just formatted and double-click the small square in the bottom right of the bold box to populate this formula for all records.
Lastly, format the new column to Date Time or some other useful date format.

*note the line Result(0) may need to be updated if you do not use US date formats.

Reply