Converting Dates in Qualys CSV Reports to Match Your Local Date Format

Document created by Eric Perraudeau Employee on May 28, 2013Last modified by Qualys Documentation on Feb 4, 2016
Version 19Show Document
  • View in full screen mode

Qualys Vulnerability Reports (CSV format) use the US date / time format, "MM/DD/YYYY hh:mm:ss". This document describes how to convert to other date / time formats used in non-US regions, for instance "DD/MM/YYYY hh:mm:ss".

 

If you only have 1 minute:

There is no one-step process in Excel to convert date formats that include a timestamp. Instead, use LibreOffice to open the CSV file and follow these steps:

 

  1. Download a Qualys VM report in CSV
  2. Open the CSV with LibreOffice
  3. LibreOffice launches the Text Import wizard to let the user define the CSV import settings

    Screen Shot 2013-05-28 at 6.06.43 PM.png
  4. Leave all the default settings unchanged except for:
    Import --> From Row = 8
    "Fields Settings" --> Select both columns "First Detected" and "Last Detected" and change the column type to "Date (MDY)"

    Screen Shot 2013-05-28 at 7.04.27 PM.png

  5. The CSV report will then be opened properly and the dates in the US format will be interpreted with the local regional settings of the user.

    Screen Shot 2013-05-28 at 7.09.32 PM.png

 

 

Full details:

Opening a CSV report should be as easy as clicking on the file and letting the CSV application (e.g. Microsoft Excel) display the spreadsheet to the user. Unfortunately, for users that are not using the MDY (Month-Day-Year) date / time format used in the US, this can be a tricky task.

 

There is an easy solution: change the "Region and Language settings" of your system to "English (United States)" and this will do the trick. But it is not practical to change the format every time you want to read CSV files. So, we need to look at a better way to open these CSV reports containing US date / time formats while using systems configured for other regions.

 

In the following examples, we will use Windows 7 and Excel 2010 configured with the "English (United Kingdom)" "Region and Language" settings ("DD/MM/YYYY" format):

 

 

Screen Shot 2013-05-28 at 1.48.20 PM.png

 

Screen Shot 2013-05-28 at 1.56.27 PM.png

 

 

When opening CSV files with dates, you would expect that you can change the date format to match the expected format, but it is actually too late if you did not import the file properly. Excel interprets every character string that looks like a date, and if the second block number in the date character string is lower than 12, then Excel assumes that this number is equivalent to the day or month according the the regional settings of the systems. Therefore, if you open a CSV file that has a field with the US date "04/05/2013" (5th of April 2013) for instance (the second block here is 05), on a UK system (expecting date formated with DD/MM/YYYY), this field will be interpreted as 4th of May 2013. Changing the format of the cell will just change the display since the date has already been interpreted.

 

Example of a CSV file:

"expected date","US format"
"fifth of april 2013","04/05/2013"
"sixth of april 2013","04/06/2013"

 

When you open the CSV file in Excel, it looks ok:

 

Screen Shot 2013-05-28 at 3.44.50 PM.png

 

 

But if you change the format to display the date with the month spelled out, you can see that the day and month have been inverted:

 

Screen Shot 2013-05-28 at 3.48.11 PM.png

 

You can try all the formatting options, including custom format, but it will always be wrong.

 

The Excel Solution

In order to fix this issue, you can use the "text to columns" feature to tell Excel how to interpret the date format. Just follow these steps:

 

  1. Open the CSV file in Excel
  2. Select the "Qualys Format" column
  3. Go to "Data --> Text to Columns"
  4. Use the option "Delimited" and click "Next"
  5. Use "Tab" as the delimiter and click "Next" (we actually are not going to create a new column)
  6. In the last window, select "Date" for the Column data format, and choose MDY

    Screen Shot 2013-05-28 at 3.57.47 PM.png

  7. Click "Finish" and the date will be properly interpreted with Excel:

    Screen Shot 2013-05-28 at 3.59.34 PM.png

    This screencast shows how the import function in Excel performs the same workflow as the file is being opened.

  8. But wait! Qualys CSV Vulnerability Reports contain date / time information, not just the date: "MM/DD/YYYY hh:mm:ss", and Excel will not automatically interpret the date and time using the MDY text-to-column wizard. So, the trick is to insert an empty column after the column containing the date/time and perform the text-to-column transformation using "space" as the separator. This keeps the date in the original column and puts the time in the new column.

 

But this is a lot of manipulations, so instead I recommend using LibreOffice, which does everything automatically as shown above.

Attachments

    Outcomes