File import functions improvement

[Keorl]

Hi,
The improved file import functions in v6.3 are great.
They can however still be improved. I also found issues in the existing functions.

I'll describe it through my use case.
I have a tabulator that displays existing data. Some columns are editable. On top of editing fields one by one in the table, I want to let the user import a file to update data massively in the table before saving.
The first issue I met is that the current Tabulator.import() function can only load new data. It would be great if it had an update mode) !
The import mode parameter could have these options :

• replace
• append
• updateOnly (update lines when the index exists in the current data, ignore other lines from the file). Ideally have some hook to handle rejected data.
• updateOnlyEditable (update lines when the index exist, and only update fields that are editable - ideally evaluated from left to right as "editable" can be a function and in my case returns true depending on data set in previous columns)
• updateAll (update lines when the index exists in the current data, add other lines from the file). Ideally add a "dataAdded" event so we can detect new lines and do something about them (in my case, loading their non-editable fields from the database)
• updateAllEditable (update lines when the index exists in the current data, only update fields that are editable in these lines

My workaround for now is this : I instantiate a second Tabulator object, with the same columns, on a document.createElement('div') without displaying it anywhere in the page. I call import on that second tabulator and then tab2.getData(). I can then compare the new data to what's in the real table, see if everything can be updated, otherwise ask the user if they want to extend the displayed scope (I then load missing lines from the database so that all lines in the file will update something), keep only editable fields, and finally call tab.updateData()

I encountered a few issues with this (using XLSX importer, but iirc the csv importer did the same) :

• The first one is that Tabulator doesn't match columns if the import file doesn't have the exact same set of columns as the table. As soon as there are less columns (for example if the use only imports the index and actually editable columns), they're not recognized anymore and data is just loaded in column order. For example if I have 10 columns (x1, x2, index, x3, x4, x5, editable1, editable2, editable3, editable4) and the user imports the only ones that matter (index, editable1, editable2, editable3, editable4), data will be loaded with x1 = index from file, x2 = editable1 from file, index = editable2 from file, x4 = editable3 from file, x5 = editable4 from file.
• In this situation, the header row will be loaded as data, contrarily to what Tabulator 6.3's documentation says ("Tabulator will treat the first row of the spreadsheet as the column headers"), making even more of a mess. On my computer, it works properly with a file exported from Tabulator (tab.download('xlsx')) and immediately re-imported as-is or with data change. On my colleague's computer, even that doesn't work despite having all columns (data will be loaded in the right fields, but the header will be taken as a line of data).
• All regardless of how far I go in importHeaderTransform to make sure that the headers are the right ones.

My workaround for this is to use autoColumns, using importHeaderTransform with some reflection on my column definitions to fall back to field names and then get proper data with getData().
But this workaround raises another issue : my mutatorImports are obviously not called anymore, as they're obviously defined in my main table using column definitions but not in the invisible import tabulator using autoColumns. This is where I use a 3rd workaround : I hijacked the importDataValidator function to call all the mutatorImport using some more reflection !

    private colonnes: ColumnDefinition[] = [...];
    this.tabulator = new Tabulator(this.divTab, {
      ...
      columns: this.colonnes,
      ...
    });
  this.tabulatorImport = new Tabulator(document.createElement('div'), {
      autoColumns: true,
      // @ts-ignore // unknown yet in @types/tabulator-tables
      importHeaderTransform: this.importHeader.bind(this),
      // @ts-ignore // unknown yet in @types/tabulator-tables
      importDataValidator: this.appliquerMutators.bind(this),
    });
  private appliquerMutators(data: MyModel[]) {
    // @ts-ignore // mutatorImport unknown in @types/tabulator-tables for now
    this.colonnes.filter(c => c.mutatorImport != undefined).forEach(c => {
      data.forEach(d => {
        // @ts-ignore
        (d as any)[c.field!] = (c.mutatorImport as Function)((d as any)[c.field!] ?? '', d, undefined, c.mutatorImportParams, this.tabulator.getColumn(c.field)); // btw getColumn(c) doesn't work for my index column, which is why I use getColumn(c.field) => is it a bug ?
      });
    });

    // now do some actual validation and return true or false
  }
  private importHeader(header: string, headers: string[]) {
    let simplifier = (x:string) => x.trim().toLowerCase().normalize("NFD").replace(/\p{Diacritic}/gu, "").replace(/\s/g, "");
    let headerSimp = simplifier(header);
    let col = this.colonnes.find(c => simplifier(c.title) == headerSimp  || simplifier(c.field ?? '') == headerSimp);
    return col ? col.field : headerSimp;
  } 

Obviously, if Tabulator is updated/fixed to match imported columns by name or field, and to solve the issue where headers are considered data if columns are not fully matched, I could then change my "tabulatorImport" to use columns: this.colonnes and remove the whole mutator reflection workaround.
If tabulator get the main functionality I'm suggesting here (import modes), I could then entirely get rid of this invisible 2nd tabulator.

Another improvement that would be great : in tabulator.import(), add a 4th parameter to give access to SheetJS's options.
I wanted to allow my users to select .xlsx, .ods and .csv as suggested in Tabulator's documentation. Great idea. However, SheetJS will interpret and transform the CSV data in weird ways. Date-like objects will be read as american dates. In a column where I'm actually importing dates (EU style), I get mixed data : "28/03/2025" is imported as a string, but in the same column "02/01/2025" is imported as an excel-like number (number of days since 1900, plus some weird digits after decimal point) which corresponds to 1st of February. My mutatorImport for that column handles both anyway, but can't guess random american day/month swapping. I had to remove .csv from the allowed files. If I want to import csv, it'll have to be a separate button using Tabulator's real csv importer. But if you give some access to SheetJS's import options (or at least hard-code its raw csv options in tabulator's import function), we can work around this. I found that SheetJs has "cellText, cellDates, header, raw, dateNF" options.

Last improvement that would be great : allow CSV with different separators. If my users save as csv from Excel, they'll get semicolons (even using excel's "csv (comma)" option !!!). Tabulator only accepts comma, both in its own csv importer and in the xlsx importer with csv files.