User Interface

Introduction

The user interface serves as an assistant for the creation, management, and parameterization of Load Configurations. Once created, they can be executed from the user interface itself or via the command line.

• The chapter "Configuration Management" describes that part of the user interface that is independent of the objective of Excel To Database.
• This chapter describes that part of the user interface that allows the parameterization of Configurations for this application.
• In this chapter and subsequent chapters, the different parameterization options for MS Access, SQL Server, and Oracle will be analyzed, their implications for data loading, and their behavior depending on the different data providers and drivers.

Workbook and Sheet Panel: Intelligent Data Source Selection

Select which sheets from the Excel workbook will be processed. The ability to handle multiple sheets simultaneously drastically optimizes loading time in high-volume environments.

After selecting a Configuration in the "Configuration Panel" and clicking the "Edit" button, this Panel appears. It contains, at the top, the controls described in "File Selection" and "Database Connection". At the bottom, the buttons "Save", "Cancel", and "Back" appear.

You can start by selecting the workbooks you want to load or by establishing the connection string to the target database. It doesn't matter.

• Back

  Closes the Workbook and Sheet Panel and goes back to the Configuration Panel.

• Target Database

  With the "Database Connection" control, the connection string to use and the target database for the data are determined.

  After establishing the connection string, the "ComboBox" is filled with the names of the existing user tables in the selected Database.

  Connection strings are stored in the JSON file UNENCRYPTED, which means their security is compromised since the password is stored in them. This situation will be resolved in future versions.

• Data File Selection

  After selecting the files to load, a small hierarchical tree is displayed in the panel indicating the name of the Workbook and the Sheets each Workbook contains.

  The order in which the Sheets appear is the order in which they will be loaded into the database. In future versions, it is expected that this order can be modified.

  Excel To Database DOES NOT require the installation of any driver or Office application to read this type of file. Therefore, only the new spreadsheet standard, i.e., books with the ".xlsx" extension, are allowed to be loaded. Books with the ".xls" extension (Office versions from 97 to 2003 inclusive) cannot be loaded with this application.

Sheet Load Options

Next to each Sheet are a series of configuration options detailed in the following sections.

Sheet Load Options

To demonstrate the behavior of the different options in the application, a series of tables for MS Access, SQL Server, and Oracle have been prepared, along with several Excel Workbooks. The table creation scripts, the workbooks, the MS Access databases, and the Configuration file for all chapters can be downloaded from: "Example Downloads".
The table creation scripts are also shown in the documentation, and the Excel Sheets used at each point are also shown in the documentation as images.

DROP TABLE IF EXISTS [dbo].[Tabla Interfaz Usuario];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabla Interfaz Usuario](
  [Char5]                [char](5)       COLLATE Modern_Spanish_100_CS_AS         NULL,
  [Char5 N Null]         [char](5)       COLLATE Modern_Spanish_100_CS_AS     NOT NULL,
  [Char5 N Null Def Val] [char](5)       COLLATE Modern_Spanish_100_CS_AS     NOT NULL CONSTRAINT DF_TIU_Value_Default Default 'V. D.',
  [Char5UTF8]            [char](5)       COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL,

  [SmallInt]             [smallint]                                               NULL,
  [SmallMoney]           [smallmoney]                                             NULL,
  [DateTime]             [datetime]                                               NULL CONSTRAINT DF_TIU_Value_Default2 DEFAULT getdate(),

  [Identidad]            [int]                              IDENTITY(1,1)     NOT NULL,
PRIMARY KEY CLUSTERED 
(
  [Identidad] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
       ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

• Load Sheet

  To load a specific sheet, the "CheckBox" must be CHECKED (in the previous figure, it is the CheckBox inside the red circle). Certain conditions must be met:

  • The destination table must be selected.
  • Column mapping must be completed.

  Note that certain actions performed by the user or the application may check/uncheck this load "CheckBox". Therefore, it is highly recommended to pay close attention to this option.

  Note: These CheckBoxes, when checked, consume a lot of CPU resources in virtual machines as mentioned in "Stop/Start".

• Header Search Start Cell

  Excel To Database assumes that the first data line contains the column headers. It is designed to search for the start of headers from the cell indicated in this field: It begins by searching for the first non-empty cell horizontally (by columns) until it reaches the last column indicated by the used range (UsedRange) or until it finds a merged cell. If it finds nothing, or the first cell found is a merged cell, it continues to the next row.

  NOTE: The data range to be loaded should NOT contain any merged cells. When it finds the first one, it will stop reading data from the sheet and load everything read up to the row before the merged cell found. This is logical since Excel To Database cannot deduce which column/row the data in a merged cell belongs to and does not make assumptions.

  In the previous example, a workbook with different data ranges is shown. If Header Search Start Cell contains the default value ("A1"), it will start the search in that cell. It will search the entire row, find nothing, and move to the next row. In the second row, it finds the header "Char5" since the next cell is a merged cell. Thus, the data range to be loaded will be "D3:D4" since cell "D5" is empty, meaning it found a row where all data is null and stops reading data there.

  Column Search - 1

  

  If Header Search Start Cell is modified to start in cell "G2", it will first encounter a merged cell, which will cause it to move to the next row. In this new row, it finds the headers "Char5", "SmallInt", and "SmallMoney". Thus, the data range to be loaded will be "H5:J10" since "I11" is part of a merged cell, and the data reading stops there.
  The following figure shows the result found:

  Column Search - 2

  

  In the Mapping Panel, it indicates that the header start is found in cell "H4" and the end in "J4". The headers found are "Char5", "SmallInt", and "SmallMoney".

• No Error Values

  When this option is checked, during the data reading phase, Excel error values are replaced with the value indicated in Default Value/NULL Values from the Mapping Panel or NULL if nothing is specified.

  In figure Column Search - 1, cell "H6" contains the error "Division by zero". When attempting to load the sheet, the Results Panel shows the error message: "Cell H6, Value: #DIV/0! is an ERROR data type.". The data is not loaded into the database.

  No Error Values - 1

  

  However, if this option is checked, the error is reported, and the corresponding column will load a NULL or the Default Values/NULL Values indicated in the Mapping Panel. Along with the previous error message, the following message is added: "It will attempt to load the cell as NULL (or the default value). "No Values Error" option checked."

  No Error Values - 2

  

• Without Conversion Errors

  When this option is checked, data conversion errors are replaced with NULL or with the Default Values/NULL Values indicated in the Mapping Panel.

  Simply put, Excel To Database contains its own data checking algorithms and data type conversion algorithms. When these algorithms fail, what is known as a data conversion error occurs. Excel To Database reports this error:

  Cell: I5, Value: Bad Data, Table: dbo.Tabla Interfaz Usuario, Column: SmallInt
      Cell content could not be converted to data type Int16
  

  Then, Excel To Database directly assigns the cell's content, unprocessed, to the structure responsible for storing the data (DataTable). This DataTable may contain algorithms that convert the data to the appropriate data type and check certain conditions or constraints.
  If the direct assignment of the cell content to the DataTable produces an error, Excel To Database reports this error, and the data insertion phase into the corresponding table will not be executed.

  Cell: I5, Value: Bad Data, Table: dbo.Tabla Interfaz Usuario, Column: SmallInt
       Input string was not in a correct format.Couldn't store <Bad Data> in SmallInt Column.  Expected type is Int16.
  

  The following figure shows the messages that Excel To Database generates when the content of cell I5 is attempted to be converted to an integer and the option "Without Conversion Errors" is not checked. As can be seen, the data insertion phase into the table has not been initiated.

  No Conversion Errors - 1

  

  If this option ("Without Conversion Errors") is checked, the error is reported, and, additionally, in the corresponding column, a NULL or the value indicated in Default Values/NULL Values from the Mapping Panel is loaded. Along with the previous error message, the following message is added: "It will attempt to load the cell as NULL (or the default value). "No Conversion Errors" option checked."

  No Conversion Errors - 2

  

• Table

  In the "ComboBox" appears the list of existing user tables in the database indicated by the connection string. Simply select the table where you want to load the Sheet's data, after which the "Column Mapping" button will be activated.

• Use Arrays

  When the target database is Oracle and the "Oracle Data Provider for .NET, Managed Driver" is used, there are two high-performance options for data loading:

  • Use Bulk Copy operations (CheckBox "Use Arrays" unchecked) which allow efficient loading of large amounts of data by eliminating a great deal of processing overhead during database writing. However, this method has some limitations.

    Bulk Copy describes these operations and their limitations. Note that Oracle is not known for the reliability or detail of its documentation.

    In Managed Driver the major and serious problems presented by this option are discussed. The most serious is that it allows the insertion of ANYTHING, as described later in Oracle: Truncate Strings, and it can leave the table in an unusable state.

  • The other option is to use Array Binding (CheckBox "Use Arrays" checked) which accelerates the loading of large volumes of data. As previously mentioned, Oracle is not known for providing free information, so the little information available is developer-oriented and, as usual, sparse and unreliable: Array Binding.

    Managed Driver discusses the potential issues with this option, specifically the existing problems with LOB-type fields.

  Although various limitations have been mentioned, users are encouraged to contact us to find solutions to the problems encountered, or failing that, to complete the documentation presented here.

• With Transaction

  Specific .NET Data Providers have the bulk data copy option: Bulk Copy. This option allows for the efficient insertion of large blocks of data, and in the event of an error, it automatically rolls back all inserted blocks.

  However, OLE DB and ODBC data providers perform data insertions record by record. Thus, when an error occurs during the insertion of a particular data row, the remaining rows will stay inserted in the target (CheckBox "With Transaction" unchecked). By checking this option, Excel To Database will use local transactions in the target database, and in the event of an error, all previous insertions will be rolled back. This way, the user only needs to correct the problematic data and try loading again.

Mappings Panel

After selecting the Table to load a particular sheet, the "Column Mapping" button is enabled. By clicking this button, the Books and Sheets Panel closes and this panel opens.

Column Mapping

The system performs automatic mapping by detecting column headers. If names match the target table, configuration is instantaneous, reducing human error in the ETL process.

At the top of the panel, information related to the Excel Sheet and the database Table is displayed:

• The row, column, and cell where the header starts in the Excel Sheet.
• The number of columns, the column, and the cell where the header ends in the Excel Sheet.
• The number of columns in the database Table.

The Panel contains a "Mappings DataGrid" with various columns to define the pairing of the Excel Sheet columns with the database Table columns and specify the load behavior for each column.

At the bottom, there are the buttons "Save", "Cancel", and "Back".

• Back

  Closes the Mappings Panel and returns to the Books and Sheets Panel.

• Mappings DataGrid

  The purpose of this panel is to pair columns: which column in the database Table should each column in the Excel Sheet be loaded into. For this, there is a ComboBox with the list of all columns in the Table, and you simply need to select the column that corresponds to the column in the Excel Sheet. Excel To Database performs an initial column pairing based on their names.

  Obviously, a column in the Table cannot be loaded twice, so it can only be paired with one column in the Sheet. Thus, selecting in the ComboBox a column that was already paired will undo that pairing to make the new column pairing.

  Once the pairing is done, the Load Column CheckBox, located on the left, must be checked to indicate that this column should be loaded. If not checked, that column in the Excel Sheet will be ignored during data reading and loading. It is the CheckBox circled in red in the previous figure.

  Additionally, the DataGrid has a series of columns that define the data load behavior for certain types of columns: "Truncate Strings" for string columns, "Round Integer" for integer columns, and "Round Decimal" for decimal columns (with scale and precision).

  Lastly, in the "Default/Null Values" column, you can specify a default value for when there is no data or it is NULL, or enable the "No Values Error" and/or "No Conversion Errors" options.

  • Load Column CheckBox

    The first column in the DataGrid is a CheckBox column. When checked, it indicates that the Table column should be loaded, regardless of whether it is assigned to a Sheet column or not.
    If it is, it will be loaded with the existing data in the Sheet. If not, it will be loaded with NULLs or the Default/Null Values specified.
    It is the CheckBox circled in red in the previous figure.

    Obviously, only those rows in the DataGrid with content in Database Table Columns that are not empty can be checked.

    This CheckBox will automatically be checked when a Default/Null Value is assigned to a Table column, regardless of whether it is assigned to an Excel Sheet Column or not.

    There is a CheckBox in the DataGrid header that allows you to check/uncheck all CheckBoxes associated with a Table column.

    It is highly recommended to check the status of this column as it is a potential source of oversights.

  • Excel Sheet Columns

    This column in the DataGrid shows the names of the columns found in the Excel Sheet. These are the first rows presented in the DataGrid. Following these are the rows corresponding to the Table columns that are not paired with any Sheet column.

    A Table column will automatically be assigned if both names match. Columns in the Sheet that are not assigned to a Table column will not be read.

  • Database Table Columns

    This column in the DataGrid shows the names of the columns in the Database Table. The pairing is done by selecting from the ComboBox the Table column you want to pair with the Sheet column.
    The DataGrid does not allow two columns in the Excel Sheet to have the same Table column as their destination.

    To unassign a Table column, simply focus on the ComboBox and press the DELETE key, after which the Excel column will be unassigned. A new row with the unpaired Table column will appear in the DataGrid.

    Note that some data types cannot be loaded into the destination. For example, in the case of SQL Server, identity columns or "TimeStamp" type columns cannot be loaded. In future versions, it may be possible to load identity columns.

  • Remarks

    Lastly, this column shows various messages associated with the status of the Excel or Table column. For example, "Table column not in Sheet" to indicate all the Table columns that do not have any Sheet column assigned.

    These remarks are not editable. They are simply application messages.

  • Additionally, the Mappings DataGrid includes the options: Default/Null Values, Truncate Strings, Round Integer, and Round Decimal.

    Due to their importance, they will be treated as independent sections.

Download Examples

You can download the test files from here:

In order for everything to work correctly, you must modify certain files so that the paths point to the folder where the files are located. You will also need to modify the connection strings to point to the databases where you have created the example tables.

For example:
  For MS Access, you must replace the following items so that they point to the appropriate folders:
    "_carpeta": "G:\\Libros Excel"
      "_cadenaConexion": "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=\"G:\\MSAccess\\MS Access ACCDB.accdb\""
      "_cadenaConexion": "Driver={Microsoft Access Driver (*.mdb, *.accdb)};dbq=G:\\MSAccess\\MS Access ACCDB.accdb;defaultdir=G:\\MSAccess;driverid=25;fil=MS Access;filedsn=G:\\MSAccess\\MSAccess_ODBC_ACCDB_X64.dsn;maxbuffersize=2048;maxscanrows=8;pagetimeout=5;safetransactions=0;threads=3;uid=admin;usercommitsync=Yes;app=Excel To Database"

  Something similar happens for Oracle:
    "_carpeta": "C:\\Oracle"
      "_cadenaConexion": "USER ID=UsuarioOracle;PASSWORD=Contrasenia;DATA SOURCE=localhost"
      "_cadenaConexion": "Provider=OraOLEDB.Oracle.1;Data Source=localhost;Persist Security Info=False;Password=Contrasenia;User ID=UsuarioOracle;oledb.net=True;ndatatype=False;spprmslob=False;usesessionformat=True;application name=\"Excel To Database\""
      "_cadenaConexion": "Driver={Oracle in 21_7};server=LOCALHOST;uid=UsuarioOracle;pwd=Contrasenia;dbq=LOCALHOST;dba=W;apa=T;exc=F;xsm=Default;fen=T;qto=T;frc=10;fdl=10;lob=T;rst=T;btd=F;bnf=F;bam=IfAllSuccessful;num=NLS;dpm=F;mts=T;mdi=F;csr=F;fwc=T;fbs=60000;tlo=O;mld=0;oda=F;ste=F;tsz=8192;ast=FLOAT;lps=8192;app=Excel To Database"

  With SQL Server:
    "_carpeta": "H:\\SQL Server",
      "_cadenaConexion": "Data Source=localhost;Initial Catalog=BASEDATOS;Integrated Security=True;Asynchronous Processing=True;MultipleActiveResultSets=True;Application Name=\"Excel To Database\";Workstation ID=Maquina"
      "_cadenaConexion": "Provider=MSOLEDBSQL19.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=BASEDATOS;Application Name=\"Excel To Database\";Workstation ID=Maquina;Use Encryption for Data=Optional;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite"
      "_cadenaConexion": "Provider=MSOLEDBSQL.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=BASEDATOS;Application Name=\"Excel To Database\";Workstation ID=Maquina;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite"
      "_cadenaConexion": "Provider=SQLNCLI11.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=BASEDATOS;Application Name=\"Excel To Database\";Workstation ID=Maquina;MARS Connection=True;DataTypeCompatibility=80;Application Intent=ReadWrite"
      "_cadenaConexion": "Provider=Provider=SQLOLEDB.1;Data Source=localhost;Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=BASEDATOS;Application Name=\"Excel To Database\";Workstation ID=Maquina;mars connection=True;datatypecompatibility=80"
      "_cadenaConexion": "Driver={ODBC Driver 18 for SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=BASEDATOS;trustservercertificate=Yes;autotranslate=No;mars_connection=Yes"
      "_cadenaConexion": "Driver={ODBC Driver 17 for SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=BASEDATOS;mars_connection=Yes"
      "_cadenaConexion": "Driver=Driver={SQL Server};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=BASEDATOS;mars_connection=Yes"
      "_cadenaConexion": "Driver={SQL Server Native Client 11.0};server=localhost;trusted_connection=Yes;app=Excel To Database;wsid=Maquina;database=BASEDATOS;mars_connection=Yes"

"*.dsn" files must also have their content modified to change the file path.
  For example:
    For MS Access:
      DefaultDir=G:\MSAccess
      DBQ=G:\MSAccess\MS Access ACCDB.accdb

    For Oracle:
      DRIVER=Oracle in 21_7
      UID=UsuarioOracle
      DBQ=LOCALHOST
      SERVER=LOCALHOST

    For SQL Server:
      LANGUAGE=Español
      DATABASE=BASEDATOS
      WSID=Maquina
      Trusted_Connection=Yes
      SERVER=localhost

Once you have made the changes, you must replace the "Excel To Database.json" file, which is located in the same folder as Excel To Database, with the modified one.