Need Help with Importing CSV with Strange Date Format

[CeiBob]

I have a CSV export from Intune which I am trying to import and the date format is causing me issues. I do not understand why this date format is not being picked up now, as it was previously.

When I look at the CSV document, the column 'Last Check-in' has a format which shows ##:##.# only, but when I click on it, the date /time shows in the field properly. The issue with this is Power BI does not recognize this date, and I would have to click on every single field to get it to convert from this odd format. If I do not convert it, Power BI doesn't know what to do with ##:##.#. 

I have included an example of the document below:

I do not know why Excel does not see the full format. When I look at the file in notepad, it shows the whole date / time for the cell, but microsoft products have been doing the ##:##.# format for some reason. This is causing me quite a headache also because I have no idea what this is called or why this is happening.

Is there a way to import this date column correctly into Power BI or convert it so Power BI can use it?

Thank you!

-CeiBob

[HaydnMorgan]

p45Cal, thank you for the reply, but that doesn't work as I mentioned in my original post. That just gives an Hours minutes and seconds without date.

Last check-in
00/01/1900 00:18:28.400
00/01/1900 00:38:34.900
00/01/1900 00:35:36.500
00/01/1900 00:25:40.200

As you can see in the csv, that is all that is exported.

[p45cal]

Has that csv ever been opened in Excel?

[lbendlin]

Tell these Intune guys to get their act together and send the data in the proper ISO-8601 format 🙂

Can you post a raw CSV file? (sensitive information removed)

[p45cal · Accepted solution]

I know nothing of Intune, however…

According to Intune Support team here: https://techcommunity.microsoft.com/blog/intunecustomersuccess/support-tip-changes-in-all-devices-li...

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:

[CeiBob]

This is exactly the problem. I've been converting this document in excel manually for months and only getting around to finding a way to convert the date format via powershell as the CSV is created. 

[lbendlin]

Why go through the hassle with Excel when you can process the CSV directly in Power Query?

[CeiBob]

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.

[lbendlin]

I can help you with that but only if you can provide samples of the raw CSV.

[CeiBob]

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

[p45cal]

It looks like you've edited this file; how have you done that?

[CeiBob]

I had to edit the sensitive data out of the CSV. I am unfortunately unable to export a doc without sensitive info from my organization. Perhaps there is another way to go about this?

[p45cal]

How did you do the editing and saving? I might be able to suggest a way which doesn't mangle the csv.

[lbendlin]

Yes, Notepad++ for example.

[p45cal]

---

@lbendlin wrote:

I can help you with that but only if you can provide samples of the raw CSV.

Hear, hear. Make sure you haven't even opened it in Excel! Ever.

I'm 99.9% certain that Power Query can be made to transform it properly.

---

[HaydnMorgan]

I'm not the original Poster, but here is an example I'm having the same issue. 1st few rows but I've taken out the resr of the rows. This is the CSV viewed in Notepad.

Device ID,Device name,Enrollment date,Last check-in,
a13db61b-7418-4792-b2f1-6dbff5fe21a8,xxxxxxxx,00:35.2,18:28.4,
c97fec60-b8b7-4c42-8191-cea6e5aff971,xxxxxxxx,31:51.8,38:34.9,
cf28a310-a95f-4fcf-9b07-59602c2c2169,xxxxxxxx,13:17.3,35:36.5,
32abaebe-731b-49e8-a85c-2a66e134b39d,xxxxxxxx,07:23.8,25:40.2,

[lbendlin]

Can you post a sample CSV?  Remove sensitive information.