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

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. 

 

Environment

Workstation with a non-US English region locale applied. 

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