• • •

Rounding Integer

This CheckBox appears in those database columns that have an integer data type. By checking this option, numeric data with decimals will be rounded to an integer during the data reading phase.

To ensure predictable results, Excel To Database implements the MidpointRounding.AwayFromZero standard. See MidpointRounding Enumeration.
This ensures that a value like 2.5 is always converted to 3, regardless of whether the database driver uses banker's rounding or truncation.

When attempting to load a non-integer number into an integer field and this option is not checked nor the option No Conversion Errors is checked, a conversion error occurs. This is because the data cannot be converted to the desired data type, and therefore the cell content is attempted to be loaded as a string.
The message that Excel To Database displays will be similar to the following:

 Cell: A3, Value: 2.1, Table: Tabla Redondeo Enteros, Column: Byte
    Cell content could not be converted to data type Byte
Cell: A3, Value: 2.1, Table: Tabla Redondeo Enteros, Column: Byte
     Input string was not in a correct format.Couldn't store "2.1" in Byte Column.  Expected type is Byte.

In these cases, an exception occurs that prevents the data loading phase from starting. The solution to this problem is to check the option No Conversion Errors.

The software also acts as an intelligent filter: if the rounding exceeds the physical capacity of the data type (e.g., a Byte field: rounding 255.5 produces an output of 256, which exceeds the field's capacity), a controlled warning is generated. We recommend using the No Conversion Errors option to handle these cases using nulls or default values.

MS Access: Rounding Integer

To demonstrate the behavior of this option with MS Access, the Database from the previous section containing the following table is used, along with the Excel Workbook from the previous section. The Sheet shown in the following figure will be used from the Excel Workbook:

 Option Compare Database
Option Explicit

Public Sub CreaTablaRedondeoEnteros()
On Error Resume Next

  CurrentProject.Connection.Execute "DROP TABLE [Tabla Redondeo Enteros]"

On Error GoTo Sub_Err

  CurrentProject.Connection.Execute "CREATE TABLE [Tabla Redondeo Enteros](" & _
    "[Id] COUNTER NOT NULL, " & _
    "[Proveedor Datos] TEXT (255) NOT NULL, " & _
    "[Driver] TEXT (255) NOT NULL, " & _
    "[Byte] BYTE, " & _
    "[Entero] SHORT, " & _
    "[Entero Largo] LONG " & _
  ")"

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

  Set db = CurrentDb
  db.TableDefs.Refresh
  Set tbl = db.TableDefs("Tabla Redondeo Enteros")
  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("Byte")
  fld.Properties.Append fld.CreateProperty("Description", 10, "1 Bytes")
  Set fld = tbl.Fields("Entero")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre -32.768 y 32.767 - 2 Bytes")
  Set fld = tbl.Fields("Entero Largo")
  fld.Properties.Append fld.CreateProperty("Description", 10, "Entre -2.147.483.648 y 2.147.483.647 - 4 Bytes")

  CurrentProject.Connection.Execute "CREATE UNIQUE INDEX PrimaryKey ON [Tabla Redondeo Enteros] (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 'CreaTablaRedondeoEnteros'." & vbNewLine & vbNewLine & vbTab & Err.Description, , "Error Nº: " & Err.Number
  Resume Sub_Exit
  Resume
End Sub

MS Access: Excel Sheet for Rounding Integer

The messages displayed by Excel To Database when attempting to load the Sheet are as follows (options "Integer Rounding" checked and "No Conversion Errors" not checked):

 Cell: A3, Value: 2.1, Table: Tabla Redondeo Enteros, Column: Byte
     "Integer Rounding" option checked. Rounded Value: 2

Cell: B3, Value: 2.49, Table: Tabla Redondeo Enteros, Column: Entero
     "Integer Rounding" option checked. Rounded Value: 2

Cell: C3, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Entero Largo
     "Integer Rounding" option checked. Rounded Value: 3

Cell: A4, Value: 2.4599999999995100, Table: Tabla Redondeo Enteros, Column: Byte
     "Integer Rounding" option checked. Rounded Value: 2

Cell: B4, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Entero
     "Integer Rounding" option checked. Rounded Value: 3

Cell: C4, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Entero Largo
     "Integer Rounding" option checked. Rounded Value: 3

Cell: A6, Value: 255.5, Table: Tabla Redondeo Enteros, Column: Byte
    Cell content could not be converted to data type Byte
Cell: A6, Value: 255.5, Table: Tabla Redondeo Enteros, Column: Byte
     Input string was not in a correct format.Couldn't store '255.5' in Byte Column.  Expected type is Byte.

Cell: B6, Value: 32767.5, Table: Tabla Redondeo Enteros, Column: Entero
    Cell content could not be converted to data type Int16
Cell: B6, Value: 32767.5, Table: Tabla Redondeo Enteros, Column: Entero
     Input string was not in a correct format.Couldn't store '32767.5' in Entero Column.  Expected type is Int16.

Cell: C6, Value: 2147483647.5, Table: Tabla Redondeo Enteros, Column: Entero Largo
    Cell content could not be converted to data type Int32
Cell: C6, Value: 2147483647.5, Table: Tabla Redondeo Enteros, Column: Entero Largo
     Input string was not in a correct format.Couldn't store '2147483647.5' in Entero Largo Column.  Expected type is Int32.

Note that 2.49 is rounded to 2, while 2.5 is rounded to 3. You can also observe the data type conversion errors when the rounding causes the field capacity to overflow.

The data loading phase does not start due to data exceeding the field's capacity. By checking the "No Conversion Errors" option, these data will be replaced by NULL or the default value, and the data loading phase will proceed.

Excel To Database considers the following to be integer data types: Byte, Integer, and Long Integer.

SQL Server: Rounding Integer

Excel To Database considers the following to be integer data types: Tinyint, Int, and Bigint. Although the Decimal/Numeric data types may be specified with a scale of 0 digits, they are not considered as integers. The "Decimal Rounding" option described in the next section must be applied to them. The Bit data type is also not considered as an integer.

The behavior of Excel To Database with SQL Server for this option is the same as described for MS Access.

To demonstrate the behavior of this option with SQL Server, the table described below and the Excel Workbook from the previous section are used. The Sheet shown in the following figure will be used from the Excel Workbook:

 DROP TABLE IF EXISTS [dbo].[Tabla Redondeo Enteros];
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tabla Redondeo Enteros](
  [Bit]                     [bit]           NULL,
  [Tinyint]                 [tinyint]       NULL,
  [Int]                     [int]           NULL,
  [BigInt]                  [bigint]        NULL,

  [Provider]                [varchar](10) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [Driver]                  [varchar](50) COLLATE Modern_Spanish_100_CS_AS NOT NULL,
  [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

SQL Server: Excel Sheet for Rounding Integer

Oracle: Rounding Integer

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

 CREATE TABLE "Tabla Redondeo Enteros" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider               VARCHAR2(10 CHAR),
  Driver                 VARCHAR2(50 CHAR),

  "Int 2"                NUMBER(2),
  "Redondeo 100 (5, -2)" NUMBER(5, -2)
);

The Workbook to be used is the Workbook from the previous section, which contains the Sheet shown in the following figure:

Oracle: Excel Sheet for Rounding Integer

First, note that only with the Managed Driver are fields with integer data types detected. With the Oracle® Provider for OLE DB and Oracle ODBC Driver drivers, the detected data type is decimal. Therefore, this option will only appear with the Managed Driver.

Bugs in Managed Driver (Bulk Copy) (CheckBox "Use Arrays" not checked)

It is worth highlighting again that with the Managed Driver and the Bulk Copy feature (option "Use Arrays" unchecked), there is a problem similar to the one described in the section "Truncate Strings". The driver allows the insertion of ANYTHING without needing to meet any of the restrictions defined in the table fields.
Thus, for example, if the checks performed by Excel To Database are not carried out, the following load would occur:
Oracle: Rounding Integer Load Error

As can be seen, there are records for the "Int 2" field that greatly exceed the restriction of not exceeding 2 digits. This can cause the table to end up in an inconsistent state and, for example, cause the instruction 'SELECT * FROM "Integer Round Table" ORDER BY ID;' to not work in "Oracle SQL Developer" as it happened in the previous section.
Excel To Database includes a series of checks to prevent such things from happening. Thus, in the "Results Panel", messages of the following style are shown:
Cell: A7, Value: 99.6, Table: Tabla Redondeo Enteros, Column: Int 2 "Integer Rounding" option checked. Rounded Value: 100 Cell: A7, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2 The number of digits '3' exceeds the allowed '2' 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.

Behavior of the "Rounding Integer" Option

When this option is not checked, the Data Load phase begins. Thus, when a data value cannot be converted to the desired data type, the text string stored in the cell of the Sheet is attempted to be loaded. It is most likely that a data type conversion error will occur, and the Data Load phase will stop.
The messages displayed for OLE DB and ODBC data providers are completely different from those for the Managed Driver, as these providers don't detect that the fields store integer data, but rather "decimal" data with precision and scale. However, these messages are displayed for comparison with the Managed Driver, which does detect them.

 Cell: B2, Value: 12345, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 5 is lower Abs(Scale) 2
    The Data Provider or the Database may round the data.

Cell: A3, Value: 2.49, Table: Tabla Redondeo Enteros, Column: Int 2
    Cell content could not be converted to data type Int16

Cell: B3, Value: 123456, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 6 is lower Abs(Scale) 2
    The Data Provider or the Database may round the data.

Cell: A4, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Int 2
    Cell content could not be converted to data type Int16

Cell: B4, Value: 1234567, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 7 is lower Abs(Scale) 2
    The Data Provider or the Database may round the data.

Cell: A5, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2
    The number of digits '3' exceeds the allowed '2'

Cell: B5, Value: 12345678, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 8 is lower Abs(Scale) 2
    The Data Provider or the Database may round the data.

Cell: A6, Value: 99.1, Table: Tabla Redondeo Enteros, Column: Int 2
    Cell content could not be converted to data type Int16

Cell: B6, Value: 49.5, Table: Tabla Redondeo Enteros, Column: Redondeo 100
    Cell content could not be converted to data type Int64

Cell: A7, Value: 99.6, Table: Tabla Redondeo Enteros, Column: Int 2
    Cell content could not be converted to data type Int16

Cell: B7, Value: 49.4, Table: Tabla Redondeo Enteros, Column: Redondeo 100
    Cell content could not be converted to data type Int64

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

An exception occurred attempting to load Table 'Tabla Redondeo Enteros'.
    Insert command: insert into "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") values(:0, :1, :2, :3)
{
  "$id": "1",
  "$type": "System.FormatException, mscorlib",
  "Message": "Input string was not in a correct format.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "mscorlib",
  "HResult": -2146233033
}

OLE DB (Oracle® Provider for OLE DB) Connections

Row: 4
Command: INSERT INTO "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?)
Select Command: SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
Exception:
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01438: value larger than specified precision allowed for this column",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01438: value larger than specified precision allowed for this column",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217833
}

    Column: Redondeo 100    Value: 12345678
    Column: Int 2    Value: 100
    Column: DRIVER    Value: OraOLEDB.Oracle
    Column: PROVIDER    Value: OLE DB
An exception occurred attempting to load Table 'Tabla Redondeo Enteros'. Row Error: 4
     Query that indicates the used columns of the Table:
     SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01438: value larger than specified precision allowed for this column",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01438: value larger than specified precision allowed for this column",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217833
}

Number of rows inserted: 0

ODBC (Oracle ODBC Driver) Connections

Row: 4
Command: INSERT INTO "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?)
Select Command: SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
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": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
        "SQLState": "HY000",
        "NativeError": 1438,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: Redondeo 100    Value: 12345678
    Column: Int 2    Value: 100
    Column: DRIVER    Value: {Oracle en OraDB21Home1}
    Column: PROVIDER    Value: ODBC
An exception occurred attempting to load Table 'Tabla Redondeo Enteros'. Row Error: 4
     Query that indicates the used columns of the Table:
     SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
{
  "$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": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
        "SQLState": "HY000",
        "NativeError": 1438,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

Number of rows inserted: 0

When the "Integer Rounding" option is selected, rounding may cause the field capacity to be exceeded, resulting in the typical field capacity overflow error. The messages that appear in the .log files will be similar to the following.
The messages for OLE DB or ODBC providers are similar to the above, except that the data has now been rounded.

 Cell: B2, Value: 12345, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 12300

Cell: A3, Value: 2.49, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 2

Cell: B3, Value: 123456, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 123500

Cell: A4, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 3

Cell: B4, Value: 1234567, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 1234600

Cell: A5, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2
    The number of digits '3' exceeds the allowed '2'

Cell: B5, Value: 12345678, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 12345700

Cell: A6, Value: 99.1, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 99

Cell: B6, Value: 49.5, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 0

Cell: A7, Value: 99.6, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 100
Cell: A7, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2
    The number of digits '3' exceeds the allowed '2'

Cell: B7, Value: 49.4, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 0

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

An exception occurred attempting to load Table 'Tabla Redondeo Enteros'.
    Insert command: insert into "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") values(:0, :1, :2, :3)
{
  "$id": "1",
  "$type": "Oracle.ManagedDataAccess.Client.OracleException, Oracle.ManagedDataAccess",
  "OracleLogicalTransaction": {
    "$type": "Oracle.ManagedDataAccess.Client.OracleLogicalTransaction, Oracle.ManagedDataAccess",
    "Committed": null,
    "UserCallCompleted": null,
    "ConnectionString": null,
    "LogicalTransactionId": null
  },
  "Errors": {
    "$type": "Oracle.ManagedDataAccess.Client.OracleErrorCollection, Oracle.ManagedDataAccess",
    "$values": [
      {
        "$id": "2",
        "$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
        "DataSource": "",
        "Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/",
        "Number": 24381,
        "ParseErrorOffset": 97,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": -1
      },
      {
        "$id": "3",
        "$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
        "DataSource": "",
        "Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
        "Number": 1438,
        "ParseErrorOffset": -1,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": 3
      },
      {
        "$id": "4",
        "$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
        "DataSource": "",
        "Message": "ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
        "Number": 1438,
        "ParseErrorOffset": -1,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": 5
      }
    ]
  },
  "DataSource": "",
  "Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/\nORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/",
  "Procedure": "",
  "Source": "Oracle Data Provider for .NET, Managed Driver",
  "Number": 24381,
  "IsRecoverable": false,
  "ErrorCode": -2147467259,
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2147467259
}

    Excel Row error: '5' Oracle error: '1438'
    Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
    Values:
        Redondeo 100: 12345700
        Int 2: 100
        DRIVER: Managed Driver (Arrays)
        PROVIDER: .NET
    Excel Row error: '7' Oracle error: '1438'
    Message: ORA-01438: value larger than specified precision allowed for this column
https://docs.oracle.com/error-help/db/ora-01438/
    Values:
        Redondeo 100: 0
        Int 2: 100
        DRIVER: Managed Driver (Arrays)
        PROVIDER: .NET

OLE DB (Oracle® Provider for OLE DB) Connections "Integer Rounding" option checked

Row: 4
Command: INSERT INTO "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?)
Select Command: SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
Exception:
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01438: value larger than specified precision allowed for this column",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01438: value larger than specified precision allowed for this column",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217833
}

    Column: Redondeo 100    Value: 12345700
    Column: Int 2    Value: 100
    Column: DRIVER    Value: OraOLEDB.Oracle
    Column: PROVIDER    Value: OLE DB
An exception occurred attempting to load Table 'Tabla Redondeo Enteros'. Row Error: 4
     Query that indicates the used columns of the Table:
     SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
{
  "$id": "1",
  "$type": "System.Data.OleDb.OleDbException, System.Data",
  "ErrorCode": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "ORA-01438: value larger than specified precision allowed for this column",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-01438: value larger than specified precision allowed for this column",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217833
}

ODBC (Oracle ODBC Driver) Connections "Integer Rounding" option checked

Row: 4
Command: INSERT INTO "Tabla Redondeo Enteros" ("Redondeo 100", "Int 2", "DRIVER", "PROVIDER") VALUES (?, ?, ?, ?)
Select Command: SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
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": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
        "SQLState": "HY000",
        "NativeError": 1438,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: Redondeo 100    Value: 12345700
    Column: Int 2    Value: 100
    Column: DRIVER    Value: {Oracle en OraDB21Home1}
    Column: PROVIDER    Value: ODBC
An exception occurred attempting to load Table 'Tabla Redondeo Enteros'. Row Error: 4
     Query that indicates the used columns of the Table:
     SELECT "Redondeo 100", "Int 2", "DRIVER", "PROVIDER" FROM "Tabla Redondeo Enteros"
{
  "$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": "[Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
        "SQLState": "HY000",
        "NativeError": 1438,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-01438: value larger than specified precision allowed for this column\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

By checking the "No Conversion Errors" option, these problematic data values will be replaced with NULL or the default value specified in the Mappings.
Below is the output in the Results Panel when these two options are checked. Please note that Excel To Database rounds the data using MidpointRounding.AwayFromZero. See MidpointRounding Enumeration.

 Cell: B2, Value: 12345, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 12300

Cell: A3, Value: 2.49, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 2

Cell: B3, Value: 123456, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 123500

Cell: A4, Value: 2.5, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 3

Cell: B4, Value: 1234567, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 1234600

Cell: A5, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2
    The number of digits '3' exceeds the allowed '2'
    It will attempt to load the cell as NULL (or the default value). "No Conversion Errors" option checked.

Cell: B5, Value: 12345678, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 12345700

Cell: A6, Value: 99.1, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 99

Cell: B6, Value: 49.5, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 0

Cell: A7, Value: 99.6, Table: Tabla Redondeo Enteros, Column: Int 2
     "Integer Rounding" option checked. Rounded Value: 100
Cell: A7, Value: 100, Table: Tabla Redondeo Enteros, Column: Int 2, Precision: 2
    The number of digits '3' exceeds the allowed '2'
    It will attempt to load the cell as NULL (or the default value). "No Conversion Errors" option checked.

Cell: B7, Value: 49.4, Table: Tabla Redondeo Enteros, Column: Redondeo 100, Precision: 10, Scale: -2
    Number of significant zeros to the left of the decimal point 0 is lower Abs(Scale) 2
    "Rounding Integer" option checked. Rounded value: 0

Frequently Asked Questions

What rounding algorithm does the application use?

It uses AwayFromZero, where midpoint values (.5) always round toward the next number away from zero.

Why am I getting a formatting error when loading decimals into integers?

The most common error is the ambiguity between regional formats (UK vs US). It is vital to standardize to ISO format (YYYY-MM-DD) before loading to avoid misinterpretations.

What happens if my Excel has empty cells or nulls?

Because the database cannot directly convert "2.1" to an integer. You must enable the Integer Rounding option so the software performs the conversion before loading.