For a successful upload of an XLSX file e.g. as dimension tree or as Actuals in the fact table, the file must fulfill some technical requirements:
- The import data must be in the first sheet of the file and there in a table in the left top corner of the sheet (starting in cell "A1")
- The first row in the sheet must contain the field names from the target table (as you can see them in the table browser of the Web App). Field names which do not match will be ignored in the import process (if you get a "15 of 15 columns ... were recognized" then you know that all your columns in the file were matched to fields in your fact table).
Tipp: create at least one record in your target table and then execute a downlaod (csv) -> this file contains the right field names for your application. - The (matching) columns in the Excel file must be compatible with the data type defined in the data1.io system ...
... if you import e.g. text ("abc") in the column "Measure" (which is the typ "float" what means in SQL that this must be a figure) you`ll get an error message:
- Fields which are defined as "date" (date)
It is not possible to import a number - which would be equivalent to a certain date - into a date field. - Fields which are defined as "boolean" (bit)
We recommend to use the englisch "TRUE" and "FALSE" in the import file since in this case it doesn't mattter if it is a text string or a boolean.
Please be aware that the german "WAHR" and "FALSCH" cannot be imported into a boolean field (e.g. the SORT field in the dimension table) if it is a text string. You can distinguish the text string "WAHR" from the boolean "WAHR" by looking on the alignment in the cell: text is left aligned and boolean is centered in the Excel column. In Excel, you can simply convert the text into the boolean by entering the cell and press ENTER. - Fields which are defined as "required" must be delivered in the upload file, all other columns are optional (from a technical perspective).
In the Fact Table the fields "Record ID" and "SYSTEM IsDeleted" are the only columns which are required. - System fields like the "UID" and "Created UTC" are ignored during the upload since those fields are filled automatically by the system.