Forum Discussion
Need Help with Importing CSV with Strange Date Format
I know nothing of Intune, however…
According to Intune Support team here: https://techcommunity.microsoft.com/blog/intunecustomersuccess/support-tip-changes-in-all-devices-list-and-reporting-in-intune/1220946
they say:
- We’ve received reports of the last check-in datetime not being understandable in Excel. The .csv export has a space between the date and time instead of a comma and is now shown in UTC format. The .csv format for the Last Check-in is “YYYY-MM-DD HH:MM:SS.sssssss”, for example "2020-03-08 17:10:10.4438071". In Excel, you can change the column cell format to display Date and time in the format you desire.
So, when I pasted:
2020-03-08 17:10:10.4438071
into a cell in Excel, it showed like this:
where the formula bar shows a more complete version than the cell does. Excel has also 'helpfully' changed the format of the cell to:
if I change it to:
I get this:
where there is more information visible in the cell than in the formula bar!
My first question is are you getting the data into Excel before getting it into Power Query/Power BI?
If so, don't. Get the data directly from the original Intune csv file into Power Query/BI. Power Query correctly auto detects that field's type:
I was unable to get the date to convert via power query. It wouldn't recognize the format and spit out errors. The initial post was created for this reason.
I can help you with that but only if you can provide samples of the raw CSV.
Thank you! Here is an example doc with this odd date format:
Device ID,Device name,Ownership,Compliance,OS version,Primary user UPN,Last check-in,Enrollment date,Model,Manufacturer,Serial number,Total storage,Free storage,Management name,SkuFamily
DeviceID-fjjfjfjfjfa,DeviceA,Corporate,Compliant,10.0.19045.3803,admin,16:36.9,39:01.6,Model1,Awesomecorp,DEVICESN1,242580,126050,Site1,Education
DeviceID-fjjfjfjfjfb,DeviceB,Corporate,Compliant,10.0.19045.3803,admin,24:52.4,18:46.7,Model2,Awesomecorp,DEVICESN2,243580,110316,Site2,Education
DeviceID-fjjfjfjfjfc,DeviceC,Corporate,Compliant,10.0.19045.3570,admin,33:56.0,17:05.0,Model3,Awesomecorp,DEVICESN3,243571,170606,Site3,Education