For the import to work smoothly, the Excel/CSV file containing the data should adhere to the following guidelines:

Formatting

  • Wash your data before importing! One column should contain one datatype. For example, a column containing numbers can not contain any text. Tip: Use the filter function to see the different values in each column.

  • Your column headers will become the default label of the variables in Ledidi Core. You can change the label during the import process or later. The use of quotation marks, commas, or (semi) colons in the column header is allowed but will require some adjustments during import.

  • Start in cell A1. There should be no empty rows or columns “surrounding” the dataset.

  • The file will automatically convert to a comma-separated file (CSV) during import, which means that only the cell’s values are imported, and all formulas and calculations are lost.

  • During the import, only the first worksheet in your file is imported.

Data types

Ledidi Core automatically suggests data types for your variables (columns) during import. If you want to define the data types of each variable before import, you can do so by marking them in your file. Define the type of data each column contains by adding "data type#” in front of the name/header of each column (which will become the name of the variable after import). 

The following types are recognised: 

  • integer (number without decimals) (integer#)

  • float (number with decimals) (float#)

  • category (category#)

  • categoryMultiple (categoryMultiple#)

  • date (date(%Y-%m-%d)#)

  • datetime (datetime#)

  • string (text) (string#)

  • Unique (userDefinedUnique#)

Example

Headers file 1Header 2

NB: Keep only one data type in each column – i.e., do not mix dates in some cells and text in others.

The different data types

  • Numbers without decimals (integers)

These values should have no decimals. Note that if you choose the datatype “numbers without decimals” and there are values with decimals, this will generate an error, which will be pointed out during import.

  • Numbers with decimals (floats)

The use of commas and punctuation as the decimal separator is accepted. These values should not be infinities. Special text values like “inf”, “INFINITY”, etc, are, by default, recognised as infinities. Having those in a file for import will cause an import to fail. There is a limit on the value of a float. We use float64. A float value outside of the range will cause the import to fail. 

  • Category

If you want a categorical variable to have fixed values, you need to manually activate the “fixed values” toggle button for that variable in the “Variables” window after import. NB: Note that your imported values should be identical to the fixed values set in Ledidi; otherwise, the import will fail. 

  • Category multiple

To import values for a variable with the data type Category Multiple, the values should be wrapped in square brackets and separated by the vertical bar symbol. Example one value: [Value1] or [|||Value1] Example several values: [Value2|Value3] or [Value2||Value3|] If your values are spread out over multiple columns, you can use the formula ="["&CELL NAME&"|"&CELL NAME&"]" in Excel to merge them into one column

  • Date

    • Ensure that cells containing dates are formatted as dates (YYYY-MM-DD)

    • Define the data type in the variable header by adding "date(%Y-%m-%d)#" in front of your variable name. This is not obligatory, but it will make the import more likely to succeed. When you are adding more entries to an existing project, it is highly recommended that you do so.

    • Save your file as a CSV file before import

  • Text

  • Unique

Note that the uniqueness of values in the imported file is not validated by Ledidi Core. However, if you add more entries to your dataset by importing, the imported values are validated against already existing data for that variable. If any matching value is found, i.e., a non-unique value, the import will fail.

  • Time (duration)

Time (duration) variables should be imported as integers or floats (i.e., numbers with or without decimals). 

  1. After import, go to the Variable window and change the data type to “Time (duration)”

  2. Select the correct Source Unit, i.e., the unit used in your import file. Note that you can only select one source unit. If you are importing a variable in microseconds, it has to be an integer, not a float.

  3. Select the time format in which you want your variable displayed in Ledidi.

NB: When changing the minimum time unit to a higher value than the source unit (e.g., time unit = hours and source unit = minutes), some data will be lost due to rounding. This is irreversible.  NB: It is not possible to import more data to an existing Time (duration) variable. (See also Import of additional variables to existing data)

Specific guidelines for Excel files

The abovementioned guidelines apply to Excel files. Note that both the “.xls” and the “.xlsx” (Only “Excel Workbook (*.xlsx)” and NOT “Strict Open XML Spreadsheet(*.xlsx)”) formats are accepted.

Specific guidelines for CSV files

When you import your data stored in a CSV file:

  • Make sure a comma is used to separate values. Tab (\t), semicolon (;) or bar (|) are also accepted.

  • Carriage return newline (\r\n) and newline (\n) are accepted as row separators.

  • If your value contains a comma (e.g., in text), make sure a double quote surrounds it. E.g., “This will be seen as one value, even though it has a comma in it” vs. This will be seen as two values, because there are no double quotes.

If your CSV file is very wide, we will not be able to infer the CSV value separator, and we will fall back to using “,” as a separator.

CSV files may be encoded using different formats; both utf-8 encoding and windows-1252 are supported.