Import data from XLS tables and others formats (XLS, JSON, ...)

Preliminary

  • Now the framework permit to export data, but not to import and the exported tables are static.
  • From GUI user interface the current release can only export XLS files.
  • The framework already allows you to import and export data in XML & JSON formats using API.
  • There is a CRUD/REST interface to inport/export data in XML,JSON format but the target is to create a GUI user interface to manage user action to import/export data to support end users.

We have to evaluate an import/export functionality, used from GUI, that positively implement the following features:

  • In a first version we can use the same format as the export data, but later it must be possible to view XLS table names to associate them to the target table fields.
  • Frequently exported fields contain codes or descriptions instead of the corresponding primary keys. So in case of import we must do the inverse process looking the keys from codes or descriptions.
  • The current export allows to export only the children of one father at a time and the parent data and children are separated (separate tables are to be exported). Which format we can adopt to export more children and parent/children in the same document (multi spreadsheet)?
  • The function must support other formats, also in a later release, such as JSON, XLS, TAB separated text, etc.
  • For printing reports in ODT / PDF format is used unoconv - evaluate whether it is convenient to import/export spreadsheets in various supported formats
  • The GUI is also useful to show the data as they are imported, indicating the new records, modified records, edited fields and any of the fields that will be deleted (if it is planned to clear fields not present in the imported spreadsheet).
  • The configuration of an import will be saved for later reuse, both for import and for export.
  • To define the GUI layout would be useful to identify some examples of applications that already do this import like Import and Export Wizard funtionalities:
    • ...
    • ...

  • In all phases the data formats must be agreed with the customer.
  • For the GUI should be used the MasonSQL framework components and widgets or Dojo Toolkit widgets.

New specifications

The new GUI interface must implement the following features (in two phases):

Phase 1

  • Interface to use unoconv (Universal Office Converter) to import/export many tabular data
    (MasonSQL already use unoconv to print forms in PDF format from ODT templates)
  • Allow to import/export data in XML/JSON format
  • We can use the same format field as the export data, but the user must be able to associate the input/output fields with the target/source fields that have different names.
  • All choices must be saved for later use by assign a name, selectable by the user on subsequent import/export operations
  • The GUI must be display in advance the data as they are imported (new and modified records).

Phase 2

  • The GUI must be display in advance the data as they are imported, indicating the new records, modified records and any of the fields that will be deleted (if it is planned to clear fields not present in the imported spreadsheet).
    It is possible to edit the fields prior to import/export.
  • The current release allows to export only the children of one father at a time and the parent data and children are separated (separate tables are to be exported). New implementation must permit to import/export more children and parent/children in the same document (multi spreadsheet), zipped archive of multiple files and JSON/XML.
  • Frequently exported fields contain codes or descriptions instead of the corresponding primary keys. So in case of import we must do the inverse process looking the keys from codes or descriptions using clever algorithm ... and vice versa when you export.
  • Able to import/export datasheets with data collected in different types of rows with rows to be discarded if they do not match the conditions (we will provide the sample stilesheets).

Implementation Details for Phase 1

Json2Ods, Ods2Json

The various formats are obtained with format conversions using "unoconv". If possible, the Perl code will use a single format for export/import to be recognized by Unoconv.

In case of XLM/JSON format, Unoconv is not usable, but we can define a bidirectional conversion scheme ODF <<-->> JSON and XML <<-->> JSON so, you can use only JSON as internal format.

JSON and XML are already defined using REST interface, so i think is more simple to extend this interface which already allows you to import or export data of the framework objects. In the first phase I think current JSON interface is sufficient. CRUD/REST interface (and data format) is documented in MasonSqlCRUD

If we define a conversion schema to/from ODS we can manage all spreadsheet formats using Unoconv to convert.

To copy the methods used in labs (JSON_SELECT_FIELDS, JSON_SELECT_FIELDS, JSON_INSERT, JSON_UPDATE and JSON_WHERE) into the framework, in the file /opt/masonsql/htdocs/data/global_autohandler so we can use them in any .mql files to manage JSON sub-fields

you have recently created a pop-up window in the "labs" server to manage some print options. With a click on the [XLS] button you can open a tiny pop-up window where the user can select:
  • data format to import/export (default depend by an Apache config variable DatasheetDefaultImportExport)
  • a combo list, to select the name of the previously saved configuration (not mandatory, if empy we import/export with default settings)
  • preview flag (so we can open a form with data to import/export and the user can confirm of decline)
And after all, the buttons [Import], [Export] and [Config]

With preview not set, the buttons [Import] and [Export] will start upload/download without open the preview window. The [Config] button open the window to config the imports/exports types (each with a unique name and a children to define the name used to naming the colums and a flag to select (so we can exclude fields).

How to open a new window?

In the past I have used tree methods:
  1. ) Using window.open( ... ) - this method is slow because it reload the framework (Dojo and MasonSQL javascript & CSS files)
  2. ) Using Dojo to open a pop-up window - it can prevent use of the underlying form, feature not always useful
  3. ) Opening a new Tab in the framework tabs

All three methods can display HTML downloaded from server of from browser.

In our case we can use 3) to open Config and preview window; 2) to open pop-up from [XLS] button.

Example how to open a new Tab is observable in the file labs:/opt/labs/htdocs/data/acc/autohandler, method open_allegato like:
masonSql.mainTab.activateMenuTab('FormName', function(container){
// code to run when the tab will be ready
. . . .
}, true);

in the code to run when the tab will be ready you can set the where conditions and you can use any variables defined from caller.

public.tabular_data

# type varchar[]

We can use the MasonSQL framework using a table with a column of type varchar[] to manage all colums. Or you can use JSON (I have already create the functions to read/write data to/from JSON in ".mql" object and you can create the methods to dinamically create the list of field name (method FIELDS).

# model name

The name used by user to identify the export --> "All choices must be saved for later use by assign a name, selectable by the user on subsequent import/export operations"

Config Form

in Config form we can manage the differents configuration to import/export with (in the 1st phase):
  • the name of the config
  • field names table used to naming the columns to be exported - this table contains all name defined in method FIELDS in the table (file .mql)
  • others? (in 2nd phase here we manage how to include children data)

In public.tabular_data you can define differents configurations to import/export the data. In it you must have the own user id, the table id (like 'public.anagrafiche'), the name to identify it, the list with table name/speadsheet name association, the list with included children (in 2nd phase). I suggest you to save these two last lists as json fields (as I have done in application labs).

You can manage these last 2 list using children forms of tabular_data.mql

#others? - the preview flag

No this is an option used by user when use the button [XLS] eventually we can define a field - default preview flag setting
Topic revision: r10 - 08 Sep 2023, GuidoBrugnara
This site is powered by FoswikiCopyright (©) Leader.IT - Italy P.I. IT01434390223 Privacy policy & use of cookies