Prototypes TabulaP19 · Admin from a spreadsheet

Import a spreadsheet

Drop CSV or XLSX files. They are read in this browser: the characters, the separator, the decimal mark and the order of day and month are worked out, then each column’s type, the keys of each sheet and the columns that point at them. Nothing is sent until you make the workspace.

Drop CSV or XLSX files hereor choose them. Several files become one workspace.
How Tabula is built
  1. The browser reads the files. A CSV is read as UTF-8 when every byte fits, and as Windows-1252 when not, which is how a Danish Excel saves it. The separator is the one that splits the first 200 lines into the same number of fields. An XLSX file is a zip of XML files: Tabula reads the zip’s directory, inflates each file with the browser’s DecompressionStream, and reads the shared strings, the date styles and each sheet’s cells.
  2. Each column gets the first type that at least 90 % of its values fit: yes or no, a date, a code, a number, an amount, an email, a phone number, a list of values or text. The decimal mark comes from the values in the file that can only be read one way, and the order of day and month from the dates where the first part is above 12. The values that do not fit are listed with their rows.
  3. A key is a column whose values are all filled and all different. A column in another sheet points at a key when its values are among the key’s values. The rows whose value points at no row are listed.
  4. The workspace holds the rows on the server, and each person has a token. A role’s rules are written in the design system’s query language and run by the design system’s engine on the server: the rows it may see, the columns hidden from it, and the rows and columns it may change. A column hidden from a role is left out of every answer, log entry and live message the role gets.
  5. In the browser the rows are typed columns in a Web Worker: numbers and dates in Float64Arrays, text as codes into a list of its different values. A query is parsed by the design system’s engine and run as loops over the arrays. A test runs 1,000 random queries on 10,000 rows through both and gets the same rows in the same order.
  6. A change is sent with the value it was made on. The server makes it only if that value is still in the row, and otherwise answers with the value that is there now and who wrote it. Each change is an entry in the log with the SHA-256 of the entry before it, so the owner’s browser can work out the whole chain again and find an entry that was altered in the database.
  7. Every window of the workspace gets each change over a WebSocket, as its own role may see it, and shows the cell each other person has open.