Noob taking baby steps here, I am having a strange problem importing CSV file - saved from the bank checking account register. The file is clean.
My system prefs are set for dates to be rendered 27 FEB 2019 (short) or 27 February 2019 (medium and long). This is how I would eventually like to have them set in my database. The bank output is in the form 2/27/2019.
I used the tutorial to successfully make a checkbook ledger with the dates the way I like them. The field is formatted as a date with the output pattern dd/mm/yyyy. I made a copy of this file and, for a new account, imported the csv, replacing the contents of the database. When I do this, PanoramaX misconstrues the month and day of the month, resulting in empty fields when ever the day exceeds 12.
This happens even when I create a new database from the CSV as follows: the file is imported flawlessly, the Date field contains the right info, however it is text. If I use the Properties window to change it to Dates, the misconstrued dates disappear. I don’t see a way to morph it from a Text type to a date datatype. Can anyone suggest the way to handle this?
As far as Panorama is concerned, the easy way to handle it would be to change your system preferences to agree with your bank. When Panorama converts text to a date, it expects that date to be formatted consistently with your system preferences or, alternatively, in a YYYY-MM-DD format. The output pattern is strictly for output, and has no affect on the way that text input is interpreted. You set the output pattern to the way you want the date to be displayed.
If you need to keep the preference setting the way it is, to get some other program to work the way you want it to, it is possible to reformat text as it is being imported. It would use the “rearrange” option in an importtext statement. That’s pretty advanced stuff for a noob.
I would like to add some advice how to change the imported text, if you do not want to change the system date format.
A simple way could be to add another field and then to use a formula in that field (menu Fields > Morph … > Fill with Formula…) with text funnels to rearrange the day and month numbers from mm/dd/yyyy to dd/mm/yyyy.
As Dave mentioned, you can use basically the same formula within the import text “rearrange” option to do the conversion directly during the import. In this case you do not need an additional field; you rearrange the import data on the fly and can import the rearranged data into your preformatted date field.
Thank you both. I think I will try the funneling and if I can’t scale that mountain I will fall back to changing my system preference. Much appreciated.
Actually what I would do is import it (with the date arriving text) and then apply a morph to change the date string to something that can be transformed into a date.
I understood that the original poster wanted to import the text into an existing database, not creating a new one from scratch.
Sometimes, however, even in that situation I will use the technique Matt described – create a new database by importing, them morph, then transfer over to the original database. That’s especially helpful for a one-off import that requires complicated transformation. But if you are going to be doing the same import over and over again into an existing database, it can be worth it to use the techniques described earlier in this thread to automate the process, reformatting the data as the import is done.
See the “Import Arrangement Formula” section on this page to learn how to set up a formula in the Text Import wizard, or use the “rearrange” option in the importtext statement as mentioned by Dave.
Unfortunately, neither of these documentation pages describes how to write the formula for rearranging the date, only how to set up a formula for import in general. Assuming your date is in the first column of text, this formula should work for this field in the Text Import wizard: