Looking at Bento's Date Format
I've been using FileMaker Bento (casually) for a week now. Before trying it, I assumed I would compare it to Microsoft OneNote (only available for Windows). But, as the price comparison between it and OneNote might indicate ($49 vs. $199), they really are quite different products and shouldn't be compared. Instead, I started thinking about it in terms of the flat file database I use the most at the moment: The web-based Zoho Creator.
Right now, I'm struggling with the way Bento works with date formats. It absolutely refuses to import dates from a CSV file that imports fine into products like MySQL. The date data I'm trying to import is in the common DD-MM-YYYY format. Bento, however, will only import dates in the format MM/DD/YYYY. I suppose I could write a Ruby script to pre-process CSV files before importing them into Bento. But, it seems to me like Bento should just deal with this date format correctly.
Labels: software
posted by Todd Ogasawara @ 8:43 AM
4 Comments
4 Comments:
I am the moderator for the Bento forum (forums.filemaker.com). The information the user posted is incorrect. Bento will indeed import date information in either DD-MM-YYYY or MM-DD-YYYY format, depending on how the Date/Time preferences are set in Leopard. Since the original post said that Bento "will only import dates in the format MM/DD/YYYY", then the Date/Time preferences are set for USA. If the user changed the Date/Time format to DD/MM/YYYY, the dates would import correctly.
If you think this should change, please feel free to post a suggestion to the forum along with the proposed change. Remember, we do not want to penalize those users who have dates in CSV files as MM-DD-YYYY.
TSGal
FileMaker, Inc.
tsgal: I have a more detailed blog commentary that I'm writing for the O'Reilly Media Mac Center. Unfortunately, my findings do not agree with your comments. I tried importing dates formatted with both forward slashes as well as hypens. Neither imported correctly. Moreover, date fields do not export either. My detailed blog items will be published at http://digitalmedia.oreilly.com/mac/ on Wednesday morning.
Are you using the Preview version or the Trial/General Release? Here are the exact steps I took.
1. I created a CSV file, US.csv, with two records as follows:
Name,Date
"TSGal",02-05-2008
"Todd",11-17-2007
2. Launch Bento and import US.csv into a new Library, changing "Date" from Text to Date on import.
3. All information is displayed as follows:
Name: TSGal
Date: 2/5/2008
Name: Todd
Date: 11/17/2008
4. Pulled down the Apple menu and selected "System Preferences..."
5. Double-click "Date & Time"
6. Click "Open International..."
7. Clicked the tab "Formats"
8. Under Dates, clicked on "Customize..."
9. Switched the Month with the Day so it now shows 5 / 1 / 08
10. Closed System Preferences...
11. Quit Bento
12. Modified US.csv to switch the dates:
Name,Date
"TSGal",05-02-2008
"Todd",17-11-2007
13. Saved file as UK.csv
14. Launched Bento
15. Notice the existing records have the date format change to as follows:
Name: TSGal
Date: 5/2/2008
Name: Todd
Date: 17/11/2007
16. Imported UK.csv into a new Library. Made sure Date was set up to import "Date" instead of "Text".
17. Resulting records:
Name: TSGal
Date: 5/2/2008
Name: Todd
Date: 17/11/2007
18. Imported UK.csv into existing US Library from step #2 above.
19. Now have four records, all displaying as expected with D/M/YYYY format.
20. Exported to CSV file, UK1.csv.
21. Opened UK1.csv file in text editor:
Name,Date
"TSGal",5/2/2008
"Todd",17/11/2008
"TSGal",5/2/2008
"Todd",17/11/2008
22. Quit Bento
23. Go back into System Preferences and change the date format back to US date format. That is, M/D/YYYY
24. Close System Preferences.
25. Launch Bento
26. Notice all dates are displayed back in US format.
27. Export to CSV file, US1.csv
28. Open US1.csv file
Name,Date
"TSGal",2/5/2008
"Todd",11/17/2007
"TSGal",2/5/2008
"Todd",11/17/2007
------
If you cannot duplicate any of these steps, please let me know where it is failing for you.
I would like to know where your findings do not agree with my comments.
TSGal
tsgal: I bought a boxed copy of Bento. Thanks for the detailed information (though it seems like an awful lot of steps just to use a hyphen instead of a forward slash). Will follow-up on the FileMaker Bento forum.
Post a Comment
Subscribe to Post Comments [Atom]
<< Home