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 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?
