• • •

Truncate Strings

Data truncation occurs when an Excel text string exceeds the capacity defined in the destination column (VARCHAR, NVARCHAR, etc.). While most drivers throw an exception that halts the entire load, Excel To Database allows you to decide the desired behavior before starting the process.

This CheckBox will only appear for character string columns where the Data Provider, Driver, or Database is unable to truncate strings that exceed the maximum allowed length.
Excel To Database will always display a warning message when the data to be inserted into the column exceeds the maximum number of characters or bytes allowed.

"Truncate Strings" option unchecked: Excel To Database acts as an integrity safeguard. If the application detects that Excel data does not fit in the destination column, it halts the process to prevent incomplete data from being saved.

"Truncate Strings" option checked: Prioritizes load continuity. The application automatically adjusts the text length to the maximum allowed by the database, allowing the process to complete without overflow errors.

MS Access: Truncate Strings

To demonstrate the behavior of this option with MS Access, a Database and the Excel Workbook from the previous section are used. From the Excel Workbook, the Sheet shown in the following figure will be used:

MS Access: Excel Sheet for [Tabla Truncar Cadenas]

In ODBC connections to MS Access, the data provider truncates the text string so that it can be inserted into the text field. Therefore, this option will not appear for this type of connection. However, Excel To Database will always display a warning message.
On the contrary, in OLE DB connections this does not occur, so if this option is not checked an error will occur in the data insertion phase in the table:

 Row: 2
Command: INSERT INTO [Tabla Valores Defecto] ([Texto 5 N Null N Empty Def Val], [Driver], [Proveedor Datos]) VALUES (?, ?, ?)
Select Command: SELECT [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": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.",
        "NativeError": -68551703,
        "Source": "Microsoft Access Database Engine",
        "SQLState": "3163"
      }
    ]
  },
  "Message": "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft Access Database Engine",
  "HResult": -2147217833
}

    Column: Texto 5 N Null N Empty Def Val    Value: 123456    Error: Column 'Texto 5 N Null N Empty Def Val' exceeds the MaxLength limit.
    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: 2
     Query that indicates the used columns of the Table:
     SELECT [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": -2147217833,
  "Errors": {
    "$type": "System.Data.OleDb.OleDbErrorCollection, System.Data",
    "$values": [
      {
        "$id": "2",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.",
        "NativeError": -68551703,
        "Source": "Microsoft Access Database Engine",
        "SQLState": "3163"
      }
    ]
  },
  "Message": "The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft Access Database Engine",
  "HResult": -2147217833
}

Number of rows inserted: 0

As already mentioned, some Data Providers, Drivers, and/or Databases truncate character strings that exceed the maximum allowed length themselves. For these, Excel To Database does not display this CheckBox, but it does show a message indicating this fact.
It is possible that, in some cases, an error message indicating this fact will be displayed during the data insertion phase. However, the data insertion is carried out correctly. An example of this is the ODBC Data Provider with MS Access.

MS Access: ODBC Truncate Strings

Finally, note that "Long DROP TABLE IF EXISTS [dbo].[Tabla GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Tabla [Char5] [char](5) [Char5UTF8] [char](5) [VarChar5] [varchar](5) [VarChar5UTF8] [varchar](5) [Text] [text] --[TextUTF8] [text] [NChar5] [nchar](5) [NChar5UTF8] [nchar](5) [NVarChar5] [nvarchar](5) [NVarChar5UTF8] [nvarchar](5) [NText] [ntext] --[NTextUTF8] [ntext] [Provider] [varchar](10) [Driver] [varchar](50) [Identidad] [int] PRIMARY KEY CLUSTERED ( [Identidad] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE ALLOW_PAGE_LOCKS = ) ON [PRIMARY] GO

text" fields, although they have a field size limitation, are assumed to be large enough to be disregarded.

SQL Server: Truncate Strings

First of all, it is necessary to correctly understand what each data type can store and the influence of COLLATION on the field definition. For this, it is recommended to read: UTF-8 Support and pay special attention to the "Suggestion" indicated in that section, where the meaning of "n" in "char(n)", "varchar(n)", "nchar(n)", and "nvarchar(n)" is very clearly explained. And, of course, to thoroughly understand each data type: Character Strings and Unicode Character Strings.
As a clarifying note on what can be stored in these fields, it is recommended to read: NVARCHAR storing characters not supported by UCS-2 encoding on SQL Server and the referenced article: How Many Bytes Per Character in SQL Server: a Completely Complete Guide.

To demonstrate the behavior of this option with SQL Server, the following table is used. The "text" and "ntext" data types do not support collations with SC or UTF8. These data types can be considered obsolete.

  Truncar Cadenas]; Truncar Cadenas]( COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS         NULL, COLLATE Modern_Spanish_100_CS_AS_SC_UTF8 NULL, COLLATE Modern_Spanish_100_CS_AS     NOT NULL, COLLATE Modern_Spanish_100_CS_AS     NOT NULL, IDENTITY(1,1)     NOT NULL, = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

  The book to be used is the book from the previous section, which contains the sheet shown in the following figure: 
SQL Server: Excel Sheet for [Tabla Truncar Cadenas]
   
 Secondly, it should be noted that none of the Data Providers or Drivers for SQL Server truncate character strings, unlike what happens with ODBC in MS Access. Thus, if the option "Truncate Strings" is not checked, Excel To Database will warn of possible issues but will attempt to load the data. This will cause an error during the loading phase. 
    
   .NET Connections (System.Data.SqlClient)

An exception occurred while loading the data into the DataTable.
Some restrictions are not met.
     Message:Failed to enable constraints. One or more rows contain values violating NON-NULL, UNIQUE or FOREIGN-KEY constraints.
An attempt will be made to upload the data, although the upload will most likely fail.

        Reading Data Sheet 'SQL Server - Truncar Cadenas': 00:00:00.0289828

An exception occurred attempting to load Table 'dbo.Tabla Truncar Cadenas'.
     Query that indicates the used columns of the Table:
     SELECT [NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider] FROM [dbo].[Tabla Truncar Cadenas]
{
  "$id": "1",
  "$type": "System.InvalidOperationException, mscorlib",
  "Message": "The given value of type String from the data source cannot be converted to type nvarchar of the specified target column.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": {
    "$type": "System.InvalidOperationException, mscorlib",
    "Message": "String or binary data would be truncated.",
    "Data": {
      "$type": "System.Collections.ListDictionaryInternal, mscorlib"
    },
    "InnerException": null,
    "HelpLink": null,
    "Source": "System.Data",
    "HResult": -2146233079
  },
  "HelpLink": null,
  "Source": "System.Data",
  "HResult": -2146233079
}


OLE DB Connections (MSOLEDBSQL19)

Row: 2
Command: INSERT INTO [dbo].[Tabla Truncar Cadenas] ([NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT [NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider] FROM [dbo].[Tabla Truncar Cadenas]
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": "The statement has been terminated.",
        "NativeError": 3621,
        "Source": "Microsoft OLE DB Driver 19 for SQL Server",
        "SQLState": "01000"
      },
      {
        "$id": "3",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
        "NativeError": 2628,
        "Source": "Microsoft OLE DB Driver 19 for SQL Server",
        "SQLState": "22001"
      }
    ]
  },
  "Message": "The statement has been terminated.
String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft OLE DB Driver 19 for SQL Server",
  "HResult": -2147217833
}

    Column: NText    Value: 123456
    Column: NVarChar5UTF8    Value: 123456
    Column: NVarChar5    Value: 123456
    Column: NChar5UTF8    Value: 123456
    Column: NChar5    Value: 123456
    Column: Text    Value: 123456
    Column: VarChar5UTF8    Value: 123456
    Column: VarChar5    Value: 123456
    Column: Char5UTF8    Value: 123456
    Column: Char5    Value: 123456
    Column: Driver    Value: MSOLEDBSQL19.1
    Column: Provider    Value: OLE DB
An exception occurred attempting to load Table 'dbo.Tabla Truncar Cadenas'. Row Error: 2
     Query that indicates the used columns of the Table:
     SELECT [NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider] FROM [dbo].[Tabla Truncar Cadenas]
{
  "$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": "The statement has been terminated.",
        "NativeError": 3621,
        "Source": "Microsoft OLE DB Driver 19 for SQL Server",
        "SQLState": "01000"
      },
      {
        "$id": "3",
        "$type": "System.Data.OleDb.OleDbError, System.Data",
        "Message": "String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
        "NativeError": 2628,
        "Source": "Microsoft OLE DB Driver 19 for SQL Server",
        "SQLState": "22001"
      }
    ]
  },
  "Message": "The statement has been terminated.
String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "Microsoft OLE DB Driver 19 for SQL Server",
  "HResult": -2147217833
}

Number of rows inserted: 0


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

Row: 2
Command: INSERT INTO [dbo].[Tabla Truncar Cadenas] ([NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Select Command: SELECT [NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider] FROM [dbo].[Tabla Truncar Cadenas]
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 Driver 18 for SQL Server][SQL Server]String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
        "SQLState": "42000",
        "NativeError": 2628,
        "Source": "msodbcsql18.dll"
      },
      {
        "$id": "3",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated.",
        "SQLState": "01000",
        "NativeError": 3621,
        "Source": "msodbcsql18.dll"
      }
    ]
  },
  "Source": "msodbcsql18.dll",
  "ErrorCode": -2146232009,
  "Message": "ERROR [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.
ERROR [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: NText    Value: 123456
    Column: NVarChar5UTF8    Value: 123456
    Column: NVarChar5    Value: 123456
    Column: NChar5UTF8    Value: 123456
    Column: NChar5    Value: 123456
    Column: Text    Value: 123456
    Column: VarChar5UTF8    Value: 123456
    Column: VarChar5    Value: 123456
    Column: Char5UTF8    Value: 123456
    Column: Char5    Value: 123456
    Column: Driver    Value: {ODBC Driver 18 for SQL Server}
    Column: Provider    Value: ODBC
An exception occurred attempting to load Table 'dbo.Tabla Truncar Cadenas'. Row Error: 2
     Query that indicates the used columns of the Table:
     SELECT [NText], [NVarChar5UTF8], [NVarChar5], [NChar5UTF8], [NChar5], [Text], [VarChar5UTF8], [VarChar5], [Char5UTF8], [Char5], [Driver], [Provider] FROM [dbo].[Tabla Truncar Cadenas]
{
  "$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 Driver 18 for SQL Server][SQL Server]String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.",
        "SQLState": "42000",
        "NativeError": 2628,
        "Source": "msodbcsql18.dll"
      },
      {
        "$id": "3",
        "$type": "System.Data.Odbc.OdbcError, System.Data",
        "Message": "[Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated.",
        "SQLState": "01000",
        "NativeError": 3621,
        "Source": "msodbcsql18.dll"
      }
    ]
  },
  "Source": "msodbcsql18.dll",
  "ErrorCode": -2146232009,
  "Message": "ERROR [42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]String or binary data would be truncated in table 'dbo.Tabla Truncar Cadenas', column 'NVarChar5UTF8'. Truncated value: '12345'.
ERROR [01000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]The statement has been terminated.",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

Number of rows inserted: 0
  
 
 
 
  When the "Truncate Strings" option is checked, it is important to note that Excel To Database truncates the strings considering the maximum allowed character and/or byte capacity for the field. Thus, when loading the aforementioned data, the following messages are obtained in the Results Panel, where it can be observed that the string is cut to meet the most restrictive criteria. 
    
   Cell: A3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: B3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: C3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: D3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: F3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: G3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: H3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: I3, Value: 123456, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12345

Cell: A4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ??234

Cell: B4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👧2

Cell: C4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ??234

Cell: D4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👧2

Cell: F4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👧234

Cell: G4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👧234

Cell: H4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👧234

Cell: I4, Value: 👧2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👧234

Cell: A5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ??234

Cell: B5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ⚕?

Cell: C5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ??234

Cell: D5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ⚕?

Cell: F5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: ⚕️234

Cell: G5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: ⚕️234

Cell: H5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: ⚕️234

Cell: I5, Value: ⚕️2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: ⚕️234

Cell: A6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ???23

Cell: B6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 芦?

Cell: C6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ???23

Cell: D6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 芦?

Cell: F6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 芦󠄀23

Cell: G6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 芦󠄀23

Cell: H6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 芦󠄀23

Cell: I6, Value: 芦󠄀2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 芦󠄀23

Cell: B7, Value: €2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: €23

Cell: D7, Value: €2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: €23

Cell: A8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: B8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: C8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: D8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: F8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: G8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: H8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: I8, Value: 12.345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 12.34

Cell: A9, Value: 气前照灯5, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max characters allowed: 5, Max bytes allowed: 5
    "Truncate Strings" option checked. Values are modified to match the Code Page of the Destination Column.
    Modified Value: ????5

Cell: B9, Value: 气前照灯5, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 气?

Cell: C9, Value: 气前照灯5, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max characters allowed: 5, Max bytes allowed: 5
    "Truncate Strings" option checked. Values are modified to match the Code Page of the Destination Column.
    Modified Value: ????5

Cell: D9, Value: 气前照灯5, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 气?

Cell: A10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ?????

Cell: B10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: C10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ?????

Cell: D10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: F10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩

Cell: G10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩

Cell: H10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩

Cell: I10, Value: 👨‍👩‍👧‍👦2345, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩

Cell: A11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: Char5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ?????

Cell: B11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: Char5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: C11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: ?????

Cell: D11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: VarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: F11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: NChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨👩?

Cell: G11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: NChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨👩?

Cell: H11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨👩?

Cell: I11, Value: 👨👩👧👦5, Table: dbo.Tabla Truncar Cadenas, Column: NVarChar5UTF8, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨👩?
  
 
 
 
  Note that in some cases Excel To Database improves the behavior of SQL Server Management Studio (specifically version 20.0.70.0): 
  INSERT INTO dbo.[Tabla Truncar Cadenas]
(Char5UTF8, Provider, Driver)
values
(N'⚕️2345','SSMS','SSMS')

Message 2628, Level 16, State 1, Line 1
Binary or string data is truncated in column "Char5UTF8" of table "dbo.Tabla Truncar Cadenas".  Truncated value: "     ".
The statement has been terminated.

The correct response should have been:
"⚕? " or something similar and not 5 blank spaces.
Note the difference between '⚕️' and '⚕'.
  Oracle: Truncate Strings
  To demonstrate the behavior of this option with Oracle, the following table is used. 
    
   CREATE TABLE "Tabla Truncar Cadenas" (
  Id NUMBER(6) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  Provider                  VARCHAR2(10 CHAR),
  Driver                    VARCHAR2(50 CHAR),

  "Char 5"                  CHAR(5 CHAR),
  "Char 5 BYTE"             CHAR(5 BYTE),
  "VarChar 5"               VARCHAR(5),

  "VarChar2 5 Invisible"    VARCHAR2(5 CHAR) INVISIBLE,
  "VarChar2 5"              VARCHAR2(5 CHAR),
  "VarChar2 5 BYTE"         VARCHAR2(5 BYTE),

  "NChar 5"                 NCHAR(5),
  "NVarChar2 5"             NVARCHAR2(5),

  "CLOB"                    CLOB,
  "NCLOB"                   NCLOB
);
  
 
 
 
  The book to be used is the one from the previous section, which contains the sheet shown in the following figure: 
Oracle: Excel Sheet for [Tabla Truncar Cadenas]
   
 As with SQL Server, none of the Data Providers or Drivers truncate character strings. 
 It is important to highlight the significant issue described in Managed Driver when used with the Bulk Copy feature (option "Use Arrays" unchecked). In this case, the driver inserts ANYTHING into the database without having to meet any of the field constraints defined in the table (CHECK, FOREIGN KEY, DEFAULTS, INSERT TRIGGERS and indexes may be left in an unusable state). The consequences of this behavior can be disastrous for the database.
 Thus, loading the previous sheet with the Bulk Copy feature and the "Truncate Strings" option unchecked (without the checks performed by Excel To Database) results in the following load: 
Oracle: Bug Constrains BulkCopy
   
 As can be seen, there are records for the "Char 5 BYTE" field that far exceed the restriction of not exceeding 5 bytes. This leaves the table in an inconsistent state and, for example, the statement 'SELECT * FROM "Truncate Strings Table" order by ID;' does not work in "Oracle SQL Developer" due to index rebuild after insert with BulkCopy. 
Oracle: Bug Indexes BulkCopy
   
 Excel To Database includes a series of checks to prevent this kind of issue. Thus, the "Results Panel" shows messages such as: 
  Cell: A2, Value: 123456, Table: Tabla Truncar Cadenas, Column: Char 5, Max number of characters allowed: 5, Max number of bytes allowed: -
    Exceeds Max number of characters allowed. Supported Value: 12345
    The Oracle Managed Driver with NO Arrays contains a bug that allows inserting character strings that do not comply with the restrictions.
    Check the "Truncate Strings" option.
    The Data Load Phase will not be executed.

Cell: B2, Value: 123456, Table: Tabla Truncar Cadenas, Column: Char 5 BYTE, Max number of characters allowed: -, Max number of bytes allowed: 5
    Exceeds Max number of characters allowed. Supported Value: 12345
    The Oracle Managed Driver with NO Arrays contains a bug that allows inserting character strings that do not comply with the restrictions.
    Check the "Truncate Strings" option.
    The Data Load Phase will not be executed.
...
   With the Managed Driver and the Array Binding feature (option "Use Arrays" checked), or with the OLE DB and ODBC Drivers, the behavior is different. Although Excel To Database shows the relevant warning messages, the data loading phase begins, and it is there where errors occur. The messages that appear in the .log files will be similar to the following: 
    
   .NET Connections (Managed Driver (Array Binding) (CheckBox "Use Arrays" checked))

Cell: A2, Value: 123456, Table: Tabla Truncar Cadenas, Column: Char 5, Max number of characters allowed: 5, Max number of bytes allowed: -
    Exceeds Max number of characters allowed. The Data Provider or Database may truncate the value.
    Supported Value: 12345
    Check the "Truncate Strings" option if you want this value to be stored.
...


An exception occurred attempting to load Table 'Tabla Truncar Cadenas'.
    Insert command: insert into "Tabla Truncar Cadenas" ("Char 5", "DRIVER", "PROVIDER") values(:0, :1, :2)
{
  "$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": 77,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": -1
      },
      {
        "$id": "3",
        "$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
        "DataSource": "",
        "Message": "ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/",
        "Number": 12899,
        "ParseErrorOffset": -1,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": 0
      },
...
      {
        "$id": "7",
        "$type": "Oracle.ManagedDataAccess.Client.OracleError, Oracle.ManagedDataAccess",
        "DataSource": "",
        "Message": "ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 11, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/",
        "Number": 12899,
        "ParseErrorOffset": -1,
        "Procedure": "",
        "Source": "Oracle Data Provider for .NET, Managed Driver",
        "ArrayBindIndex": 7
      }
    ]
  },
  "DataSource": "",
  "Message": "ORA-24381: error(s) in array DML
https://docs.oracle.com/error-help/db/ora-24381/\nORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/\nORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/\nORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/\nORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/\nORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 11, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/",
  "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: '2' Oracle error: '12899'
    Value: 123456
    Message: ORA-12899: value too large for column "C##USUARIOORACLE"."Tabla Truncar Cadenas"."Char 5" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/
  Excel Row error: '4' Oracle error: '12899'
    Value: ⚕️2345
    Message: ORA-12899: value too large for column "C##USUARIOORACLE"."Tabla Truncar Cadenas"."Char 5" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/
  Excel Row error: '5' Oracle error: '12899'
    Value: 芦󠄀2345
    Message: ORA-12899: value too large for column "C##USUARIOORACLE"."Tabla Truncar Cadenas"."Char 5" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/
  Excel Row error: '7' Oracle error: '12899'
    Value: 12.345
    Message: ORA-12899: value too large for column "C##USUARIOORACLE"."Tabla Truncar Cadenas"."Char 5" (actual: 6, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/
  Excel Row error: '9' Oracle error: '12899'
    Value: 👨‍👩‍👧‍👦2345
    Message: ORA-12899: value too large for column "C##USUARIOORACLE"."Tabla Truncar Cadenas"."Char 5" (actual: 11, maximum: 5)
https://docs.oracle.com/error-help/db/ora-12899/
Configuration 'Oracle_Net_Array': Load Finished. Incomplete Charge.


OLE DB (Oracle® Provider for OLE DB) Connections

Row: 1
Command: INSERT INTO "Tabla Truncar Cadenas" ("Char 5", "DRIVER", "PROVIDER") VALUES (?, ?, ?)
Select Command: SELECT "Char 5", "DRIVER", "PROVIDER" FROM "Tabla Truncar Cadenas"
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-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "Source": "OraOLEDB",
  "HResult": -2147217833
}

    Column: Char 5    Value: 123456
    Column: DRIVER    Value: OraOLEDB.Oracle
    Column: PROVIDER    Value: OLE DB
An exception occurred attempting to load Table 'Tabla Truncar Cadenas'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT "Char 5", "DRIVER", "PROVIDER" FROM "Tabla Truncar Cadenas"
{
  "$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-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)",
        "NativeError": 0,
        "Source": "OraOLEDB",
        "SQLState": ""
      }
    ]
  },
  "Message": "ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)",
  "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: 1
Command: INSERT INTO "Tabla Truncar Cadenas" ("Char 5", "DRIVER", "PROVIDER") VALUES (?, ?, ?)
Select Command: SELECT "Char 5", "DRIVER", "PROVIDER" FROM "Tabla Truncar Cadenas"
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-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)\n",
        "SQLState": "HY000",
        "NativeError": 12899,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

    Column: Char 5    Value: 123456
    Column: DRIVER    Value: {Oracle en OraDB21Home1}
    Column: PROVIDER    Value: ODBC
An exception occurred attempting to load Table 'Tabla Truncar Cadenas'. Row Error: 1
     Query that indicates the used columns of the Table:
     SELECT "Char 5", "DRIVER", "PROVIDER" FROM "Tabla Truncar Cadenas"
{
  "$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-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)\n",
        "SQLState": "HY000",
        "NativeError": 12899,
        "Source": "SQORA32.DLL"
      }
    ]
  },
  "Source": "SQORA32.DLL",
  "ErrorCode": -2146232009,
  "Message": "ERROR [HY000] [Oracle][ODBC][Ora]ORA-12899: value too large for column \"C##USUARIOORACLE\".\"Tabla Truncar Cadenas\".\"Char 5\" (actual: 6, maximum: 5)\n",
  "Data": {
    "$type": "System.Collections.ListDictionaryInternal, mscorlib"
  },
  "InnerException": null,
  "HelpLink": null,
  "HResult": -2146232009
}

Number of rows inserted: 0
  
 
 
 
  By checking the "Truncate Strings" option in Excel To Database, character strings will be truncated considering both the maximum allowed characters and bytes permitted by the field. Thus, when loading the aforementioned data, the Results Panel will display the following messages, indicating that the string is trimmed to meet the most restrictive criterion. 
    
   Cell: A9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: Char 5, Max number of characters allowed: 5, Max number of bytes allowed: 20
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩‍👧

Cell: B9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: Char 5 BYTE, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?  

Cell: C9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: VarChar 5, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: D9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: VarChar2 5 Invisible, Max number of characters allowed: 5, Max number of bytes allowed: 20
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩‍👧

Cell: E9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: VarChar2 5, Max number of characters allowed: 5, Max number of bytes allowed: 20
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩‍👧

Cell: F9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: VarChar2 5 BYTE, Max number of characters allowed: 5, Max number of bytes allowed: 5
    "Truncate Strings" option checked.
    Truncated Value: 👨?

Cell: G9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: NChar 5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩

Cell: H9, Value: 👨‍👩‍👧‍👦2345, Table: Tabla Truncar Cadenas, Column: NVarChar2 5, Max number of characters allowed: 5, Max number of bytes allowed: 10
    "Truncate Strings" option checked.
    Truncated Value: 👨‍👩
  
 
 
 
  The following figure partially shows the result of the data loading: 
Oracle: Bug Unicode BulkCopy - 1
   
 As you can see, even though the values "👨‍👩‍👧" and "👨👩👧👦5" are attempted to be inserted into the "Char 5" and "Varchar2 5" columns, the values "👨‍👩" y "👨👩�" appear in the database when using .NET Connections (Managed Driver (Bulk Copy) (CheckBox "Use Arrays" not checked)). For the rest of the drivers and providers, the correct values are inserted. 
 You can also see in the following figure the dump of the "Char 5" column. This driver does not fill the field correctly. 
Oracle: Bug Unicode BulkCopy - 2
   
 Frequently Asked Questions
 How to prevent the load from stopping due to a length error? 
 You can enable the 'Truncate' option. The software will automatically cut strings that exceed the limit, allowing the rest of the load to continue without errors. 
Why is prior validation better than leaving it to the driver? 
 Drivers typically abort the entire transaction upon a single truncation error. Our pre-validation allows identifying the issue row-by-row before committing the load. 
Why are there differences between drivers when truncating? 
 Because each engine (SQL Server, Oracle, Access) and driver handles buffer overflow differently. Excel To Database provides these options to unify behavior, giving control back to the user instead of the driver.