• • •

Configuration & Automation: The core of MalaCharger tools

Introduction

Insert Bulk Bestia and Excel To Database share the same philosophy for managing the parameterizations of the tasks to be performed:

  • The set of options selected by the user to perform a specific task will be called a "Configuration".
  • Both applications can manage multiple Configurations.
  • They can also store and retrieve these Configurations.
  • They allow editing of Configurations to adapt them to the needs of each moment.
  • They allow system administrators to create load templates that can later be executed by any user without prior technical knowledge. In this way, different data migration profiles can be created for the different parameterized tasks.
  • They display, in the user interface, information related to actions, events, and/or errors that may occur during the manipulation of these Configurations. This information is also stored and expanded in "Application Logs", common to all Configurations.
  • They allow multiple Configurations to be executed from the user interface. They will be executed consecutively, one after another.
  • They allow multiple Configurations to be executed from the command line. They will be executed consecutively, one after another.
  • During the execution of a Configuration, information about what is happening at each moment is displayed in the user interface, such as actions performed by the application, error information, etc. This information is also stored and expanded in "Configuration Logs" files, independent for each Configuration.

The user interface of both applications consists of a "Main Window" in which different "Panels" are displayed according to the actions performed by the user. Several of these panels are common to both applications and function the same way.

When the applications are started, the following "Panels" are displayed (the last two will always be visible):

Main Window

This is the container window for the different panels displayed to the user. It looks different from a traditional window, but its functionality is similar. For example, although the window does not have the traditional borders, typical actions can still be performed:

To resize the window, place the cursor on the window's edges, and when it changes shape, you can adjust the window size.

To move the window, use its top edge with the same gestures used to move any other window.

Additionally, all controls in both applications have associated ToolTips (help that appears when hovering the mouse over the control). The following figure shows the Main Window with the ToolTip associated with the "Close" button.

Main Window

Main Window showing ToolTips for the window control buttons.

  • Min, Max, Restore, Exit

    The "circle" in the upper right corner hides within it the buttons for "Maximize", "Restore", "Exit" and "Minimize", in that order and from top to bottom. When you hover the mouse pointer over the buttons, the corresponding ToolTips are displayed indicating the actions performed by the hidden button.

  • Stop/Start

    Allows stopping or starting the visual effects.

    The visual effects of the user interface consume many CPU resources, especially on virtual machines. Therefore, the visual effects are only activated for a few seconds at the start of the application and after the executions are completed.

    Every rule has its exception: In the "Books and Sheets Panel" of Excel To Database, there are a series of CheckBoxes with animations that also consume too many resources. Since they are a mere whim, there are no plans to remove them.. This resource consumption stops when you switch panels, so it does not pose any problem unless the application is left open on that panel.

  • Language

    Allows changing the application's language.

    Changing the language involves restarting the application WITHOUT ASKING THE USER if changes that have been made are not stored in the JSON file.

    Up to now, not all libraries are multilingual. The application has been developed in Spanish, and the translation to English will be fully completed in subsequent versions.

Configurations Panel

This is the panel that manages the Configurations: Create, Edit, Delete, ...

Panel: Configuration Management

Configurations Panel: 3 Configurations. 2 checked to run, 1 checked to edit which enables admin buttons.

Depending on the actions the user performs, certain buttons will be activated/deactivated. For example, actions that involve altering the Configuration may cause certain buttons, such as the "Execute" button, to be disabled. In this case, to enable this button, it will be necessary to "Save" the Configurations or "Cancel" the changes made.

  • Add

    Allows creating a new empty Configuration. It will ask for a name, which cannot contain the following characters: , (comma), = (equal), " " (space). The name must be unique.

    The button (if it was enabled) "Execute" will be disabled until the changes are saved or canceled.

  • Configuration List

    As Configuration are created, they will appear in the list. Each Configuration has an associated checkbox, indicating whether its tasks will be executed when the "Execute" button is pressed. In the previous figure, the Configuration "Vacía2" and "Vacía3" will be executed in that order when this button is pressed.

    When one of these checkboxes is checked or unchecked, the "Execute" button will be disabled until the changes are saved or canceled.

    Clicking on a particular Configuration selects it for editing. At that moment, the buttons that allow managing it are enabled. In the previous figure, the Configuration "Vacía3" is selected, and it can be edited, copied, deleted, or reordered with respect to the other Configurations.

  • Copy

    This button will be enabled when a Configuration is selected.

    It creates a complete copy of the selected Configuration as a new Configuration. The copy must be given a new unique name.

    The button (if it was enabled) "Execute" will be disabled until the changes are saved or canceled.

  • Delete

    This button will be enabled when a Configuration is selected.

    It deletes the selected Configuration from the "Configuration List".

    The button will be disabled (if it was enabled) "Execute" until the changes are saved or canceled.

  • Edit

    This button will be enabled when a Configuration is selected.

    It allows editing the different options of the selected Configuration. The "Configurations Panel" will disappear, and the panel that allows parameterization of the Configuration will appear. For example, for Insert Bulk Bestia, the "Connections Panel" will appear, and for Excel To Database, the "Books and Sheets Panel" will appear.

    Once the "Configurationss Panel" is hidden, by pressing the "Back" button of the panels that replace it, this panel will be shown again.

  • Up/Down

    This button will be enabled when a Configuration is selected.

    Alter the order in which the Configurations are displayed and/or executed.

    The button will be disabled (if it was enabled) "Execute" until the changes are saved or canceled.

  • Save

    Configurations are stored in a file with JSON format, which is located in the same folder where the application is located and with the product name. For example: "Excel To Database.json".

    This button will be enabled when there are unsaved changes in the Configurations.

    Saves the changes made to the Configurations in the JSON file.

    If the appropriate conditions are met, the "Execute" button will be enabled.

    The connection strings are stored in the JSON file UNENCRYPTED, so their security will be compromised since the password is stored in them. This situation will be addressed in future versions.

  • Cancel

    This button will be enabled when there are unsaved changes in the Configurations.

    Cancels the changes made to the Configurations by reloading the content of the JSON file where they are stored.

    If the appropriate conditions are met, the "Execute" button will be enabled.

  • Exit

    Closes the application. If there are unsaved changes in the Configurations, it requests confirmation from the operator.

    Confirmation to exit without saving changes

    Confirmation to exit without saving changes.

    NOTE: when changing the "Language", the application will automatically restart. Any unsaved changes to the Configurations will be lost.

  • Execute

    This button will be enabled when there are no pending changes to be saved in the Configurations and when at least one Configuration is marked for execution (CheckBox Checked).

    When clicking the button, the marked Configurations will be executed in the order they appear in the "Configuration List". The "Results Panel" will be activated, and it will provide information about the actions being performed, any occurring events, and potential error messages.

    Industrial Automation: Command Line Interface (CLI)

    Both Excel To Database and Insert Bulk Bestia share a robust command-line execution engine, engineered for professional workflow integration. This feature enables system administrators and DevOps engineers to automate bulk data loads without manual intervention.

    By using the /CFG="Name" switch, you can trigger specific configurations from .bat scripts, PowerShell, Windows Task Scheduler, or Process Managers. This decoupled architecture ensures that migration logic remains consistent, whether you are processing complex spreadsheets or synchronizing tables between SQL databases.

    As an example, a ".bat" file is included, which can be invoked from a process manager. The Configurations to be executed should be included in the application's call arguments after the "/CFG=" modifier. If no Configuration is specified in the command line, the marked Configurations will be executed. If a list of Configurations is specified, it should be enclosed in double quotes (") and separated by commas (,). These will be executed regardless of whether they are marked or not. For example, the following command will execute the "Vacía1" and "Vacía2" Configurations, even if "Vacía1" is not marked as shown in the previous figure: 

    To automate data loading, use the following syntax in your terminal or batch file:

".\Excel To Database.exe" /CFG="Vacía1, Vacía2"

Results Panel

It consists of a text box displaying various information to the user, a "Clear" button to clear the text box, and a "Back" button to return to the previous panel.

Since the standard Microsoft controls for displaying text have low performance, the popular text editor "Scintilla" (used, for example, in Notepad++) has been used. Unfortunately, integrating this control with the technology used in the user interface presents some problems. For example, it does not allow scrollbar customization, and integrating with transparencies results in a "weird" effect when starting the application.

In the following figure, the panel displays information related to the "PacketSize" parameter. This is a parameter belonging to the SQL Server connection strings, and it appears when a connection string of this type is established.

Results Panel

Results Panel. Displays information related to the parameter "PacketSize".

In the panel, typical accelerators work, such as Ctrl-A to select all text, or Ctrl-C to copy the selected text.

In addition to this panel, several "trace" files are generated:

  • Application Logs

    During the management and parameterization of Configurations, in addition to displaying information in the "Results Panel", two log files are also generated. These files are located in the same location as the application, have the ".log" extension, and start with the product name. For example, for Excel To Database the files "Excel To Database.log" and "Excel To Database_Error.log" are generated, while for Insert Bulk Bestia the files "Insert Bulk Bestia.log" and "Insert Bulk Bestia_Error.log" are generated.

    "Excel To Database.log" and "Insert Bulk Bestia.log" contain the information displayed on the screen. This is useful when running Configurations from the command line, as these files record the actions performed by the applications.

    "Excel To Database_Error.log" and "Insert Bulk Bestia_Error.log" contain the information from the previous files, but with more detailed error information when errors occur. Therefore, these files should be used for analyzing potential problems.

  • Configuration Logs

    During the execution of Configurations, other trace files are generated following the same philosophy. This way, the logs related to the application's operation are separated from the logs associated with the tasks scheduled in each Configuration.

    These files are also located in the same location as the application, have the ".log" extension, start with the product name, and end with the Configuration name. For example, for Excel To Database and the Configuration "Vacía3", the files "Excel To Database_Vacía3.log" and "Excel To Database_Error_Vacía3.log" will be generated.

    As with the Application Logs, the first file contains all the information shown in the "Results Panel", while the second file contains more detailed error information when errors occur.

About Panel

This panel displays information about the application.

About Panel

About Panel of Excel To Database.

Common Controls

  • File Selection

    This control, depending on the needs, will allow the selection of one or several files or a folder. The control consists of a text that defines the action: "File Selection", a button that launches the action: "Browse", and a text box where the selected folder or the folder containing the selected files is displayed.

    In the dialog box that appears for file selection, the extension of the files to be selected is indicated. For Excel To Database, it allows the selection of files with extensions "*.xlsx" (Excel Files) and "*.xlsm" (Excel Macro Files). However, there is also the possibility of removing this filter, allowing the selection of any file "*.*" (All Files).

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

    File Selection

    File Selection. Selection of Excel Books.

    Once the files are selected, and while the application is running, modifying the selected Excel Books may cause errors during reading the book's content.

  • Database Connection

    This control is a wizard for creating database connection strings. It consists of a text that defines the action: "Database", a button that launches the action: "Connection String Wizard", and a text box where the generated connection string is displayed.

    Depending on the type of license and the application, the allowed destinations for the connection strings will vary. Up to now, the allowed destinations are: Microsoft Access, SQL Server, and Oracle. In future versions of both applications, the possible allowed destinations may be expanded.

    For each of the destinations, it is possible to use several data providers. Data providers determine the behavior of the connections and the possibilities in handling the data. Additionally, in some cases, for each data provider, it is possible to choose from several drivers. This provides a wide range of flexibility and versatility.

    It should be noted that the behaviors of data providers and drivers are not identical, so thorough testing must be performed to find the connection string that best suits each need.

    Connection String: Data Providers

    Connection String: Selecting the Data Provider for the Database.

    • Microsoft Access

      The use of the .NET Data Provider for OLE DB is allowed. Excel To Database is a 64-bit application, so the permitted drivers are: Microsoft.ACE.OLEDB.12.0, Microsoft.ACE.OLEDB.15.0, and Microsoft.ACE.OLEDB.16.0 64-bit.

      If there is enough demand, it is possible that 32-bit versions of the applications will be generated, in which case, the allowed drivers will be: "Microsoft.ACE.OLEDB.12.0", "Microsoft.ACE.OLEDB.15.0" and "Microsoft.ACE.OLEDB.16.0" 32-bit, along with the driver included with the operating system "Microsoft.Jet.OLEDB.4.0" 32-bit for MDB-type databases.

      This information is expanded in the chapters dedicated to the tests performed with Excel To Database for MS Access: "MS Access Drivers".

    • ODBC

      Only the use of the .NET Data Provider for ODBC is allowed, which Microsoft already considers obsolete. The use of system or user data sources is not allowed because they do not store the password and, therefore, it will be requested every time the connection is attempted to be opened. However, it is possible to create a connection string from these data sources.

      Not all drivers are allowed. The following drivers are permitted: Microsoft Access Driver (*.mdb), Microsoft Access Driver (*.mdb, *.accdb), ODBC Driver 18 for SQL Server, ODBC Driver 17 for SQL Server, SQL Server, SQL Server Native Client 11.0, and Oracle ODBC Driver.

      The "Microsoft ODBC for Oracle" driver only works with 32-bit applications and with too obsolete Oracle clients. Unlike MS Access, where the 32-bit drivers have been tested, this driver has not been tested. If there is enough demand, it is possible that 32-bit versions of the applications will be generated and this driver integrated.

      For Excel To Database, in future versions, the use of flat files as a data source will be allowed through the use of the "Microsoft Text Driver (*.txt, *.csv)" drivers for 32-bit applications and the "Microsoft Access Text Driver (*.txt, *.csv)" drivers for 32 and 64-bit applications.

      For Insert Bulk Bestia, it is not allowed to use ODBC as a database destination. It is possible that in future versions it will be allowed as a data source.

      This information is expanded in the chapters dedicated to the tests performed with Excel To Database: "MS Access Drivers", "ODBC SQL Server Drivers", and "ODBC Oracle Drivers".

    • SQL Server

      .NET Data Provider for SQL Server is supported. This data provider uses System.Data.SqlClient and for later versions Microsoft.Data.SqlClient will also be available.

      Excel To Database, for SQL Server, allows the use of the data providers .NET Data Provider for ODBC and .NET Data Provider for OLE DB as the data destination. Insert Bulk Bestia, for SQL Server, it is possible that in future versions these data providers will be allowed as data sources, not as destinations.

      For .NET Data Provider for OLE DB the following drivers are allowed: Microsoft OLE DB Provider for SQL Server more commonly known as SQLOLEDB and SQL Server Native Client 11.0 (SQLNCLI), which Microsoft considers deprecated. The use of: Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL) and Microsoft OLE DB Driver 19 for SQL Server (MSOLEDBSQL19) is also allowed.

      For .NET Data Provider for ODBC the use of the drivers SQL Server Native Client 11.0, SQL Server, ODBC Driver 17 for SQL Server, and ODBC Driver 18 for SQL Server for 32 and 64 bits is allowed.

      This information is expanded in the chapters dedicated to the tests performed with Excel To Database for SQL Server: "SQL Server Drivers".

    • Oracle

      The use of Oracle Data Provider for .NET, Managed Driver is allowed.

      Excel To Database allows the use of the .NET Data Provider for ODBC with the Oracle ODBC Driver. With the .NET Data Provider for OLE DB, the use of the Oracle Provider for OLE DB driver is allowed.

      The "Microsoft ODBC for Oracle" and "Microsoft OLE DB Provider for Oracle" (MSDAORA) drivers only work with 32-bit applications and with too obsolete Oracle clients. Unlike MS Access, where the 32-bit drivers have been tested, these drivers have not been tested. If there is enough demand, it is possible that the corresponding 32-bit versions of the applications will be generated and these drivers will be integrated.

      Finally, note that it is necessary to be an Oracle engineer to install the 32-bit and 64-bit versions of these drivers on the same machine. It is incomprehensible how Oracle continues to use technology from 40 years ago for the light installation of these drivers: ODAC Xcopy Package + Instant Client ODBC package ("Oracle Instant Client" + "Oracle Provider for OLE DB" + "Oracle ODBC Driver"), and whose documentation is incomprehensible, indecipherable, insufferable, in..., in.... There is the "Oracle Universal Installer" (OUI), but the "noise" it generates makes it not the indicated option for production machines (in our case, 2010 machines with an i7 920).

      This information is expanded in the chapters dedicated to the tests performed with Excel To Database for Oracle: "Oracle Drivers".

    • OLE DB

      For the .NET Data Provider for OLE DB, the allowed drivers have already been described in the previous points.

    Connection String: Test Connection String

    Connection String: Configuration and testing the Connection String.

    Connection strings are stored in the JSON file UNENCRYPTED, which means their security is compromised since the password is stored in them. We recommend using users with restricted permissions (Least Privilege) in the database while encryption is implemented in future versions.

    Both Insert Bulk Bestia and Excel To Database impose certain properties on the connection string to ensure they function correctly. For example, for connection strings to SQL Server that use ".NET Data Provider for SQL Server," the following modifiers are added: 

    Asynchronous Processing=True;MultipleActiveResultSets=True;Application Name="Excel To Database";Workstation ID=MaquinaOrigen;ApplicationIntent=ReadWrite.

Frequently Asked Questions

Can I run multiple configurations at the same time?

Yes, checked configurations run consecutively, ensuring no process collisions within the database.

Where are my configurations stored?

They are stored in a JSON file within the application's root folder, making portability, backups easy and allowing manual edits if necessary.

How to automate Excel To Database or Insert Bulk Bestia with a scheduled task?

Use the /CFG="ConfigName" modifier in the command line to launch automated tasks without human intervention.

What happens if the CLI execution encounters an error?

The MalaCharger engine generates a detailed error log and halts execution to protect database integrity, returning an exit code that can be captured by monitoring scripts.