• • •

Default/Null Values

💡 Architecture Tip: By centralizing null management in the App, you eliminate the risk of erratic behavior between different versions of database drivers.

Excel To Database is engineered to abstract the specificities of every database engine. While traditional ETL processes often fail due to how different drivers (SQLClient, Oracle Data Provider, etc.) interpret null values or empty cells, our software unifies this behavior. By managing Default Values at the application level, we ensure business logic remains intact regardless of the destination. This enables total portability of your configurations: what works for SQL Server will work exactly the same for Oracle, without requiring any further adjustments.

This ensures that an empty cell behaves exactly the same in SQL Server, Oracle, or MS Access, eliminating unexpected behaviors during migration. This column is used for this purpose. The value entered here will be used in the following cases:

When a cell is empty, it will be interpreted as NULL. Thus, if the value 'NULL' is entered in this column, those empty cells will be loaded as 'NULL'. However, if another value is entered, that value will be loaded into the database when the cell is empty. This is called the 'Default Value'.
For NOT NULL fields, the application requires a replacement value. This acts as a pre-validation filter, preventing load processes from failing mid-execution and ensuring data always complies with the target schema.
There is an error in the cell and the No Values Error option is checked.
There is a data conversion error and the No Conversion Errors option is checked.

To enter the value, you must select the content of the TextBox and enter the new value.

There are a couple of "tags", "Nombre Fichero" and/or "File Name", that when included in this field will be replaced by the file name, i.e., the file name from which the data is being read will be used as the default value for that field.

Another pair of "tags", "Ahora" and/or "Now", allow using the current system date and time when data loading starts as the default value.

Where possible (suitable data provider and driver), the default value defined in the database for columns that have it will be displayed. However, this data obtained from the database will most likely need to be manually modified, as the expressions indicated in the database language may not be correctly interpreted by .NET.

It is highly recommended to properly adjust these default values to align with the desired behavior.

MS Access: Default/Null Values

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.

To demonstrate the behavior of this option with MS Access, the table creation script shown below and the Excel Sheet in the following figure are used:

 Option Compare Database
Option Explicit

Public Sub CreaTablaValoresDefecto()
On Error Resume Next

  CurrentProject.Connection.Execute "DROP TABLE [Tabla Valores Defecto]"

On Error GoTo Sub_Err

  CurrentProject.Connection.Execute "CREATE TABLE [Tabla Valores Defecto](" & _
    "[Id] COUNTER NOT NULL, " & _
    "[Proveedor Datos] TEXT (255) NOT NULL, " & _
    "[Driver] TEXT (255) NOT NULL, " & _
    "[Texto 5 N Null N Empty Def Val] TEXT (5) NOT NULL, " & _
    "[Byte N Null Def Val] BYTE NOT NULL, " & _
    "[Fecha/Hora N Null Def Val] DATETIME NOT NULL, " & _
    "[Sí/No Def True] YESNO" & _
  ")"

  Dim db As Object, tbl As Object, fld As Object

  Set db = CurrentDb
  db.TableDefs.Refresh
  Set tbl = db.TableDefs("Tabla Valores Defecto")
  tbl.Fields.Refresh
  Set fld = tbl.Fields("Proveedor Datos")
  fld.AllowZeroLength = False
  fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
  Set fld = tbl.Fields("Driver")
  fld.AllowZeroLength = False
  fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)

  Set fld = tbl.Fields("Texto 5 N Null N Empty Def Val")
  fld.Properties.Append fld.CreateProperty("UnicodeCompression", 1, True)
  fld.AllowZeroLength = False
  fld.DefaultValue = "V. D."
  Set fld = tbl.Fields("Byte N Null Def Val")
  fld.DefaultValue = "255"
  Set fld = tbl.Fields("Fecha/Hora N Null Def Val")
  fld.DefaultValue = "=CDate(""15/01/2023"")"
  Set fld = tbl.Fields("Sí/No Def True")
  fld.DefaultValue = "Yes"

  CurrentProject.Connection.Execute "CREATE UNIQUE INDEX PrimaryKey ON [Tabla Valores Defecto] (Id ASC) WITH PRIMARY"

Sub_Exit:
  Set fld = Nothing
  Set tbl = Nothing
  If Not db Is Nothing Then db.Close
  Set db = Nothing
  Exit Sub
  
Sub_Err:
  MsgBox "Se ha producido un error en 'CreaTablaValoresDefecto'." & vbNewLine & vbNewLine & vbTab & Err.Description, , "Error Nº: " & Err.Number
  Resume Sub_Exit
  Resume
End Sub

MS Access: Excel Sheet for [Tabla Valores Defecto]

It should be noted that this data (the default value of a column defined in the MS Access database) can be obtained with OLE DB connections, but NOT with ODBC connections, since the data provider does not offer this possibility for the latter. Thus, the following figure shows the "Mapping Panel" for an OLE DB connection.

MS Access: Mapping Panel with Default/Null Values

MS Access: Default/Null Values. Fields that do not accept Null (NOT NULL)

When a field is required (for example: "[Byte N Null Def Val] BYTE NOT NULL") and this value is not defined for that field (NULL instead of 255 as shown in the previous figure), an error will occur during the data loading phase. The error shown in the _Error.log files will be similar to the following:

 OLE DB Connections
Row: 1
Command: INSERT INTO [Tabla Valores Defecto] ([Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos]) VALUES (?, ?, ?, ?, ?, ?)
Select Command: SELECT [Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos] FROM [Tabla Valores Defecto]
Exception:
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147467259,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
        "NativeError": -541331061,
        "Source": "Microsoft Access Database Engine",
        "SQLState": "3314"
      }
    ]
  },
  "Message": "You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft Access Database Engine",
  "HResult": -2147467259
}

    Column: Sí/No Def True    Value: True
    Column: Fecha/Hora N Null Def Val    Value: 01/15/2023 00:00:00
    Column: Byte N Null Def Val    Value: 
    Column: Texto 5 N Null N Empty Def Val    Value: V. D.
    Column: Driver    Value: Microsoft.ACE.OLEDB.16.0
    Column: Proveedor Datos    Value: OLE DB
An exception occurred attempting to load Table 'Tabla Valores Defecto'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT [Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos] FROM [Tabla Valores Defecto]
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147467259,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
        "NativeError": -541331061,
        "Source": "Microsoft Access Database Engine",
        "SQLState": "3314"
      }
    ]
  },
  "Message": "You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft Access Database Engine",
  "HResult": -2147467259
}

Number of rows inserted: 0



ODBC Connections
Row: 1
Command: INSERT INTO [Tabla Valores Defecto] ([Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos]) VALUES (?, ?, ?, ?, ?, ?)
Select Command: SELECT [Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos] FROM [Tabla Valores Defecto]
Exception:
{
  "$id": "1",
  "$type": "System.Data.Odbc.OdbcException, System.Data",
  "Errors": {
    "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC Microsoft Access Driver] You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
        "SQLState": "HY000",
        "NativeError": -3701,
        "Source": "ACEODBC.DLL"
      }
    ]
  },
  "Source": "ACEODBC.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: Sí/No Def True    Value: True
    Column: Fecha/Hora N Null Def Val    Value: 
    Column: Byte N Null Def Val    Value: 
    Column: Texto 5 N Null N Empty Def Val    Value: V. D.
    Column: Driver    Value: {Microsoft Access Driver (*.mdb, *.accdb)}
    Column: Proveedor Datos    Value: ODBC
An exception occurred attempting to load Table 'Tabla Valores Defecto'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT [Sí/No Def True], [Fecha/Hora N Null Def Val], [Byte N Null Def Val], [Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos] FROM [Tabla Valores Defecto]
{
  "$id": "1",
  "$type": "System.Data.Odbc.OdbcException, System.Data",
  "Errors": {
    "$type": "System.Data.Odbc.OdbcErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC Microsoft Access Driver] You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
        "SQLState": "HY000",
        "NativeError": -3701,
        "Source": "ACEODBC.DLL"
      }
    ]
  },
  "Source": "ACEODBC.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Microsoft][ODBC Microsoft Access Driver] You must enter a value in the 'Tabla Valores Defecto.Byte N Null Def Val' field.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

Number of rows inserted: 0

MS Access: Default/Null Values. Default values and .NET

The default values for text fields may be enclosed in double or single quotes, which are added by the OLE DB driver for MS Access. This means, for example, that the field "Texto 5 N Null N Empty Def Val" is assigned the default value "V. D." with double quotes included. The solution is obvious: remove the double quotes.
Note that if the option Truncate Strings is checked, it will truncate the default value, resulting in '"V. D' (single quotes not included). This is reported by the application with the following message:

 Cell: , Value: "V. D.", Table: Tabla Valores Defecto, Column: Texto 5 N Null N Empty Def Val, Max number of characters allowed: 5
    "Truncate Strings" option checked. Truncated Value: "V. D

For the field "Date/Time N Null Def Val" the default value is obtained as "=CDate("15/01/2023")", which is not a value or expression that .NET can correctly interpret. The solution, in this case, would be to enter the date directly as the default value: 15/01/2023.

The field "Yes/No Def True" in MS Access (Spanish) has the default value "Sí" ("Yes" in English). OLE DB connections retrieve this value, and during the configuration check, an error occurs because .NET cannot correctly interpret this data.

 Result Panel

        Sheet 'MS Access - Val Def-Val Null': Checking...

Cell: , Value: Yes, Table: Tabla Valores Defecto, Column: Sí/No Def True
    Cell content could not be converted to data type Boolean

The default value 'Yes' indicated for Column 'Sí/No Def True'
cannot be cast to the data type of the column.

String was not recognized as a valid Boolean.

        Sheet 'MS Access - Val Def-Val Null': Check Finished. With modifications or failures.

"Excel To Database_Error_MS_Access_OLE_DB.log" File

        Sheet 'MS Access - Val Def-Val Null': Checking...

Cell: , Value: Yes, Table: Tabla Valores Defecto, Column: Sí/No Def True
    Cell content could not be converted to data type Boolean

The default value 'Yes' indicated for Column 'Sí/No Def True'
cannot be cast to the data type of the column.

{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "String was not recognized as a valid Boolean.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "mscorlib",
  "HResult": -2146233033
}

        Sheet 'MS Access - Val Def-Val Null': Check Finished. With modifications or failures.

The problem, in this case, is resolved by replacing the value "Yes" with "true", as .NET can process this value.

MS Access: Default/Null Values. Boolean Fields

A special case is the behavior of Yes/No (Boolean) fields in MS Access. This data type is always required, meaning it does not accept NULL. In ODBC connections, it is necessary to define a default value; otherwise, the configuration check phase will end with an error and the following message will be displayed:
ODBC Connections Sheet 'MS Access - Val Def-Val Null': Checking... Column "Sí/No Def True" of table "Tabla Valores Defecto" does not allow null values. Please provide a non-null default value for the column Sheet 'MS Access - Val Def-Val Null': Check Finished. With modifications or failures.
However, OLE DB connections have a completely different behavior. When NULL is defined as the default value, for some strange reason, that NULL is replaced by the default value that .NET variables accept for the "bool" data type: false. And this is the value that will be inserted into the field, even though the database field has "true" defined as the default value.

SQL Server: Default/Null Values

To show the behavior of this option with SQL Server, the table defined earlier in the Sheet Load Options section is used. The Excel Workbook indicated in that section, which contains the Sheet shown in the following figure, is also used in this section.

SQL Server: Excel Sheet for [Tabla Interfaz Usuario]

The default values defined in the database are obtained with any driver allowed in Excel To Database since it is precisely the application that obtains them, not the drivers.

SQL Server: Mapping Panel with Default/Null Values

SQL Server: Default/Null Values. Fields that do not accept Null (NOT NULL)

When a field is required (for example: "[Char5 N Null] [char](5) COLLATE Modern_Spanish_100_CS_AS NOT NULL" and this value is not defined for the field, an error will occur during the configuration check phase. The error shown both on screen and in the _Error.log files will be similar to the following:
Column "Char5 N Null" of table "dbo.Tabla Interfaz Usuario" does not allow null values. Please provide a non-null default value for the column

SQL Server: Default/Null Values. Default values and .NET

In the Mappings Panel, it is observed that the default values are enclosed in parentheses and, in the case of character strings, also include single quotes. This causes, for example, the field "Char5 N Null Def Val" to be assigned the default value "('V. D.')" without double quotes, thus exceeding the maximum field length of 5 characters. The solution is clear: remove the parentheses and single quotes.
If the option Truncate Strings is checked, it will truncate the default value, leaving it as "('V. " (double quotes not included).

Again, note that the default value obtained from the database will likely need to be manually modified, as expressions specified in the database language may not be correctly interpreted by .NET. For example, for the "DateTime" field, the value "(getdate())" (without double quotes) is obtained. The following figure shows this and indicates the formats accepted by the application for dates (which are usually dependent on the system configuration).

SQL Server: Error converting a date

After completing the Configuration check phase, an error will occur during the data loading phase when attempting to load a NULL into that column. The _Error.log files will contain messages similar to the following:

 .NET Connections (System.Data.SqlClient)

An exception occurred attempting to load Table 'dbo.Tabla Interfaz Usuario'.
     Query that indicates the used columns of the Table:
     SELECT [DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5] FROM [dbo].[Tabla Interfaz Usuario]
{
  "$id": "1",
  "$type": "System.InvalidOperationException, mscorlib",
  "Message": "The given value of type String from the data source cannot be converted to type datetime of the specified target column.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.FormatException, mscorlib",
    "Message": "Failed to convert parameter value from a String to a DateTime.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": {
      "$type": "System.FormatException, mscorlib",
      "Message": "String was not recognized as a valid DateTime. There is an unknown word starting at index 0.",
      "Data": {
        "$type": "System.Collections.ListDictionaryInternal, mscorlib"
      },
      "InnerException": null,
      "HelpLink": null,
      "Source": "mscorlib",
      "HResult": -2146233033
    },
    "HelpLink": null,
    "Source": "System.Data",
    "HResult": -2146233033
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233079
}


OLE DB Connections (MSOLEDBSQL19)

Row: 1
Command: INSERT INTO [RepositorioLicencias5].[dbo].[Tabla Interfaz Usuario] ([DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5]) VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT [DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5] FROM [dbo].[Tabla Interfaz Usuario]
Exception:
{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "Failed to convert parameter value from a String to a DateTime.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.FormatException, mscorlib",
    "Message": "String was not recognized as a valid DateTime.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "mscorlib",
    "HResult": -2146233033
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233033
}

    Column: DateTime    Value: (getdate())
    Column: SmallMoney    Value: 
    Column: SmallInt    Value: 
    Column: Char5UTF8    Value: 
    Column: Char5 N Null Def Val    Value: ('V. 
    Column: Char5 N Null    Value: No Nl
    Column: Char5    Value: a
An exception occurred attempting to load Table 'dbo.Tabla Interfaz Usuario'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT [DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5] FROM [dbo].[Tabla Interfaz Usuario]
{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "Failed to convert parameter value from a String to a DateTime.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.FormatException, mscorlib",
    "Message": "String was not recognized as a valid DateTime.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "mscorlib",
    "HResult": -2146233033
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233033
}

Number of rows inserted: 0


ODBC Connections ({ODBC Driver 18 for SQL Server})

Row: 1
Command: INSERT INTO [RepositorioLicencias5].[dbo].[Tabla Interfaz Usuario] ([DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5]) VALUES (?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT [DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5] FROM [dbo].[Tabla Interfaz Usuario]
Exception:
{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "Failed to convert parameter value from a String to a DateTime.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.FormatException, mscorlib",
    "Message": "String was not recognized as a valid DateTime.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "mscorlib",
    "HResult": -2146233033
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233033
}

    Column: DateTime    Value: (getdate())
    Column: SmallMoney    Value: 
    Column: SmallInt    Value: 
    Column: Char5UTF8    Value: 
    Column: Char5 N Null Def Val    Value: ('V. 
    Column: Char5 N Null    Value: No Nl
    Column: Char5    Value: a
An exception occurred attempting to load Table 'dbo.Tabla Interfaz Usuario'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT [DateTime], [SmallMoney], [SmallInt], [Char5UTF8], [Char5 N Null Def Val], [Char5 N Null], [Char5] FROM [dbo].[Tabla Interfaz Usuario]
{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "Failed to convert parameter value from a String to a DateTime.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.FormatException, mscorlib",
    "Message": "String was not recognized as a valid DateTime.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "mscorlib",
    "HResult": -2146233033
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233033
}

Number of rows inserted: 0

In this case, it is suggested to set the default value to the keyword "Ahora" or "Now," which inserts the date and time when the data loading starts, ensuring that all records inserted into the table using this default value will have the same timestamp. This decision was made instead of inserting the date and time of each record's insertion (which is what the database actually does) because it is desired to mark these records as inserted together.

It is strongly recommended to properly adjust these default values to match the desired behavior.

Finally, note that unlike MS Access, the behavior of Booleans (Bit) for default values is the same as for other numeric fields.

Oracle: Default/Null Values

To demonstrate the behavior of this option with Oracle, the following table is used:

 CREATE TABLE "Tabla Valores Defecto" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider                      VARCHAR2(10 CHAR) NOT NULL,
  Driver                        VARCHAR2(50 CHAR) NOT NULL,

  "Char 5"                      CHAR(5 CHAR),
  "Char 5 N Null"               CHAR(5 CHAR) NOT NULL,
  "Char 5 Val Def"              CHAR(5 CHAR) DEFAULT 'V.1D.',
  --"Char 5 Val Def"              CHAR(5 CHAR) NOT NULL DEFAULT 'V. D.',
  "Char 5 Val Def On Null"      CHAR(5 CHAR) DEFAULT ON NULL 'V. D.',
  "Int 2 N Null"                NUMBER(2) NOT NULL,
  "Int 2 Val Def"               NUMBER(2) DEFAULT -1,
  "Int 2 Val Def On Null"       NUMBER(2) DEFAULT ON NULL -1,
  "Date N Null"                 DATE NOT NULL,
  "Date Val Def"                DATE DEFAULT sysdate,
  "Date Val Def On Null"        DATE DEFAULT ON NULL date'0001-01-01'
);

It should be noted that in Oracle 21, the declaration of fields with the syntax: "Char 5 Val Def" CHAR(5 CHAR) NOT NULL DEFAULT 'V.1D.' is not supported.

The Excel Workbook mentioned in this section is also used, which contains the Sheet shown in the following figure used in this section.

Oracle: Excel Sheet for [Tabla Valores Defecto]

The default values defined in the database are obtained with any of the drivers allowed in Excel To Database as it is the application itself that retrieves them, not the drivers.

Oracle: Mapping Panel with Default/NULL Values

Oracle: Default/Null Values. Fields that do not allow Null (NOT NULL)

When a field is required (for example: ""Char 5 N Null" CHAR(5 CHAR) NOT NULL") and this value is not defined for that field, an error will occur during the Configuration verification phase. The error shown both on screen and in the _Error.log files will be similar to the following:
Column "Char 5 N Null" of table "Tabla Valores Defecto" does not allow null values. Please provide a non-null default value for the column

Oracle: Default/Null Values. Default values and .NET

In the Mappings Panel, it is observed that some default values are enclosed in single quotes. This means, for example, that for the field "Char 5 Val Def" the default value assigned is "'V.1D.'", without the double quotes, and, therefore, exceeds the maximum field length of 5 characters. The solution is obvious: remove the single quotes.
If the option Truncate Strings is checked, it will truncate the default value, leaving it as "('V.1" (double quotes not included).

Again, it is important to highlight that the default value obtained from the database is very likely to need manual modification, as the expressions indicated in the database language may not be correctly interpreted by .NET. Thus, for the field "Date Val Def" the value obtained is "sysdate" (without double quotes). As with SQL Server, during the Configuration verification phase an error will occur and the formats accepted by the application for dates (which are often dependent on the system configuration) will be indicated. In the _Error.log files, messages similar to the following will appear:

 .NET Connections (Managed Driver (Bulk Copy) (CheckBox "Use Arrays" unchecked))

        Sheet 'Oracle - Val Def-Val Null': Checking...

Cell: , Value: sysdate, Table: Tabla Valores Defecto, Column: Date Val Def
    Cell content could not be converted to data type DATE

For dates formatted as text, these formats are supported:
dd/MM/yyyy
dd/MM/yy
d/MM/yy
.....
MMMM dd
hh:mm tt
h:mm tt
yyyy MMMM

An exception occurred while checking whether the Excel Sheet 'Oracle - Val Def-Val Null' has undergone changes that affect data loading.
{
  "$id": "1",
  "$type": "System.ArithmeticException, mscorlib",
  "Message": "Cell: , Value: sysdate, Table: Tabla Valores Defecto, Column: Date Val Def
    The cell content could not be converted to a date.
    Oracle's Managed Driver with NO Arrays contains a bug that allows data to be inserted that does not comply with the column restrictions.
    Check the \"No Conversion Errors\" option to load the default value.
    The Data Load Phase will not be executed.
",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "ExcelToDatabaseWpf",
  "HResult": -2147024362
}

        Sheet 'Oracle - Val Def-Val Null': Check Finished. With modifications or failures.

.NET Connections (Managed Driver (Array Binding) (CheckBox "Use Arrays" checked))

        Sheet 'Oracle - Val Def-Val Null': Checking...

Cell: , Value: sysdate, Table: Tabla Valores Defecto, Column: Date Val Def
    Cell content could not be converted to data type DATE

For dates formatted as text, these formats are supported:
dd/MM/yyyy
dd/MM/yy
d/MM/yy
.....
MMMM dd
hh:mm tt
h:mm tt
yyyy MMMM

The default value 'sysdate' indicated for Column 'Date Val Def'
cannot be cast to the data type of the column.

{
  "$id": "1",
  "$type": "System.ArgumentException, mscorlib",
  "Message": "The DefaultValue for column Date Val Def is of type System.String, but the column is of type Oracle.ManagedDataAccess.Types.OracleDate.",
  "ParamName": null,
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.InvalidCastException, mscorlib",
    "Message": "Invalid cast from 'System.String' to 'Oracle.ManagedDataAccess.Types.OracleDate'.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "mscorlib",
    "HResult": -2147467262
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2147024809
}

        Sheet 'Oracle - Val Def-Val Null': Check Finished. With modifications or failures.

OLE DB (Oracle® Provider for OLE DB) and ODBC (Oracle ODBC Driver) Connections

        Sheet 'Oracle - Val Def-Val Null': Checking...

Cell: , Value: sysdate, Table: Tabla Valores Defecto, Column: Date Val Def
    Cell content could not be converted to data type DATE

For dates formatted as text, these formats are supported:
dd/MM/yyyy
dd/MM/yy
d/MM/yy
.....
MMMM dd
hh:mm tt
h:mm tt
yyyy MMMM

In this case, it is proposed to use the keyword "Now" as the default value, which inserts the date and time when the data load begins, so that all records inserted into the table using this default value will have the same data. This decision was made instead of inserting the date and time when each record is inserted (which is what the database actually does) because it is desired that these records be marked as having been inserted together.

It is strongly recommended to properly adjust these default values to match the desired behavior.

Frequently Asked Questions

How does Excel To Database handle empty cells?

By default, it interprets them as NULL, but it allows you to define a custom substitution value that the software injects before sending the data to the database driver.

Why is a default value mandatory for NOT NULL fields?

To ensure process robustness. By detecting this during the validation phase, the software prevents the database from rejecting the bulk load, saving time and avoiding integrity issues.

How does this affect bulk load performance?

Processing occurs in-memory during the mapping phase, adding negligible latency while ensuring the driver receives pre-validated data, thus avoiding costly rollbacks or mid-process insertion errors.