Forum Discussion
Need Help with Importing CSV with Strange Date Format
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
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:
17 Replies
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:
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.
Why go through the hassle with Excel when you can process the CSV directly in Power Query?
This is what i needed, thanks for the great documentation
Can you post a sample CSV? Remove sensitive information.
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,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.200As you can see in the csv, that is all that is exported.
