When editing metadata files, the files that MediaBeacon will use as input for Import or Bulk Select, there are a best practices for specific editors.Please note that this is not an endorsement of these products, and they are property of their respective companies.
- Don't allow Excel to open character-delimited files directly. Many users will have Excel set as the default application for CSV files, and are used to opening them without realizing that Excel changes data in CSV files as part of then opening process. Excel is known to:
- Misinterpret UTF-8 characters
- Assume date formatting and alter data format silently. A value like "2018-10-08", may get changed to "10/8/2018".
- Note: ISO 8601 date and time values (2018-10-08T13:20:55+00:00) are not reformatted.
- Automatic incorrect formatting of numeric fractions as date values, "1/2" becomes "1/2/2018"
- Each time an Excel file opens a CSV, it will make the same mistakes, as noted above. A best practice is to save the correctly-formatted file as a native Excel file, and export CSV copies for use as metadata files for MediaBeacon.
- Excel's XML format is not directly compatible with MediaBeacon
Workarounds for Excel
- Correcting after opening
- Date fields can be corrected by applying a custom cell format (yyyy-mm-dd) before saving.
- Correcting via Data Import
- This is the best practice recommendation, see the Opening CSV files with Excel's Data Import Function article.
OpenRefine is a free software tool from Google, that can be very good for cleaning up CSV style data, without having to use something like Excel. It has a steeper learning curve, but can perform many complex operations easily.
Sometimes, it becomes preferable to inspect or manipulate a CSV file (or other character-delimited file) and avoid reinterpretation by another pieces of software. In these cases, a plaintext editor is required. These editors are contrasted with and are not interchangeable with "rich text" editors, such as Microsoft Word, or WordPad.
A highly extensible cross platform editor that be used to:
- Verify original character encoding without alteration.
- Find and correct formatting issues.
- Find and replace non-printing characters, or any other characters using RegEx.
- Manipulate files in a very low-level way.
Sublime Text 3 also has a large community of developers, who create "Packages" that install in seconds, and provide extended functionality for manipulating text.
A Windows-only plaintext editor. Not as extensible, but often used to edit server config files.