Log In
Register
Home
March 27, 2020
Offline
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"
See Answer
October 5, 2010
Offline
Hi Sunil,
To attach a file click on the Attachments button below where you type you qs. Then Add Files-> Start Upload.
Phil
March 27, 2020
Offline
Thanks for directing me. Attached is the sample file.
Thanks
Sunil
VIP
Trusted Members
December 7, 2016
Offline
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
March 27, 2020
Offline
I have tried using PQ also but it does not work. The PQ first defines it as a Text and when I convert it to Date/Time the 1st, 3rd and 4th record errors out.
Thanks
Sunil
VIP
Trusted Members
December 7, 2016
Offline
Hello Sunil,
As I don't know how you converted the date/time column, but as shown in attached sample file where I have used your .csv file, I don't see any errors in the date after the conversion.
Br,
Anders
March 27, 2020
Offline
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
Offline
Hello Sunil,
The errors you get in the video is because the first row is text. If you make the first row as headers and after that convert the date/time column to date you will be successful.
Br,
Anders
March 27, 2020
Offline
Hi Andres I tried but still the same issue 🙁
VIP
Trusted Members
December 7, 2016
Offline
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
Offline
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
Offline
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)
