Import Excel files

Web Dashboard allows you to import data from Excel files into different databases managed by Web Dashboard. The Excel sheets should contain tabular data disposed in one area only.

The file to be imported can be located on a local directory or on server side inside any folder defined in Web Dashboard in Administration > Folder.

The information you provide in order to import the Excel file is saved as a template. This allows you to re-import the file in the future.

A three-step wizard helps you insert the data from an Excel file into a database managed by Web Dashboard. The wizard analyzes the file, scanning the first 15 lines to determine the type of data in each column.

  1. On the Main menu, click Data management > Excel Import.
  2. A list of previously created import sessions is displayed.
  3. Click the New document button icon green plus sign to create a new Excel import.
  4. A new document opens and the Description tab displays.
  5. On the Description tab, you specify information about the import. Enter information in the following fields:
  6. Field Description
    Name

    A unique identifier for the Excel import session. The name is a mandatory field.

    Description

    A user friendly label of your Excel import session to be displayed across the GUI. The description is a mandatory field.

    Entity Owning entity (see Set up access management). This will impact the access rights for this object.
    World The World pointing to the database into which the data from the Excel file will be imported.
    Source location

    Use these buttons to specify the source of the import.

    Select either the local directory or the on server folder radio button.

    The file can be located on a local directory or on server side inside any folder defined in Web Dashboard in Administration > Folder. After you select the type of location, choose the file to import data from. For files located on server site, click the folder icon to open a file chooser popup. The file chooser popup lists all the Folders defined in Web Dashboard.

  7. After you select a file, click on the green arrow on the right to go to the next step, on the Source tab.
  8. The file is analyzed, a preview and some basic options are displayed.
  9. If the Excel file contains more that one sheet with tabular data, each sheet can be individually handled. Uncheck Import worksheet to ignore the current sheet.
  10. If the first row in the Excel file is the header of the table, select the option, First row is label.
  11. Click More details to further modify the import settings. You can then choose the data type of each column and which part of the sheet to import. If you set the End row to zero, the whole sheet is imported.
  12. Click the green arrow on the right to go to the next step on the Destination tab.
  13. Data will be imported in a table. If the table doesn't exist, it will be created. By default, a table name will be generated, and the table will be created in the previously selected World (see Create a world ). By checking the option Create data dictionary the application will generate a new data dictionary (see Build a data dictionary) representing the data from the Excel file. Therefore, after the import is finished, data is ready for building reports.
  14. Click More details to configure how to insert data in the database.
  15. On the Destination tab, enter values for the following fields:
  16. Field Description
    Create data dictionary Check this option to create a new standard data dictionary based on the table defined.
    Import type

    Choose from the following:

    • Carbon Copy — data added replaces data contains in the table, if any
    • History — data is added to the existing table without delete the old data.
    Table The name of the table to be populated with the source data.
    Catalog The name of the database catalog.
    Schema

    The name of the database schema.

    Create table if needed The table is created if it does not exist. Otherwise, you can use an existing table.
    Delete type

    When you use the Carbon copy import type, use this field to select the Delete type:

    • Delete — Deletes all the rows from the table, but does not free the space containing the table.
    • Truncate — Deletes all rows from the table and frees the space containing the table. You cannot use this option when the table is referenced by a Foreign Key.
    Commit Type

    Choose from the following:

    • Line by line — Commits after each line is written.
    • Global commit — Commits once after all lines are written.
    • Number of lines before a commit — Commits after a number of lines you specify.
    Batch execution

    Batch execution allows you to group related SQL statements and into a batch and submit them with one call to the database.

    • Deactivated — Batch execution is unavailable.
    • Global batch — Groups all SQL statements into a single batch. This mode of batch execution improves performance as there is only one call made to the database.
    • Number of lines in a batch — Split SQL statements into multiple batches. This mode of batch execution generates multiple calls to the database.
  17. Click the green arrow button on the right side of the screen to import your Excel data.
  18. When lines are inserted into the table during import, the result of the transaction is displayed as a message.
  19. Click the Save button.
  20. Your settings are saved allowing you to perform the same import later. The reference to the Excel file, itself, is not saved. This means each time you want to reuse these settings, you must select an Excel file again.

Related Links