Hi All,
We use an online CMS that outputs dates in “01-May-2019” format when I export a csv. I’m in the UK and my default format is dd-mm-yyyy. How can I import this CSV and convert the date format from the above to dd-mm-yyyy so that PanX reads it as a date? At the moment it is coming up as a text field. If I convert it to a date field all the data is lost.
I cannot duplicate your results. Panorama already is built to understand the format “01-May-2019”, it automatically detects that the middle part of the date is a month name and adjusts accordingly. It doesn’t matter what your date pattern is or what region you have your date setting configured for in System Preferences. I just did some testing to verify that this works, and it worked perfectly for me. I could convert a text field to date, enter dates in this format with the keyboard, and import data into a date field in this format. In all cases it automatically recognized the format and correctly brought in the date. In fact, it even worked if I quoted the date as you did in your post (I assume you put in quotes just as punctuation, but thought I’d try it just in case – and it worked).
So … there must be some additional detail you are not sharing with us that is tripping Panorama up.
Perhaps there is a problem with my test process.
Is that expected behaviour? Am I doing something wrong?
Simon
I can confirm this. With language and region preferences set for United States, it is correctly converted to a date, but if I set the preferences to United Kingdom, the date comes up empty. This happens both when I convert a text field to date, and when I import into an existing date field.
I didn’t test this, because I looked at the source code and it wasn’t checking the region preferences. Apparently something extra is going on that will take some significant sleuthing. So I have added this as an open bug.
I am a first time user of Panorama and I have found the same issue as svsmailus. I am in Australia (system prefs set Region to Australia) and when I download my bank statements the date format is dd-Mmm-yy and dates get imported into Panorama as text fields. When I tried to use morph to change the text fields to date fields the data disappears. I’m working on a procedure to make the transformation to US style dates. Is there a slick way to write a procedure for the transformation? Is there an ETA for the fix?
As a fellow-Australian, I often encounter the same problem, especially when importing dates from an Excel sheet. There are many ways to skin this particular cat but here’s a line of code I used recently:
field ScanDateTime formulafill array(ScanDateTime,2,"/") +"/"+ array(ScanDateTime,1,"/") +"/"+ array(ScanDateTime,3,"/")
… where ScanDateTime is the US-style date in the form mm/dd/yyyy, so 11/27/2019 becomes 27/11/2019.
I’d like to suggest to do all your experimenting and testing of formulas not on the original imported field. Create another field and test your formulas there, and set the field type to date when your formulas are working.
For the conversion you can use text funnels in combination with Panorama’s datevalue( function. So if your imported data contain “27-11-2019” in a text field “importedDate”, a formula like this would convert it right into a date field:
field "aDateField"
formulafill datevalue(val(importedDate[-4,-1]),val(importedDate[4;2]),val(importedDate [1;2]))Thanks to Michael and KJM for your responses. I’ll give it a try.
I think I’m going to like Panorama. My first post and I get excellent, prompt responses!
You will always get that.
If your text date field is formatted as you noted (dd-Mon-yy) I have a procedure that will convert that text field into a date field with the output pattern set to a US format (mm-dd-yyyy).
So that the current text field…
11-Sep-19
10-Sep-19
9-Sep-19
8-Sep-19
7-Sep-19
6-Sep-19
5-Sep-19
4-Sep-19
3-Sep-19
2-Sep-19
1-Sep-19
31-Aug-19
30-Aug-19
29-Aug-19
28-Aug-19
27-Aug-19
becomes a date field with this output…
9-11-2019
9-10-2019
9-9-2019
9-8-2019
9-7-2019
9-6-2019
9-5-2019
9-4-2019
9-3-2019
9-2-2019
9-1-2019
8-31-2019
8-30-2019
8-29-2019
8-28-2019
8-27-2019
Here is a procedure that simply adds a new field temporarily named tempDate and set to “date” type with the output pattern also set to US (or any other format you wish). This field is then filled with the date value taken from the existing text Date field (I’m assuming this field is currently named Date). We then delete the original Date field containing the text dates and change the new tempDate field’s name to Date. The noshow and endnoshow statements avoid screen redraws and vastly speed things up. The showother statement allows the newly created date type field to be properly displayed in the datasheet.
noshow
field Date
insertfield "tempDate"
fieldtype "date"
setfieldproperties initializedictionary("OUTPUTPATTERN","mm-dd-yyyy")
formulafill date(Date)
field Date
deletefield
field tempDate
fieldname "Date"
showother «»,99
endnoshow
Test this on noncritical data since my personal testing is rather limited.
I see this bug is fixed, but I am still having issues with this. I use various online forms that return data to me. The latest format is DD Mon yyyy. When I import this into my database the date is missing. I have to manually add it as dd/mm/yyyy. I’m using the UK locale.
Is there a solution?
There are several possible solutions:
Thank you for that. Will take a look!