DCE date/time export for CSV Reports

[DCIM_Support]

Hi All,

This is a little tricky so bear with me...

In DCE you can export the results of a report into a CSV file. (all good)

The 'time' field in the resultant CSV file (called 'Sensor Table Data.csv') is in "dd/mm/yyyy hh:mm:ss [am|pm]" which is all great and is what is shown on the report screen in DCE .  You can import that into Excel and the date/time is recognised and all is right wit the world.

OK.  Now we want to schedule that report to come via email, so from 'Configure Report' we select Name and Schedule Data Export and fill in the fields, choosing a delimiter of "Comma' and Locale of "Use Server Locale".  These are the defaults and remain unchanged.

When the report arrives, the attached file is in zip (still good) and we extract the file.

Now comes the problem.

The date(time) in the exported/scheduled report is in "Mmm dd, yyyy hh:mm:ss" (ie "Jan 2, 2015 03:00:00") and is in 24 hour format.

This date format is not recognised by excel and is the root of the problem.

So the question becomes, why does the date/time format change between an manually exported report/file and a scheduled report/file?

Thanks for your consideration.

Craig

(CID:94601353)

[DCIM_Support]

Hi Craig,

I checked with engineering, and received the following answer:

There is a known inconsistency between the manual export and the scheduled export. A manual export routes the data through the client; a scheduled export comes directly from the server.

So, there are two slightly different implementations for the date formatting, one on the client side (manual export), and one for the server side (scheduled export).

We have successfully imported a scheduled export into Excel 2007. The scheduled export was sent with the default server locale and the 24 hour time format.

Be sure the date column is wide enough in Excel or it will show all hashes. '#######'

I hope this helps.

Best,

Jackie

(CID:94601405)

[DCIM_Support]

Thanks Jackie, 

I have successfully imported the scheduled report into Excel 2013 but it does not recognise the date format.  Do you know if there is something I can do in excel 2013 to make it recognise the date format output from the scheduled reports?

Thanks again

Craig

(CID:94601445)

[DCIM_Support]

Althought it's a manual process and not automatic, you can always insert a column into the Excel spreadsheet and then have a formula parse the date string into a date that Excel will understand. Tom

(CID:94601479)

[DCIM_Support]

Hi Craig, I configured a scheduled export in the Name and Schedule Export dialog in DCE 7.2.6, and chose comma delimited. I did not change the default column configuration. The exported report has a .txt extension; I changed the file extension to .csv and opened it in both Excel 2007 and Excel 2013. I had to expand the width of the Time column to remove the hashes; the date then appeared as expected. Maybe you can ask a colleague to open your exported report to see if the issue is specific to your installation of Excel. I know just enough about Excel to get by. If you find the issue is specific to you, is there an Excel wizard in your group that could help? Otherwise, I'd turn to Google. Let me know how it goes, if you would. Best, Jackie

(CID:94601481)

[DCIM_Support]

Thanks All, 

Yes I get that there is, an unfortunate difference, in the way the download/saving of a report and the scheduled report.  And yes, the date does show in the resultant report.

The issue is that as Excel does not recognise it as a date and it is displayed as 'text' and therefore you cant sort or search using any of the 'date' related functions in excel.

So while I am happy to insert a new column and convert/parse the text field containing the date string to a date field unfortunately, in our office, I am the excel guru (very sad I know) and I cant come up with a excel formula to parse the various dates correctly.  Mainly due to the first 10 days of the month.

So if any other excel gurus out there can come up with a formula that will 'parse' this type of date field "Jan 2, 2015 03:00:00" and "Jan 22, 2015 03:00:00" that would be great.

better still fix the product to work correctly with the word leading tool for parsing CSV files, Excel.  Just a thought 😀

Thanks

Craig

(CID:94601487)

[DCIM_Support]

Hi Craig,

I have tried importing an scheduled export.

I have got excel 2011 for Mac however I would imagine the same functionality would be available in older/later versions of excel as well.

I used the "Get external data" -> "from text file" menu option. Basically it is the import function.

When going through the import wizard i chose comma as delimiter and then the trick as I see it it when importing to tell that the time column is of date format MDY.

At least doing this seems to work for me when using the filtering option and I would imagine therefore also all the date functions available.

(CID:94602675)

[DCIM_Support]

This question is closed for comments. You're welcome to start a new topic if you have further comments on this issue.