Applies to Matrix key projects
CSV files are text files that hold the basic data for the key (feature and entity lists and multistate and numeric score data), with one row per state, and entities and their scores separated by commas. CSV files can be opened in many spreadsheet and database programs such as Microsoft Excel and Access. If you have data in an Excel file or database, you may structure the file into a format compatible with the Lucid CSV format (see below) and export as a CSV file for import into the Lucid Builder.
Download a test key (lucid v4) and its corresponding CSV to help see how they correspond to each other.
CSV Import Options
File orientation
Entity columns
If selected, Entities are expected in the columns of the CSV file.
Feature/State Columns
If selected, Features/States are expected in the columns of the CSV file.
Import options
Merge existing scores
Scores contained within the CSV file will be merged with any existing scoring contained for the feature/state entity combination.
Replace existing scores
If selected, any matching feature and entity will have it’s scores removed and replaced with the scoring contained within the CSV file.
Export CSV Key
Filename
Enter or browse for the destination CSV filename using the Browse button located to the right of the Filename text box. If the defined file name already exists within the file system you will need to confirm the file is to be overwritten when Export is selected.
Export Options
Transposed (entities as rows)
If selected, the CSV file that is output will have the Entities are rows, rather than the default Entities as columns.
Split Files
If your key is particularly large, or your version of Excel is quite old and doesn’t support the larger row and column counts now available, the you can tell the Builder to export the key across multiple CSV files based on the maximum rows and columns defined.
Maximum Rows
Maximum rows of data to be output into each CSV file.
Maximum Cols
Maximum columns to be output into each CSV file.
CSV Structure
The structure of a CSV file compatible with the Lucid Builder can be represented as follows:
CSV structure
| Entity 1 | Entity 2 | Entity 3 ...etc | |
|---|---|---|---|
| State 1 | Score | Score | Score |
| State 2 | Score | Score | Score |
| State 3 ...etc | Score | Score | Score |
Entities are represented using the full path name (colon-separated) of the entity in the entity tree. For example, if a key has the following entity hierarchy:
Brassicaceae
Brassica
Brassica oleracea
Brassica juncea
The entity Brassica oleracea would be represented as:
Brassicaceae:Brassica:Brassica oleracea
States are represented using the full path name (colon-separated) of the state in the feature tree. For example, if a key has the following feature hierarchy:
Flowers
Colour
White
Yellow
The state white would be represented as:
Flowers:Colour:white
For multistate features
The value of Score will be as follows:
0 – absent
1 – present
2 – rarely present
3 – uncertain
4 – present by misinterpretation
5 – rarely present by misinterpretation
6 – not scoped
For more information on the Lucid score values, see the topic About Lucid Scores
For numeric features
Numeric features are represented using the full path name (colon-separated) of the feature in the feature tree, suffixed with ‘#’. For example, if a key has the following feature hierarchy:
Flowers
Petals
Length
Where Length is a numeric feature, the feature Length would be represented as:
Flowers:Petals:Length#
The value of Score is a string with five colon-separated values in the format:
Score Type: Rare Low Value: Low Value: High Value: Rare High Value
For more information on numeric values, see the topic Scoring Numeric Features.
Note
Numeric measurement units are not supported by the CSV format and will need to be defined manually after import.
Importing and Exporting data from Excel
If you wish to exchange data between the Lucid Builder and Microsoft Excel via CSV, please note the following:
- Only one worksheet may be imported at a time.
- The first row must contain Entity names and the first column must contain feature:state names. Use colon-separated values to create trees of Features and Entities.
- The first cell must be blank.
- Excel worksheets can have a maximum 1,048,576 rows by 16,384 columns. The maximum characters supported within a column is 255 characters.
- The CSV is UTF-8 encoded.
Tip
The fastest way to see what the Lucid CSV file looks like is to create a small key with a couple Features, States and Entities. Then export the key to CSV. Open the exported CSV file in Excel or your preferred text editor, such as Notepad++.
CSV Special Characters
If your CSV file contains special characters (e.g. non-Latin alphabet) and you wish to open it in Excel then you may need to do the following to open the CSV with the correct encoding:
- Start a new empty spreadsheet.
- Using the ‘Data’ menu. Select the ‘From Text/CSV’ option. Normally the second option on the toolbar as shown here:
Lucid CSV file open in Excel – handling special characters. - Browse for the CSV file. The import dialog should then show, and the correct encoding options should be detected and automatically selected as show here:
Lucid CSV Excel automatic encoding detection. - Finally, click the ‘Load’ button. The CSV data should now appear correctly encoded in your spreadsheet.
