March 27, 2020
Need some help in converting the date/time field of a CSV into date. In CSV it is a text field.
Also the format of the date/time field is: mm-dd-yyyy hh:mm:ss a/p
When I convert the field to date where ever the dd is less than or equal to 12 it works fine but for others it gives error.
Attaching a sample file for reference. I am actually using power query to import the data.
*Not sure how to insert a file so pasting the sample data
"number","opened_at","short_description"
"INSFDSFDSFDS","01-10-2019 03:16:10 PM","TEST directory results - network names and logos"
"INSDFDSF","06-24-2019 10:13:18 AM","Issues with enet Administrators)"
"INCSDFDSFDS","07-15-2019 11:36:05 AM","Data Load"
"INDFSGERREE","08-09-2019 04:22:40 PM","Please load file for Data"
VIP
Trusted Members
December 7, 2016
Hello Sunil,
I would use Power Query to import the csv file and to convert the date/time column to date. Have you tried using PQ?
Check out this blog article, it might give you a good help.
/Anders
VIP
Trusted Members
December 7, 2016
March 27, 2020
Hi Anders thanks for helping me out. Attached is the link of the video with the process and the steps that I am trying to implement it.
VIP
Trusted Members
December 7, 2016
VIP
Trusted Members
December 7, 2016
Hello Sunil,
I made a simple screen recording too, so you can see the difference. Your row 1 is the column headers, for me I get the column headers correct during import so my row 1 is the first data row. You need to either remove the first line or make it as headers.
As you might also notice is that for me I don't get the AM/PM displayed during import, it is because of different regional settings used in Sweden. The time is still showing correct.
If you just fix the first row you should be good to go.
Br,
Anders
March 27, 2020
Not sure why but it is very strange and it is driving me nuts. Such a simple thing and I am unable to implement it.
Few of the observations:
1. For you it automatically takes the first row as the header while importing but for me it does not.
2. Also for you it automatically takes the 2nd field as date/time but for me it does not.
Not sure what the issue is.
Andres thanks for investing time in me to sort the things out.
Thanks
Sunil
VIP
Trusted Members
December 7, 2016
Hello Sunil,
I have played around with the regional settings and when switching over to English (United Kingdom) settings I get the same issue as you have. This is not the case when using my default Swedish settings. When setting local to English (United States) it all works fine again.
As it seems my .mp4 file can't be uploaded I try to explain in text where to check.
In Excel, go to Data tab and choose Get Data and then Query Options.
Check and try with different localizations in the Regional Settings.
With Swedish and United States locale the date/time type is set correctly, but with English locale it is identified as text. The first and fourth rows are identified, but the months and days are wrong. The second and third rows are not identified correctly as it reads day 24 as month 24, etc.
With Swedish and United States locale this is not an issue. It seems you need to play around to get it to work.
Br,
Anders
Answers Post
1 Guest(s)