Import csv and change date format at the same time


#1

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.


#2

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.


#3

Perhaps there is a problem with my test process.

  1. I export my csv from my online cms
  2. I create a new database from csv file
  3. The data imports and the date column is set by default to a text column. Date entries are 02-May-2019.
  4. I select the column and change it to a date column. All date entries disappear.

Is that expected behaviour? Am I doing something wrong?

Simon


#4

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.


#5

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.