Date formats are not correctly recognised when importing a .csv file originating from Enforce console reports

Article Id: 159506
Status: Published
Updated On: 11-04-2019 07:17
Legacy Id: TECH218901
Products:
Data Loss Prevention Enforce
Issue/Introduction:

You have exported a report from DLP into .csv format

When the report (.csv file) is opened in Excel, the dates are not recognized as a date/time type and cannot be correctly sorted. 

 

Cause:

The DLP Enforce server uses the US-English short date format when exporting data to CSV. Due to other internal dependencies, this is not due to be changed. 

Resolution:

Workaround 1

Change the locale in the Region control panel on the Windows workstation machine to English (United States) before importing the CSV file: 

Workaround 2

In newer versions of Excel you can designate the region of the imported source CSV file: 

  1. Open a new Excel workbook
  2. Click the Data tab and click 'From Text/CSV' button
  3. Import your CSV report
  4. Click Edit on the data preview window to open the Power Query Editor
  5. Right click on the column with your date/time data
  6. Select Change Type | Using Locale...
  7. Select Data Type of Date/Time and Locale: English (United States)
  8. Click OK, and click close on the power query editor window - choose 'Keep' when prompted
  9. The data will now be imported with a recognized date/time data-type but the format will be US standard: mm/dd/yyyy hh:mm AM/PM
  10. Change this to your chosen standard format eg. European by right-clicking the column with your date/time data
  11. Select Format | Custom and choose dd/mm/yyyy hh:mm