• Search

  
  

• Recently Written

  • Tracking Hours
  • Bento Sweepstakes
  • Not Abandoned, Just Busy
  • New Bento Videos
  • An Introduction to Databases
  • Fields, Records, Tables & Forms
  • Fixing CSV Exports
  • Importing Related Data Into Bento
  • Field Orders In CSV Exports
  • The Bento User Guide

• Categories

  • Bento Basics
  • Bento Examples
  • Bento News
  • Bento Tips
  • Site Notes
  • Tutorials

• RSS Feed

  • Subscribe to our RSS feed

As of Bento 1.0v2, it appears that when you export data the CSV file may not be properly formed if any of your fields contain one or more double-quotes. The CSV file format allows for each field to be surrounded by double-quotes and to avoid confusion with these, any double-quotes which appear in a field are doubled-up. In Bento’s case, the issue seems to be that for each double-quote in the field, the closing double-quote is moved one character to the left.

Click for a Larger View

To solve this I have created an AppleScript which will re-process an exported CSV file and will attempt to re-position the closing quote in the correct position.

Click for a Larger View

Unfortunately there is a restriction which is that your Library should only have one field which contains double-quotes. If you have double-quotes in more than one field then you should probably wait for an official fix.

In addition, before you export your data you need to add a new field to your Library so that the script can find the end of each record. This filed should contain a single character and this character should not appear in the text of any of your fields. This is most easily achieved via a Calculation field:

Click for a Larger View

The name of the field is irrelevant. but in the above screenshot it is called ‘EOR’ which is short for ‘End of Record’ and I have used the pipe ( | ) character to identify the end of each record.

Once you have exported your CSV file you need to process it using the Double Quote Fixer script. Simply double-click on the Double Quote Fixer file (downloadable below) which will open the file in an application called Script Editor.

Click for a Larger View

Just below the grey comments you will find some configuration settings which you may like to change.

Click for a Larger View

• Remove_Carriage_Returns will strip out any carriage returns. This can be useful if you are, for example, processing text which will be imported into something else which does not handle multi-line CSV records well. The setting should either be true or false.
• Record_Delimiter is the character you used in the record delimiter field as described above.
• Double_Quote_Symbol is a symbol which the carriage returns will be replaced with temporarily. If you use this character in your text you need to change it to something else.
• Double_Quote_Opening_Closing_Symbol is another symbol which is used to temporarily replace carriage returns. Again, if you use this character in your text you need to change it to something else.

No not change the New_Line_Character.

Now you can run the script via the Run button in the toolbar or the Run command in the Script menu. You will be asked to select the original export file and then when it has been processed you will be asked where the fixed file should be saved.

Please note that the process can take a while is your CSV file is large so please be patient.

Download the Double Quote Fixer.

Comments