Recently Written- Lion, iCloud, and Bento… October 25, 2011
- Bento 4.06 Available October 1, 2011
- Bento Price Drop Confirmed! August 23, 2011
- Bento 4.0.5 Available! August 9, 2011
- It’s Confirmed: Lion Is Fighting Bento! August 1, 2011
- Bento 4.0.3 Update Is Unstable May 21, 2011
- Customizing Themes Even Further April 23, 2011
- Sorely Needed: Page Guides On Forms! April 23, 2011
- List Field Refinements Needed April 12, 2011
- Dear Filemaker & Bento Product Manager: April 11, 2011
- New Free Service: iChat Screen-Sharing! April 10, 2011
- Bento 4 Crashes Reach BentoUsers! March 17, 2011
- Finally: Bento 4 – First Review! March 16, 2011
- Bento Black-Friday Pricing! November 26, 2010
- Important Software Update – Bento 3.0.5 November 4, 2010
Mar
1
Importing & Exporting Data Via CSV Files
Filed Under Bento Tips
Bento™ can import and export data from and to CSV files. These are essentially plain text files where each record is stored on its own line and each item of data, or field, within a record is separated by a comma, hence Comma Separated Value or CSV file.
This article will explain how to import CSV data into both new and existing Libraries, how to export data from a Library to a CSV file and highlight some limitations with the CSV file format.
Sample Data
For the purposes of this article we are going to use a very small sample file which contains the following data:
The first line in the data file contains the field names whilst the other three lines are the actual data.
Importing Data Into A New Library
There are two ways to start the process of importing data into Bento:
1. Select “Import…” from the File menu.
2. Drag and drop the CSV file into the Sources section of the main window.
Tip: If the Sources section is not visible then select one of the first two options under Show in the View menu.
Either method will result in the main Import dialogue being displayed with the only difference being that if you dragged and dropped a CSV onto the Sources section the file that will be imported has already been selected.
Once a source file has been selected the dialogue fills in some additional details:
By default the name of the data file is used as the name for the Library that will be created but you can edit this value in the ‘Choose a target’ area.
The grid area in the middle of the dialogue is where you can configure how the fields in your CSV file will be handled
The first column, Record Values, will show you the values of the fields in each record. You can move through the records in the file using the arrow buttons just below the grid:
The second column, New Field Names, lets you edit the field names that will be created in your new Library. By default the import process assumes that the first row in your data file contains the column or field names so this is what you will initially see listed here.
A nice feature in the import process is that you can select any of the records as containing the field names or have none selected in which case you can edit the field names yourself. If the ‘Use this record’s value as column names’ is checked for any of the records you are viewing in the first column then the record values from the first column become the field names. If it is not checked for any of the records then you can edit the field names.
Please note that if you choose a record other than the first one as being the source for the column names then any records before that one are not imported.
You can also change the field type using the third column in the grid. Clicking on the field type for each row in the grid will display a list of the possible field types you can use:
The last setting on this dialogue is whether the records are separated by commas or semicolons.
Finally, a summary area tells you which, if any, of the records will be used to supply the column names and which records will be imported.
Clicking on the Import button should now import the file and create a new Library.
Importing Data Into An Existing Library
The process of importing data into an existing library is very similar to that of importing data into a new library. However there are a few differences and things to watch out for.
First of all, in the main Import dialogue ensure that the target is set to the Library you want to add the data to:
The second thing to note is that the grid behaves slightly differently. For a start, the field names and types are fixed and choosing a record as containing the column names simply determines what the first record that will be imported is. If no record is chosen then all of the records will be imported and if a record is chosen then the records after the chosen one will be imported.
You can now also map which fields in the CSV file relate to the fields in the existing Library. You can also choose not to import a field from the source file or you can add a new field to the Library so that you can import any extra fields.
Assume we have a second data file which will want to add to the original data we have already imported:
This data contains two extra fields, one for the person’s title and one for their sex. The Forename field has also been renamed to First name. When the existing Library is chosen as a target Bento bases the import settings on the existing Library so the three fields in the Library are shown. Bento recognises that there are two fields called Surname and Age in the data file so it manages to map these to the Surname and Age fields in the Library. The other three fields are left for you to handle.
The first step it to map the Forename field to the new First Name field. This is done by clicking in the Record Values column of the grid in the Forename row and selecting ‘First Name’ from the list that appears.
Essentially this means that the data from the First Name field in the data file will be imported into the Forename field of the existing Library.
The Title and Sex fields will need to be added to the Library before they can be imported and this can be done via the ‘Add Field…’ button. Clicking on this will bring up the standard field creation dialogue where the two fields can be created. Once done they should appear in the grid and automatically map to the fields in the data file.
Click the Import button and the new data is appended to the existing data in the Library.
Exporting Data From A Library
Exporting data is much simpler because Bento simply exports a CSV file to wherever you specify.
The main thing to be aware of is that not all of the field types that Bento uses can be exported and the following will be excluded:
- Media
- File List
- Related Records List
- Phone Number List
Limitations Of CSV Files
Unfortunately CSV files are not perfect and there are some limitations involved in using them. Because of these limitations it would be great if Bento supported alternative file import and export formats such as XML.
Some of the limitations include:
- There is no standard CSV format so different applications can and do generate differently formatted CSV files. This can create problems when one application trips over a file created by another one.
- Embedded double quotes can cause problems and actually do for Bento.
- Not all of Bento’s own field types are supported during a data export.
Comments
4 Responses to “Importing & Exporting Data Via CSV Files”
Leave a Reply
This page (more-34) ends by saying:
“Not all of Bento’s own field types are supported during a data export.”
Please tell me which are and which are not supported. I have a situation in which, after export as CSV (semicolon) some fields are enclosed by a pair of double quotes and some are not. I can’t see any rhyme or reason.
Also, how do I find what type (e.g. number, text, date) any field in my library or collection is? I get a choice when I create it but how do I display that info later, please?
John
It’s probably not an exhaustive list but this FAQ entry might help:
http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=6688
Essentially, “when an address, phone number, email, URL or IM account field is created, an associated list field is also created to store one or more items in a list. Bento will not export the contents of these list fields. Be sure the most important entry in a list field is entered first because the associated non-list field can be exported. If you need to export more than one entry, consider creating another non-list field for the second entry. For example, create a Home Phone and Work Phone field. These single fields can then be exported.
Besides list fields, Bento neither exports related records, file aliases nor media fields.”
Regarding the situation where some fields are wrapped in double quotes, the CSV file format generally wraps all text fields in double quotes but leaves numbers, dates, etc, alone. The quotes both tell an application which will import the data that this is a text field and it also allows things like commas in text to be handled properly. However, sometimes even text fields are not quoted, especially when they are single words.
To find out the field type, simply double-click on the field name in the fields list (on the right in Bento 1, bottom left in Bento 2 and you may need to display the list first).
Having HUGE problems importing data from my old FileMaker database into Bento Library. Tried exporting Filemaker as .csv file and importing into Bento existing Library. It says “220 records imported” but there are just 220 blank new rows added at the bottom. Tried many times, same results. All the fields match etc. Soooo frustrating to a new Bento user! Maybe I’ll just stick with FileMaker and chalk the $50 as wasted?
Lois, it is supposed to be straight forward. Two things to check and try: before you actually push the button to import, you can page through he records to see how they look – right in the import dialog box. Have you done that? Second, try to import into a blank (new) Bento library. Bento will automatically create the necessary fields. If that doesn’t work, then there is something wrong with your .csv file.
To get free tech support, also explain your problem in the Bento forums.