Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

sql

data-conversion

import-settings

data-types

byNikita Barsukov·Aug 23, 2024

Your truncation or encoding predicament during UNPIVOT can be overcome by casting all columns to NVARCHAR(MAX) first. This tactic successfully sidesteps code page disparities, saving all characters from vanishing into thin air.

SELECT
  pvt.ColumnName,
  -- Now you see me, now you don't! But seriously, don't let your characters disappear. 
  CAST(pvt.Value AS NVARCHAR(MAX)) as HangInThereCharacters
FROM
  (SELECT Col1, Col2, Col3 /* Add your columns here. The more, the merrier! */) AS t
UNPIVOT
  (Value FOR ColumnName IN (Col1, Col2, Col3)) AS pvt

For importing data with diverse character sets, remember to tick the "UNICODE" checkbox during the flat file connection setup. Mismatches waive goodbye with unicode types such as NVARCHAR(255) for culprits like the "recipient-name" column.

Tweaking data types and sizes

Molding data types "on the fly"

Data types and sizes should be twins for your source and destination. The SQL LEN() function is your ally to determine column length requirements:

SELECT MAX(LEN([ColumnName])) FROM [YourTable]; -- Because size matters, right?

Modify target table definitions or update import settings based on your findings. Forget Excel and embrace importing flat files as CSV, TSV, or TXT formats. They are less likely to goof up conversions.

A different import sequence

Shuffling your data rows to bring the longest text on top during import can be quite a game changer! Import processes then adjust to the largest size first, smiling at reduced truncation risk.

Syncing data with code page

Your target code page and data must shake hands. Storing Latin characters in ASCII-only fields can create horrors of data loss/corruption. Go for larger data values and superior type detection with "text stream" or NVARCHAR(MAX) DataType.

Tackling common errors

Catching nuances of flat file import

Lean on the "Edit Mappings" during flat file imports. It’s your lifeline to ensure data sizes align with the target database code page.

Spot and resolve errors wisely

Got a data conversion failure? Spot a status value of 4 or a "Text was truncated" bug message. Play around with different data types and lengths for the troublemaker columns, especially varchar/nvarchar fields like "recipient-name".

More tips: Go the extra mile

In your quest to outsmart character issues, optimize import settings. And hey, consider taking a leaf out of the open-source tools' book to see if they offer better results.

Keep your eyes open for field-specific errors during the flat file connection process. Identifying and treating them early can save precious troubleshooting time.

explain-codes / Sql / Text was truncated or one or more characters had no match in the target code page including the primary key in an unpivot

Linked

Import CSV file into SQL Server



Load DATA LOCAL, How do I skip the first line?



Sql NVARCHAR and VARCHAR Limits



How do I remove the first characters of a specific column in a table?



How to increase SQL*Plus column output length?

